UPDATE query - the ' problem

[normie]

Hi all,

I need some advice to solve the following problem:

<?php
$cx=odbc_connect (\"USERS\", \"\", \"\");

$qry=\"UPDATE USERS
SET title = \'$title\'
WHERE id = $id\";

$success=odbc_exec($cx,$qry);

?>

The above query fails if $title contains \' (single quote)
eg. \"Tom\'s CookBook\".

So how can I store the \"\'\" into the database?

I tried to change the query to:
$qry=\"UPDATE USERS
SET title = \\"$title\\"
WHERE id = $id\";
Still cannot work.

Please advise. Thanks. 🙁

[alanl]

Take the slashes out of your string and use addslashes around the variables. ie:

$qry="UPDATE USERS
SET title = 'addslashes($title)'
WHERE id = $id";

Cheers,
ALan

[daus95]

you must change every single quote (') to double single quote (''). try function below to convert your query. don't forget to remove slashes if you get the string content from a form field.

example:
$title = parse_string($title);
$qry = "UPDATE USERS SET title = '$title' WHERE id = $id";

// parse_string - use to parse a string from HTML Tags and Quote character.
function parse_string($str) {
return str_replace("'", "''", stripslashes(trim($str)));
}

[Anon]

Thanks Alan & daus!

I did tried your solutions and it works! 🙂

However in my case, I am using MS ACCESS as the database (ODBC). So the ' has to be replace by ".

meaning John's Book -> John"s Book.

I know that your method will work well with MySQL. Thank!!! 🙂

[normie]

Dear all,

*ignore my previous posting.

This statement works well for PHP/MS ACCESS
$usr = str_replace("'", "''", $usr);

This fails:
$usr = addslashes($usr);

Regards,
normie