Special Characters Mess With SQL Queries?

[Agold]

So I've slowly noticed that thinks like apostrophes and such will cause mySQL queries to fail when going through PHP? I believe it probably has something to do with escaping and magic quotes? For instance, see this code:

 

$ename = "Jakes";

$qry = "SELECT * FROM userBars WHERE barName='$ename'";
$result = mysql_query($qry);

if($result){
	echo "success";
	while($row = mysql_fetch_array($result, MYSQL_ASSOC))
		{
    			echo "Name :{$row['barName']} <br>" .
         		"ID : {$row['barID']} <br>" .
         		"Address : {$row['barAddress']} <br><br>";
		} 
}else{
	echo "Error";
}

 

That work fine, but say it's spelled "Jake's" in the database, that appears to fail every time. It's got to be something PHP is doing because if i just query the database like so:

 

SELECT * FROM userBars WHERE barName="Rick's"

 

from database console, it works just fine and I see the entire row. Anyone have any clue what's going on here?

[requinix]

Or it could be that you aren't running the same query as PHP is. Notice how the quotes are different?

 

mysql_real_escape_string. Learn it, live it, love it.

[Agold]

I should have noted that these were just examples. Say I pass in the data of $ename from a form. That form allows the user to create any sort of name with any sort of characters. I then use $ename to dynamically query based on that original data from the form and it fails because there are apostrophes. If I just force $ename with a string with apostrophes, it fails.

 

What I mean to say is that if there are any apostrophes in the $ename variable, the query seems to fail. But if I do the same query using a name with apostrophes in mySQL directly, it succeeds.

[requinix]

But if I do the same query using a name with apostrophes in mySQL directly, it succeeds.

It's not the same query. Let me put them right next to each other:

In PHP:   SELECT * FROM userBars WHERE barName='Rick's'
In MySQL: SELECT * FROM userBars WHERE barName="Rick's"

See the difference now?

[Agold]

Indeed I do, but this code does not fire:

 

$qry = "SELECT * FROM userBars WHERE barName="$ename"";
$result = mysql_query($qry);

 

Nor does this code:

 

$qry = "SELECT * FROM userBars WHERE barName="'$ename'"";
$result = mysql_query($qry);

 

This code fires but the query result comes up blank:

 

$qry = 'SELECT * FROM userBars WHERE barName="$ename"';
$result = mysql_query($qry);

 

So please, enlighten me here cause I'm lost as to how I properly syntax the query so that apostrophes will not interfere.

 

Only this code fires and brings back the right result as long as there are no apostrophes:

 

$qry = "SELECT * FROM userBars WHERE barName='$ename'";
$result = mysql_query($qry);

[dcro2]

$qry = "SELECT * FROM userBars WHERE barName='".mysql_real_escape_string($ename)."'";

[Agold]

Exactly what I needed. Thanks man. Requinix, thanks for pretending like you knew what the problem was and then being an asshole about it.

[dcro2]

He did know what the problem was, and he told you pretty clearly.

Or it could be that you aren't running the same query as PHP is. Notice how the quotes are different?

 

mysql_real_escape_string. Learn it, live it, love it.