php, mysql, escaping and update queries

Anon

First off, I'm frustrated as the devil right now....

I'm trying to give PHP a fair shake after using Perl to connect to MySQL for the past 5 years.

My problem is a simple one. The solution "escapes" me.

I add data to a database that contains a "'", ex., the word "don't".

PHP appears to be automatically escaping the "'" for the add even though I've turned "magic quotes" off in the ini file.
The data goes into the database just fine.

I try updating that record.

No matter what, MySQL refuses to update the record becaused PHP turns the simple commmand:
"update faqs set answer ="Don't use quotes, Dummy" where id=122;"

into

"update faqs set answer ="Don\'t use quotes, Dummy" where id=122;"

which MySQL won't act on.

How do I turn the blasted auto-escaping feature off in PHP?

And if it's on, how do you get a stupidly simple update query like the one above to work?

Yes, I've tried

htmlspecialcharacters($answer);
htmladdslashes($answer);
htmlstripslashes($answer);

I must be doing something wrong. And I've spent way too much time trying to figure out what it is.

Thanks in advance.

Kinney Baughman

Anon

You need to put your actual query... because one way or another, you have to choose between single or double quotes, and escape the other accordingly.

You say it makes this:
"update faqs set answer ="Don\'t use quotes, Dummy" where id=122;"

Which is not possible because that has two sets of quotes, which will result in a parse error. The query should either end up like this:

"update faqs set answer = \"Don't use quotes, Dummy\" where id=122"

or like this:

'update faqs set answer = "Don\'t use quotes, Dummy" where id=122;'

It has a lot to do with how you format your query in the first place. I generally do it like this:

$query = sprintf(
"UPDATE faqs
SET answer = '%s'
WHERE id = 122",
mysql_escape_string("Don't use quotes, Dummy"));

then mysql_query($query) or whatever

NOTICE:
if you do:
mysql_escape_string('Don't use quotes, Dummy')
you'll get a parse error, and you'll need to escape the apostrophe beforehand.

Anon

OK, Problem solved.

I went home and slept on it.

When I looked at things again this morning, I realized my mistake was in the syntax of the update query. I had shortened it for purposes of posting on the board and because I was thinking it was an escaping problem. The lesson to be learned is never do something like that. Always share everything that you did, down to the very last "," or "and" ...

The full text of my update query was:

"update faqs set question="How do I enter FAQ\'s" and answer ="Don\'t use quotes, Dummy" where id=122;"

The problem is the "and". It should have been a ",". Thus:

"update faqs set question="How do I enter FAQ\'s", answer ="Don\'t use quotes, Dummy" where id=122;"

Arrrggghhh!

To add insult to injury, I've been bitten by this mistake before!!!

Thanks to all who have replied.

The real dummy,

Kinney