Faster SQL injection prevention

Regarding your sanitization function - while I can't produce an exploit for the exact example you provided, if we change your example to something like this:

$sql = "UPDATE ipsum SET price=$str WHERE id=1337";

If a value of 10, otherColumn=1234 or maybe 10;-- was passed in for $str you could see problems. If you roll your own sanitization function and begin using it in your code, it seems like it would only be a matter of time before an vulnerability like this popped up. I'll keep playing around to see if I can find an exploit for the exact example you provided in your question.

That said, I wouldn't recommend rolling your own sanitization function. Smarter people than you and I have put considerable effort into testing the effectiveness of the currently accepted industry standard of using prepared statements to prevent SQL Injection so you can be (fairly) certain that it is safe. Rolling your own is almost always a mistake.

Also - I would wager that there are people out there using prepared statements in environments that handle more traffic and require more performance than your current project.

If you are experiencing performance problems accessing your data layer, I would recommend spending some time looking at performance tuning rather than building your own sanitization function.

Performance tuning access to your data layer is an essential and normal part of development. Rolling your own sanitization is not.


Firstly, PHP already has your second function and it's called addslashes().

The docs explicitly say:

To escape database parameters, DBMS specific escape function (e.g. mysqli_real_escape_string() for MySQL or pg_escape_literal(), pg_escape_string() for PostgreSQL) should be used for security reasons.

If your DBMS doesn't have an escape function and the DBMS uses \ to escape special chars, you might be able to use this function only when this escape method is adequate for your database. Please note that use of addslashes() for database parameter escaping can be cause of security issues on most databases.

Your first function is also missing several characters that are significant to MySQL syntax.

Other important reasons why you shouldn't resort to this:

  • Let's say you can get away with some more efficient but less comprehensive sanitization for specific queries. Do you want to have to try and fully consider all potential attack vectors against every single query you write? How long until you make a mistake?

  • A a good reason to use prepared statements over mysql_real_escape_string is that one day you will inevitably forget to escape something. Many SQL injections in well known PHP projects have been caused by someone simply forgetting to escape something, it happens to everyone. At least with prepared statements if you forget to call bindParam() your query doesn't work.

If you want to improve performance implement caching and avoid MySQL queries entirely.

Also, make sure you're using an up to date version of MySQL:

Before 5.1.17, the query cache is not used for prepared statements. http://dev.mysql.com/doc/refman/5.1/en/query-cache.html

Don't roll your own when it comes to security.


Can you remove the brakes of your car to save weight? Well, if you only drive on an isolated site and keep the speed at a minimum and ensure that nothing extraordinary will ever happen (like an uninformed person using the car), probably.

Your method “works” as long as you control every single aspect of the system and don't overlook anything. You've already failed to meet the second requirement (you didn't know the NO_BACKSLASH_ESCAPE setting), so what makes you think it's all good now? Are you sure you haven't overlooked another setting? Can you promise that anybody working on the system now and in the future knows the odd constraints and will never make a mistake?

Personally, I wouldn't bet my money on it. When we write applications for the real world, we want them to work under all conditions, even if we don't control every single setting, even if somebody makes a mistake, even if we overlook some detail. In other words, the solution should be robust, because many decades of software development have shown that humans are in fact fallible. Prepared statements are very robust, mysql_real_escape_string() is also fairly robust. Your function is not. At all.

I also wonder how you've concluded that both prepared statements and mysql_real_escape_string() are “too slow”. If you have so many users that you need to worry about a few extra roundtrips, then the last thing you want is some home-made security tool. And if you don't have that many users, then I think you're simply exaggerating.