SQL Injection
What is SQL Injection?
SQL Injection is a technique used by attackers to exploit the way a website interacts with SQL databases. By changing the commands sent by the website, the attacker can potentially achieve a number of aims. He can obtain information he is not supposed to have access to, he can modify, corrupt or destroy information in the database, and he can break mechanisms which rely on the database (such as login mechanisms). Estimates suggest at least 1 out of 5 sites have SQL injection vulnerabilities.
What makes a site vulnerable to SQL Injection?
SQL Injection vulnerabilities occur when a website uses user-supplied input in the SQL commands it executes, without properly securing that input. If the attacker is given the opportunity to add to the command without restriction, he can potentially modify the command’s functionality. The attacker can not only modify the command, but can also potentially execute further queries which bare no particular relationship to the original. Injections do not just occur in obvious user input, but can be contained in browser supplied fields such as the user agent.
Another situation in which SQL Injection vulnerabilities can arise is when the SQL query is actually passed in the URL. This is very bad security practice (unless some very strict filtering of these queries is done on the server-side), and should be avoided. It gives the attacker complete control over the query exectuted, and requires the minimal of effort on the attacker's part.
Example of an SQL Injection attack
The SQL query to test if a user exists with a given password may look something like this:
SELECT * FROM user_list WHERE username='$name' AND password='$pass';
where $name and $pass are replaced by the user-supplied username and password. A command such as this may be used to check that a user’s login details are correct. To exploit this with an SQL injection attack, the attacker can enter no password and the username: admin'#. This creates the query:
SELECT * FROM user_list WHERE username='admin'# AND password='';
The # symbol starts a comment, and so everything after it is ignored. This causes the query to match the user with the username ‘admin’, regardless of the password. Hence, the attacker has managed to log in as admin without supplying the password.
Advanced SQL Injections
Even when using escaping input protection against injections, such as using PHP's mysql_real_escape_string() function, a number of injections may still be possible. Often queries include integers supplied by the user. It is not functionally necessary to surround these integers with quotes, but failing to do so can allow the user to inject SQL. The integers should also be validated as integers, especially when used in some places such as to parameterise LIMIT where quoting the integers would cause the query to fail. If user-input is used for column names, e.g. in ORDER BY, care must be taken. Column names are quoted with backquotes (`), which aren't escaped by mysql_real_escape_string(), so such escaping will not prevent injections. User-supplied column names should ideally be matched against a fixed set of permitted columns. The injection of wildcard characters into a LIKE can be used with malicious effect, for instance performing a DoS attack by disabling indexing on searches performed within large tables. Ultimately wildcards should be escaped, or removed from search terms, where practical.
Impact of an SQL Injection attack
A successful SQL injection attack will give the attacker some level of control over the website’s database. The attacker may be able to exercise full control over the database. This will include having the ability to read and modify data in any table in the database, and to alter the table structure at will. If inadequate encryption is used, the user may be able to gain passwords to the site itself, or obtain credit card and other private information. Furthermore, the attacker may be able to bring down the entire site if it relies heavily on the data or does not handle loss of the database gracefully. The attacker may also be able to gainfully exploit mechanisms which rely on the database, such as the login mechanism. Perhaps more serious than this is that an attacker may be able to execute system commands from SQL, and thereby exercise control over the web server. Recent innovations such as Google Gears are bringing SQL storage and SQL Injections to the client side.
Output channels
The usefulness of an SQL injection vulnerability to an attacker is somewhat dependent on the attacker’s ability to observe the effects of their actions. If the attacker is able to inject system commands and observe the resulting output, he will be able to establish an interactive shell for accessing the system. There are a number of ways in which the attacker can observe this output. If he is very lucky, the website will output it directly. The attacker may be able to observe the effect of his commands by inducing error messages which echo some aspect of it. In blind SQL injection the attacker establishes a single bit output channel where a false SQL injection query causes one page to be displayed and a true query causes another page to display. This allows the attacker to play ‘20 questions’ to determine the output of the commands through successive questions. Blind SQL injections are readily automated to make the task easier, and can even be optimised to reduce the number of requests. DNS channels can be used to by having the system perform DNS lookups with the output encoded in the URL of website to whose logs the attacker has access. Timing channels can be used to vary the response time of the website depending on the output of the commands, allowing the attacker to infer the output.
Preventing SQL Injection attacks
SQL injection attacks occur when the website expects the information it uses in SQL queries to be of a certain type (number, text, etc.), but does not enforce this. To prevent SQL injection attacks, it is necessary to put in place measures to enforce the necessary type requirements. At each place where input is used in an SQL query it should first be checked that it is of the correct type and length and rejected or modified if it is not. Note that overly long input should always be rejected or truncation attacks may be enabled, in which an attacker can cause part of the modified input to be dropped which may undo the effects of the modification. This should be carried out for all user-supplied input. Information taken from other sources, such as the database, should also be checked as it may have been previously supplied by the user. This is necessary to prevent variants such as reflected (or second-order) SQL injection in which the exploit data is first stored in the database and then later included in the vulnerable query, bypassing validation checks applied only to direct input. As an additional layer of defence, possible output channels should be closed. If using ms access sql, the following cheat sheet may prove useful when testing the strength of any defensive measures.
Another strategy for mitigating SQL injection attacks is to protect the privacy of the data in the database in the event that an SQL injection attack is discovered. This can be done by encrypting the contents of the database (or the sensitive data at least). For this, use an encryption key which is stored outside the database. This will force the attacker to perform additional work after obtaining access to the database, if he is to decrypt the sensitive data.
The attack in the wild
In August 2007, the UN website was the target of an SQL injection attack, in which the Secretary-General’s speeches were graffitied.