single quote in mysql record

[kruser]

What do I need to do to insert a record into a table that has a variable that contains a single quote " ' " ? When the varible contains a string such as "Tom's" ( $variable="Tom's" ) it does not insert into the table for me.

Thanks,
Randy

[DaveSawers]

You have to escape the single quote, so if you're using PHP, something like:

$query = "insert ... var='Tom\'s', ..."

or using your example directly, you could alternatively use:

$variable = "Tom's";
addslashes($variable);

to do the same thing. addslashes is an internal PHP function: PHP: addslashes - Manual

[niggles]

Hi,

Instead of addslashes() which means you then have to use stripslashes() when re-displaying it, I prefer to use mysql_real_escape_string() which drops it in without messing around with anything.

Cheers,
Niggles

[steveglobal]

I always thought that addslashes() was functionally the same as mysql_real_escape_string(), but when niggles posted that, I Googled the subject a bit to find the actual difference.

Turns out that there are some security benefits to mysql_real_escape_string():

Chris Shiflett: addslashes() Versus mysql_real_escape_string()

Although, some have posited that true security comes only from prepared statements:

mysql_real_escape_string() versus Prepared Statements - iBlog - Ilia Alshanetsky

[magnets]

Whenever I receive data from an untrusted source I do a string replace and change a quote (') to a tick (`) character. Even if I forget to change it back later for display purposes, people get the idea anyway.

If it is a trusted source of mine, it does not have a quote (') in the first place.

[kruser]

Thanks for all the great information, just what I was looking for and more.

Best Regards,
Randy

[boxmonkey]

nggles, you don't have to use strpslashes after usng addslashes unless you're dong t on user nput *and* you have magc quotes turned on. of course f magc quotes s turned on, apostrophes are already nserted correctly and addslashes s not needed (when dealng wth user data)