Emesh Posted March 13, 2011 Share Posted March 13, 2011 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>"); Link to comment https://forums.phpfreaks.com/topic/230478-syntax-error-single-quote/ Share on other sites More sharing options...
nkorth Posted March 13, 2011 Share Posted March 13, 2011 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. Link to comment https://forums.phpfreaks.com/topic/230478-syntax-error-single-quote/#findComment-1186841 Share on other sites More sharing options...
PFMaBiSmAd Posted March 13, 2011 Share Posted March 13, 2011 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. Link to comment https://forums.phpfreaks.com/topic/230478-syntax-error-single-quote/#findComment-1186844 Share on other sites More sharing options...
Emesh Posted March 13, 2011 Author Share Posted March 13, 2011 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. Link to comment https://forums.phpfreaks.com/topic/230478-syntax-error-single-quote/#findComment-1186980 Share on other sites More sharing options...
kenrbnsn Posted March 13, 2011 Share Posted March 13, 2011 Please post your current code. Ken Link to comment https://forums.phpfreaks.com/topic/230478-syntax-error-single-quote/#findComment-1186982 Share on other sites More sharing options...
Emesh Posted March 13, 2011 Author Share Posted March 13, 2011 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! Link to comment https://forums.phpfreaks.com/topic/230478-syntax-error-single-quote/#findComment-1186999 Share on other sites More sharing options...
Pikachu2000 Posted March 13, 2011 Share Posted March 13, 2011 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); Link to comment https://forums.phpfreaks.com/topic/230478-syntax-error-single-quote/#findComment-1187004 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.