Understanding input escaping in PHP

Question

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.

Answer 1

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().

Answer 2

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