Apostrophies in text boxes creating mysql error

BrianChesworth · 17th June 2008, 12:21 PM

Hi all,

perhaps an obvious problem, I use php/mysql to insert form field data into a mysql db for content management, everything was fine using php4, then the hosting server was upgraded to php5 and this problem was born, the insert script worked fine, allowed any text inputed.
Now, when a user places text into a field which includes an apostrophy (i.e. test's) the submission results in a mysql error declaring too many (')s.
This is only happening with 'INSERT' if I use 'UPDATE/SET' then I do not have any problems, but obviously I need the 'INSERT' syntax to create new entries in the table.

A snippet of the script I am using is:

PHP Code:

  <?php .....dbconnect....etc ?>

 
<?php 

$_POST['Info_Box2_txt']; 

$_POST['Info_Box3_txt']; 

?>

........

<?

php $sql = mysql_query("INSERT INTO tbl_product 

(Info_Box2_txt, Info_Box3_txt) VALUES ('$Info_Box2_txt', '$Info_Box3_txt')") or die(mysql_error()); 

?>

........

Any thoughts?

Do I need to separate using periods (.) or quotes (") rather than apostrophies?

Why does this problem not exist when using 'UPDATE/SET'?

Regards

Brian

MrBen · 17th June 2008, 02:01 PM

It was probably because magic quotes was turned on (which automatically escapes special characters).

Check out example #3 at PHP: mysql_real_escape_string - Manual

Ben

BrianChesworth · 17th June 2008, 03:06 PM

Cheers Ben,

Its taken me a while to extract some sense from all that was there, but I've found a solution to the problem which works;
eg.

PHP Code:

 
......

<?php $who = (mysql_real_escape_string($_POST['who'])); ?>

 
<?php $sql = mysql_query("INSERT INTO tbl_product 

(who) VALUES ('$who')") or die(mysql_error()); ?>

......

very simplistic, and not particularly secure, but for the moment,more than sufficient.

Many Thanks

Brian