So I'm doing some research into PHP/MySQL security partly for my own edification and partly cause I now am responsible for a rather large number of websites employing that very combination and I figure I'd sure as heck better know whether or not they are secure.
The main question I have for all my fellow web guru's out there involves the prevention of SQL Injection attacks. I've been researching it and every where I look seems to mention the same methods of primary prevention for said attacks namely: input filtering and running input through escape functions. They all seem to suggest doing this before sending the queries to php's mysql_query() function.
However, it seems to me that mysql_query() actually handles both of those things, as well as preventing multiple statements in a single query, already all by itself. When I attempted to hack my own scripts using MySQL injection in the input fields I found that mysql_query() did in fact escape all the queries I sent to it and did not allow multiple queries. So why do all the sites I look at suggest running queries through mysql_real_escape_string() and why do none of them mention that fact that mysql_query() seems to take care of this already? Can anyone confirm that it does in fact take care of this? Or does it do some sort of minimal escaping and mysql_real_escape_string() is simply more thorough? Anyone know?
Posted by Alcon (Member # 6645) on :
I take it back, it's not mysql_query that's doing the auto escaping, it seems rather to be the post method itself doing it! Any one know if this is the case?
Posted by Dagonee (Member # 5818) on :
There are quite a few settingsin PHP that can affect this. One possibility is Magic Quotes. It's dangerous to rely on this, because it can be disabled server-wide. However, double-escaping can cause problems, too.
Posted by Alcon (Member # 6645) on :
Ahh, that's it. Thanks Dag. That was driving me crazy, everything I'd read suggested that it shouldn't be doing that and nothing had pointed me at that variable. I'd heard magic quotes mentioned, but only in the context of a function to be run that wasn't as good as mysql_real_escape_string().
Posted by fugu13 (Member # 2859) on :
Do not use mysql_query (or the better variant functions thereof in the standard library). It is a bad practice. Use PEAR::MDB2 or at least PEAR::DB, with query parameters. Do not even use mysql_real_escape_string, the query parameter usage will take care of that. Do check for other input constraints (or rather, require; it is usually better just to convert everything to an int -- 0 if PHP can't figure out what it should be, using a common function, than to try to work it out, when the only way that can usually happen is if somebody is fiddling to see what your site will take).
Magic quotes are evil (as has been recognized in the latest versions of PHP, where they are disabled by default). However, if you're writing anything that might be used by other people, you need to check if it is set and behave differently depending. As I said, evil.