Understanding input escaping in PHP

Hi,

One thing that's always confused me is input escaping and whether or not you're protected from attacks like SQL injection.

Say I have a form which sends data using HTTP POST to a PHP file. I type the following in an input field and submit the form:

"Hello", said Jimmy O'Toole.

If you print/echo the input on the PHP page that receives this POST data, it comes out as:

\"Hello\", said Jimmy O\'Toole.

This is the point where it gets confusing. If I put this input string into (My)SQL and execute it, it'll go into the database fine (since quotes are escaped), but would that stop SQL injection?

If I take the input string and call something like mysqli real_escape_string on it, it comes out like this:

\\"Hello\\", said Jimmy O\\'Toole.

So when it goes into the database via (My)SQL, it ends up as:

\"Hello\", said Jimmy O\'Toole.

This obviously has too many slashes.

So if the input comes through HTTP POST as escaped, do you have to escape it again to make it safe for (My)SQL? Or am I just not seeing something obvious here?

Thanks in advance for any help.

From stackoverflow

• Obvious is the keyword for a hacker.

  I think escaping normally should be enough, but protecting against just the quotes might not be enough.

  See this SQL Injection cheatsheet, it's a good list of test you can run and see if too many slahses is a good thing or not.

  And don't forget to escape other kinds of values too, i.e. numeric fields and datetime fields can all be injected just as easily as strings.

• Ah, the wonders of magic quotes. It is making those unnecessary escapes from your POST forms. You should disable (or neutralize) them, and many of your headaches go away.

  Here's an exemplary article of the subject: http://www.sitepoint.com/blogs/2005/03/02/magic-quotes-headaches/

  Recap: disable magic quotes, use real_escape_string().

  Gumbo : You should disable them if you can. It’s better than reversing them.

  TravisO : Magic quotes were a bandaid the PHP devs added in the past then realized it cause more problems than it fixed. Magic quotes will be removed from PHP in 6.0

• Instead of relying on escaping I would use parametrized SQL queries and let the mysql driver do whatever escaping it needs.

  Tomalak : I wonder why so many people insist on going through the pain of string escaping for SQL use when paramterized queries are available. Fear of RTFM?

  TravisO : Yes I agree, parametrized queries or store procedures are the most secure way to access a SQL server.

  staticsan : "To he who has a hammer, every problem looks like a nail." There's nothing wrong with manually writing SQL. Parameterised queries are sometimes more work than that.

• What's going on is that you have Magic Quotes turned on in your PHP configuration.

  It's highly recommended that youturn magic quotes off - in fact, they've been removed from PHP 6 completely.

  Once you disable magic quotes, you'll see the POSTed text coming back exactly as you typed it in to the form: "Hello", said Jimmy O'Toole. It's now obvious that you need to use the mysql escaping functions or even better, prepared statements (with prepared statements you can't forget to escape a string as it's done for you).

• It looks like your PHP server has the Magic Quotes feature enabled - that's where your first set of slashes comes from. In theory, it should then be unnecessary to call the escape functions - but when the app runs on a server with magic quotes disabled, you're suddenly wide open to SQL injection while thinking you aren't.

  As chakrit wrote, escaping is not the best way to protect yourself - It's much safer to user parameterized queries.