| [Originally published in MySQL Ezine May 2007] ABSTRACT: In a SQL injection, attackers try to corrupt your data by "injecting" destructive SQL into a statement your application has prepared for execution against your database. Learn simple ways to prevent this. SQL injection is a method for attacking databases. The attacker "injects" elements into your program's SQL in order to bypass authorization or damage the database. Web sites that send SQL commands to databases are particularly vulnerable to SQL injection, because they often rely on dynamic SQL, and because it can be easy to mount millions of such attacks until one succeeds. Here is a simple example. A PHP page asks the user for a name and a password, then sends this to the database...
Is it as innocuous as it looks? Suppose a user enters something like this as a user name...
When your application plugs that entry into your SQL, the command becomes...
Your intruder just retrieved all rows and columns of the mysql.user table. Not exactly what you had in mind. Or a malevolent user might supply this username...
whereupon your application sends this command to the database...
If your application's connection supports multiple SQL commands in a single query call, all your customer data just went away. LEVEL 1 DEFENSE: NEGATIVE INPUT FILTERS The simplest way to prevent this sort of injection is to search the SQL string for semi-colons and double dashes, and remove them before passing the statement to the database. That's easy in an adequate application language, for example in PHP...
If $qry has offending characters, sending $protectedqry to the database raises a MySQL error. That provides one level of protection. Better still, search the string for double dashes and semi-colons, and if either is found then refuse to send the query to the database. If you want to be really thoroughgoing, you could blacklist the IP address that launched the attack. Now you are fully protected against attacks that use double dashes and semi-colons. Have you covered all possible attacks? Not a chance, human ingenuity having no practical limit. For example, a favorite trick we haven't touched on is introduction of malevolent WHERE clauses. LEVEL 2 DEFENSE: POSITIVE INPUT FILTERS The attacker has to succeed just once. If your database is to be safe, you must succeed every time. You are on better logical ground enforcing a simple positive validation pattern than looking for a limitless number of dangerous or invalid patterns. Positive input filters improve your chances of success enormously. For example, you could decide to accept only alphanumeric characters in user names and passwords. It is easy to enforce that rule in any adequate app language. In PHP, for example, you can write ...
You can formulate more stringent tests based on specific input requirements. LEVEL 3 DEFENSE: OUTPUT FILTERS Application languages provide generic tools for cleaning up submissions to your database. In PHP use mysql_real_escape_string() ...
but that is not enough. For example if a user can specify the last parameter of a dynamic query as something like 1 select user,password from mysql.user, passing user input through mysql_real_escape_string() will not protect against many injections. You need positive output filters, in this case, if an integer value is required from the user, passing the value to intval() will filter out trailing injection code.LEVEL 4 DEFENSE: ENCAPSULATION Enterprise RDBMS policies usually require that all such protective logic be encapsulated in stored procedures. Stored procedures offer no anti-SQL-injection magic—within them, just apply the above principles. One kind of encapsulation, the prepared statement, does have protective value because it organises input and output filters, for example in PHP ...
SUMMARY To stop SQL injection attacks in their tracks, apply simple positive and negative input filters, and escape possibly problematic characters in what you send to the database. |