PHP and MS SQL

Problems with quotes!

[djd4n]

I am having problems with quotes.

The setup I have is a PHP page gets form data sent from another page via POST.

In the PHP page the $_POST variable is put into a SQL statement

"UPDATE products SET product_des_long='".$product_des_long."' WHERE id =".$id."";

This is then executed on the MS Access database.

If there are any ' in the POST data the SQL returns an error.
SQL state 37000 in SQLExecDirect

Double quotes " however do not cause a problem.

magic_quotes_gpc() appears to be on as get_magic_quotes_gpc() returns 1.

If I output the SQl statement using echo, the ' are correct escaped with \.

anyone know what is happening?

[Blackie]

I would simply used this line:
"UPDATE products SET product_des_long=$product_des_long WHERE id =".$id."";

[gliff]

In Microsoft Access, you escape single quotes not with a backslash, but with a second quote, like this

The quick brown fox''s style was getting old

If you can, turn the magic-quotes-sybase [us4.php.net] directive on.

Or, if you want to stay sane, always keep Magic Quotes off and escape your strings yourself (-:

[djd4n]

same problem occurs when using

"UPDATE products SET product_des_long=$product_des_long WHERE id =".$id."";

the double quotes " do cause a problem actually just tried it.

[djd4n]

hi gliff

would i have to strip the single quotes off again before it is outputted?

i'm guessing not?

[gliff]

would i have to strip the single quotes off again before it is outputted?

i'm guessing not?

'' (two single quotes) in Access is the same as \' in MySQL. Only the one quote gets put in the database.

Try it out, everything will become clear :)

[djd4n]

ok cheers, but now i'm stuck making a function to do this so far got:

$quote = array(" ' "," " ");
$quote_new = array(" ' ' "," ' " ");
$product_des_long = str_replace($quote, $quote_new, $_POST['product_des_long']);

the ' and " need to be escaped so, otherwise there is a parse error

$quote = array(" \' "," \" ");
$quote_new = array(" \'\' "," \'\" ");
$product_des_long = str_replace($quote, $quote_new, $_POST['product_des_long']);

however this then puts the \ into the SQL query as well as the extra '

am i going about this the wrong way?

[djd4n]

Right sort of fixed it, in a roundabout way!

I can't turn magic_quotes_sybase on or magic_quotes_gpc off

so I've used...

$product_des_long=stripslashes($_POST['product_des_long']); // take out slashes magic_quotes_gpc puts in
$quote = array("'","\""); //find ' and "
$quote_new = array("''","\\\""); //replace with '' and \"
$product_des_long = str_replace($quote, $quote_new, $product_des_long); //:-)

think i'm there!

[djd4n]

Function:

function fix_quotes($str){
$fix_str=stripslashes($str);
$quote = array("'","\"");
$quote_new = array("''","\\\"");
$fix_str = str_replace($quote, $quote_new, $fix_str);
return $fix_str;
}

[Blackie]

same problem occurs when using
"UPDATE products SET product_des_long=$product_des_long WHERE id =".$id."";

the double quotes " do cause a problem actually just tried it.

Oh sorry, I forgot to remove those quotes as well, here is the line totally without quotes that should work:
"UPDATE products SET product_des_long=$product_des_long WHERE id=$id";