How to enable auto mysql real escape to all queries?

Question

Hi,

I'm running php 5.2.13 and i have an app that contains tons of files but they all calling one file at the beginning, i want to put some line in that file to automatically mysql real escape any query, because i don't want to go across every file and change code.

Thanks!

Answer 1

I don't know how well that would work. What you really need is to escape the input not things like table names, fields, etc. If you pass the entire query to an escape, I'd be willing to bet you'd find a good number of queries that will fail because it will turn things like

select * from tablename where name = 'foo'

into

select * from tablename where name = \'foo\'

Which would choke.

And, having a wrapper function in your code helps a lot (assuming you don't want to use a framework, etc). If you have "mysql_query()" littered around your code, you probably are in for a bit of work to change it up. If you can't/don't-want-to adopt a framework, at least wrap it in a function of your own, like "db_query()" like this:

function db_query($query,$and,$other,$arguments)
{
    mysql_query( ... ); // you can change this to some other database later if you want
}

I did that in a project a few years ago and it helped a ton when I wanted to log some errors. I just added it to that function instead of having it in 200 places in the code.

But even that won't really help if you didn't escape input properly in the first place. In that case your only option is to take some time and fix it.

Answer 2

Hans has some good suggestions. But i think the bottom line is youre going to have to modify a lot of code. There is no magic bullet on this one. Whoever wrote it should have known better, and now you my friend are going to pay the price. Personally if youre going to have to go in and manually edit i would urge you to switch to PDO or mysqli. That way you can make use of prepared statements which will handle the escaping of variables for you provided you use them correctly.

Answer 3

If you have a large project, and need to change the data access, I would suggest to move to an ORM, my personal pick is Propel.

With that you would solve the whole escaping sql's problem, would make your app more scalable and you could also reverse your database diagram in order to generate the classes needed for Propel.

Propel will give you benefits like transactions, parameters and many more, so you should reaally think about it.

Best regards

Answer 4

Take note that it's not queries that you will want to escape, it's user supplied variables that are to be included in the query (unless you're writing malformed SQL yourself on purpose). So what you can do is to run mysql_real_escape_string() on, say, the $_POST array with array_map(), provided that you are not going to use that array for anything else.

mysql_real_escape_string() is still only the second best solution to the issue anyway. I you can use prepared statements (AKA parametrized queries) and you're home free.