Signs that a SQL statement is dangerous

Question

Hi,

I want to develop a function in PHP that checks how dangerous a SQL statement is. When i say dangerous i mean, certain symbols, characters or strings that are used to get data from a database that the user shouldnt see.

For example:

SELECT * FROM users WHERE userId = '1'

can be injected in several ways. Although i clean the params, i also want to monitor how safe the query is to run.

Thanks in advance

Answer 1

You're trying to find a fix for a problem that shouldn't exist. You should use prepared (precompiled) queries, then you don't ever have to worry about SQL injection and escaping as the query itself is fixed and only the arguments are variable.

See here for an example on using them in PHP: http://mattbango.com/notebook/web-development/prepared-statements-in-php-and-mysqli/

Another advantage is that it's faster too, at least for MySQL, as the server doesn't have to parse the query every time.

Answer 2

This article has some good examples of preventing sql injection, and good explanations of sql injection problems.

Answer 3

You don't “clean” the params. That will break your data. Why should someone called “O'Reilly” not be allowed to enter their name into your database?

To put any string into an SQL string literal, you need to escape it, or, better use parameterised statements so you don't have to worry about SQL injection. “Sanitising” at the input side is the wrong approach: it adds needless limitations, and doesn't solve the whole problem.

If you are creating an SQL statement which has any variable string in it that you haven't escaped, it is unsafe, period. Whether it's the simplest SELECT *, or a massively complicated load of joins and subqueries, it only takes one injection to make you vulnerable.

Answer 4

I agree that Parametrized Queries is the best way to go. However most php/mysql applications use mysql_query(), and most web applications also vulnerable to some form of sql injection.

Suhosin Hardened PHP is installed by default on many LAMP systems and it has a feature called "experimental SQL injection heuristics", but it doesn't break any exploits that I know of. A better solutions is a Web Application Firewalls(WAF) which is looking for attacks like sql injection in the raw HTTP query. WAFs are required by the PCI-DSS are a commonly used on the internet today because they work.

There is an application called GreenSQL which is betting on the fact that injected queries look different. For the most part this is a safe bet, but an SQL is a free formed Declarative language and there are many ways that an attacker can rewrite a query to perform the same attack. In short this type of secuirty will stop some attacks, but it is flawed when compared to Parametrized Queries. WAF's suffer from the same problem as GreenSQL, its possible to encode or obfuscate an attack such that it slips past the massive library of regular expressions used to detect the attacks. Bobince's answer to this question cracks me up, his point is also true for the exploitation process.

Answer 5

There are three areas you want to focus on:

1. Sanitise your input. This doesn’t mean stripping out characters, it means defining an acceptable. whitelist and rejecting exceptions
2. User parameterised SQL statements rather than building up a concatenated string of syntax and values.
3. Secure the data layer as far as you can by applying the principle of least privilege and restricting public user access to only the absolutely essential functions i.e. not allowing write access to many of the tables.

You might find this useful: OWASP Top 10 for .NET developers part 1: Injection

It’s written for .NET devs but the principles transfer equally to PHP.