mySQL quoting

[Anon]

I'm having problems inserting records into a mysql database whose variables have a single quote in them. I don't have access to the server to enable any php autoquoting (I know the server admin has it turned off). I've tried $string = ereg_replace("'", "''", $string); but without luck. The funny thing is that no error is returned by mysql. The script will execute with no error but no record will be inserted.

[Anon]

If you are using single quotes to surround the records you are inserting, and your records also have single quotes in them(i.e 'it's'), try escaping the single quote with a backslash(i.e. 'it\'s'). Otherwise, you can also set off your records for insertion with double quotes (i.e. "it's"). THen you won't have to worry about escaping your single quote (i.e. "it's").

[Anon]

So if I used double quotes like you said would this syntax be correct:

$query = "insert into table values (\"$val1\", \"$val2\")"; ? I didn't think double quoting in sql was proper syntax.

[Anon]

Jay wrote:

I'm having problems inserting records into a mysql database whose variables have a single quote in them.

You need to use the function addslashes().

mysql_query("insert into foo (bar) values ('".addslashes($var)."')");

[Anon]

If you are storing your info in variables, then you don't need to bother with escaping. You can just write

$query = "insert into table values ('$val1', '$val2')";

If the info under $val1 and $val2 have single quotes in them, then they should show up fine in your DB.

If you hardcode the values in w/out variables, then you need to escape

$query = "insert into table values ('it\'s', 'it\'s')";

This is what I do and it works for me...hope it works for you.