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

[davefethiye]

Hi,

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:

[PHP]

$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 )
VALUES 
( '$member_no','$client_no','$em_type','$broad_no','$camp_no','$gift_no','$link_no','$sub_id','$author','$to','$contact',
'$subject2','$message2','$country','$mess_no','n','$today_time')";  

[/PHP]

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,

[spaceshiptrooper]

@davefethiye

There must be a fairly standard method of dealing with this

There is. It’s called Prepared Statments.

[Noppy]

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 ) 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.

hth