Syntax Error - Single Quote

[Emesh]

This will have been posted before, but I can't find a solution that works.  Most people say to try mysql_real_escape_string, I have tried lots of variations and it doesn't seem to work.

 

Could anyone help with the below code?  It is part of a form that returns a syntax error when adding a single quotation mark e.g. entering "Bryan's" into the form causes the error.

 

I'd be really grateful for any assistance.

 

Steven

 

P.S. Before anyone mentions it, the mysql connect does work - I just haven't included the full page of code.

 

mysql_connect($dbserver, $dbusername, $dbpassword);
mysql_select_db($dbname);

$sitetitle = htmlentities($_POST[sitetitle]);

$query = mysql_query("UPDATE site_settings SET sitetitle = '$sitetitle'");

echo("<b>Settings Updated!</b>");

[nkorth]

This is a common issue - you have to clean your database inputs. When PHP inserts $sitetitle with a single quote in it, mySQL thinks it's the end of the quoted string in your SQL code. So if I entered

asdf'; DROP TABLE site_settings;

in the POST field, it could do crazy things to your database.

 

Here's a simple solution:

function db_input($text){
if(get_magic_quotes_gpc()){
	$text = stripslashes($text);
}
return mysql_real_escape_string($text);
}

Just use this function on all variables you insert into SQL queries. When you retrieve that data from the database, you'll need to use stripslashes() on it.

[PFMaBiSmAd]

When you retrieve that data from the database, you'll need to use stripslashes() on it

 

^^^ No you won't.

 

When data is properly escaped (only once) the \ characters are not inserted into the database and when it is retrieved, with magic_quotes_runtime turned OFF, the \ characters as not added to the retrieved data and you don't need to do anything before you use the data.

[Emesh]

Thanks for your reply.

 

Using the function doesn't seem to help the problem, I still get the same SQL Syntax error...  This is probably more my lack of php knowledge, because don't use php very often.

[kenrbnsn]

Please post your current code.

 

Ken

[Emesh]

The code is essentially as above.  Originally my function was:

 

function security($data) {

if(is_array($data)) {

       foreach ($data as $key => $value){

           $data[$key] = security($value);

       }
		return $data;

	}else{

	return mysql_real_escape_string(addslashes($data));

	}
}

 

 

And the PHP code:

 

mysql_connect($dbserver, $dbusername, $dbpassword);
mysql_select_db($dbname);

$sitetitle = security($_POST[sitetitle]);

$query = mysql_query("UPDATE site_settings SET sitetitle = '$sitetitle'");

echo("<b>Settings Updated!</b>");

 

I just want to be able to use proper punctuation in forms!

[Pikachu2000]

Get rid of addslashes(). That function should instead test for get_magic_quotes_gpc(), and apply stripslashes() if that result is true.

 

if( get_magic_quotes_gpc() ) {
     $data = stripslashes($data):
}
return mysql_real_escape_string($data);