PHP database injection prevention?

This question is aimed towards people who have experience in dealing with PHP to access a database (specifically MySQL).

What, in your opinion or experience, is the best method to prevent data injections, via alteration of SQL statements, to your database?

Typically I use mysql_real_escape_string(), but I'm not so sure if that is enough to safeguard string data types being entered by the user.

Having been working more with Regular Expressions lately, I wonder if creating my own RegEx function that removes threatening characters might prove to be a more suitable security application than the built in functions.

Anyone have some insight into this?

Anwered 2010-03-11 23:44:47 by Robin T

To be honest, I think you'd just be re-inventing the wheel. The PHP built-in function has been tested by however many PHP programmers who have been using it for years. If you are really keen it would be more of benefits if you try to find what the function is lacking and contribute your patch to improve it.

Anyway, apart from calling mysql_real_escape_string() to escape the string you should also use substr() to limit the length of input, to guard against attacks that target buffer overflow vulnerabilities.

Anwered 2010-03-11 23:18:11 by Ratchetr

Use Parameterized Queries, AKA prepared statements.

Things like mysql_real_escape_string or a RegEx are just band-aids. Sooner or later someone comes up with a clever way to conjure up a string that bleeds through the band-aid.

Parameterized Queries solve the problem and remove all the yuck from the code in one step. It doesn't make a bit of difference what is in a string parameter....it's a literal string, no way, no chance, no how is it going to be interpreted as SQL. Problem solved.

There are a few types of problems that aren't easily solved like this: Where the UI lets the user create some arbitrary combination of query parameters, strung together with AND's, OR's, LIKES' etc. You may still need to use dynamic SQL here. An IN clause is also hard to parameterize.
But that's a very small subset of what's out there.