How do I update my table if the data has an apostrophe in it?


This must be an old chestnut, but I couldn’t find
a straightforward answer when I searched.

My php script updates my mySQL table update like this:


$sql_insert = "INSERT INTO `emailqueue` (member_no,client_no, em_type, broad_no, camp_no, gift_no, link_no, sub_no, author, to_mail, contact, subject,mess, country, attempts, sent, send_date )
( '$member_no','$client_no','$em_type','$broad_no','$camp_no','$gift_no','$link_no','$sub_id','$author','$to','$contact',


So when my $subject2 variable or my $message2 contain an apostrophe, the code breaks and I get an error.
EG $subject2 = "Oh no You didn't get it";
This $subject2 variable is not input at this time but is being pulled out of a different table and gets inserted if the date is correct.

There must be a fairly standard method of dealing with this

Many thanks,

There is. It’s called Prepared Statments.

The way you are doing this is very insecure and is vulnerable to ‘SQL injection’. Because you aren’t telling the code to ignore quotes and apostrophes a malicious user can come along and basicially ‘inject’ an extra bit of code into your script. So they could use any of the fields you have in a form and add in a command to drop your table or print out the table or insert a user into a table.

As @spaceshiptrooper says you need to use ‘Prepared statements’ (it has an ‘e’ in it :wink: ) or at the very least escape your variables before you put them in a query

mysqli_real_escape_string($connection, $member_no); etc for each variable before the insert.

If you are using ‘mysql’ you should move to ‘mysqli’ at the very least.


This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.