Despite being so well understood, SQL Injection remains one of the most common vulnerabilities in web applications.
What is SQL Injection
Any SQL which is dynamically created has the potential for having malicious SQL injected into it. For example, the below code receives a querystring and adds it to a SQL select string which will then be executed against the database.
//Querystring for example could be ?userid=1
var inputStr = Request.QueryString("userid");
var sqlQuery = "select createDate from users where userid = '" + inputStr + "'";
//sqlQuery is 'select createDate from users where userid = '1'
All the attacker has to do is append sql to the querystring for it to execute. Thus adding 1; delete from users;-- will cause the sql statement to be select createDate from users where userid = '1'; delete from users;-- and two statements will be executed by the database. The first statement returns the createDate for the ‘jude’ user, the malicious second statement deletes all records from the database. Note that no statements added to the sqlQuery will be executed since they are commented out using the — characters.
There are a multitude of variations on these basic scenarios, as well as executing operations on the database, attackers can retrieve sensitive data by displaying it on the site which does not require execute permissions on the database. Take for example a search box on a site which places the search term in the querystring which is then used to form the SQL query as below :
var searchTerm = Request.QueryString("searchterm");
var sqlQuery = "select * from articles where article.title like '%" + "%'";
The sql is then executed against the database and the search results are output to the webpage.
A malicious user can inject sql into the search term and return a listing of table names in the database using the below ‘search’ term:
' union select name from sysobjects where type = 'u' --
Once armed with the table name listing, the attacker can target a table to return the column names using the below term:
' union select * from information_schema.columns where table name = 'users' --
Then it is just a straightforward select statement to return the sensitive data:
' union select socialsecuritynumber from users --
There are a large number of sources of SQL Injection, as noted above querystrings and form values are common sources of injection, but cookies and Http Headers are also potential sources. Http header values in particular are commonly stored in the database, for example logging the referring url is common practice on many sites:
var referringUrl = ServerVariables("HTTP_REFERER")
var insertReferralSql = "insert into trafficreferrals values ('" + referringUrl + "')"
The Http Post can easily be tampered with using a tool such as Fiddler and malicious code entered in the Referrer field in the header. Note in this particular instance, the .NET framework provides the Request.UrlReferrer property which will pass back a valid Uri and should be clear from injected code.
Problematic Fix – Manually Cleaning Strings
Before looking at robust defenses against SQL injection it is worth looking at ad-hoc protections which are often ineffective and should not be used.
Writing custom code to ‘clean’ the input string of dangerous characters is effective in theory but needs to be done with extreme rigour and many solutions can very easily be bypassed.
For example, the potentially hazardous ‘ and – – characters can be removed as below :
var inputStr = Request.QueryString("userid");
var cleanStr = inputStr.Replace("--", "")
.Replace("'", "");
This may also be performed on SQL Server using the TSQL REPLACE function:
... set @input = REPLACE(@input, '--', '') select @input set @input = REPLACE(@input, '--', '') select @input ...
In this example the cleanStr may be consider safe of the ‘ and – – sequences. However this is not the case. Consider the below input string:
; delete from orders -'-
Note the -‘- sequence, in this case the ‘ character will be removed after the – – characters are tested for and the — character sequence will then be created leaving the below string:
; delete from orders -'-
The ‘ and – characters are not always necessary for attacks. Consider the first example we looked at
var inputStr = Request.QueryString("userid");
var sqlQuery = "select createDate from users where userid = " + inputStr
In this case the below SQL could be injected without the ‘ and – characters :
19; delete from users;
If the database has SET QUOTED-IDENTIFIER OFF then attacker could simply replace the ‘ character with ” .
Defenses Against SQL Injection
Ensure Correct DataTypes Are Input
In many of the above scenarios an integer was expected and the attacker passed in an SQL string instead.
In the application we can test the input is in the correct format :
int orderId = int.Parse(Request.QueryString["OrderId"]);
//The below code can be problematic when users have different date formats //Consider also DateTime.TryParse DateTime orderDate = DateTime.Parse(Request.QueryString["orderDate"]);
This ensures that strings are not input when integers, date formats and other datatypes are expected. This step is sometimes seen as unnecessary overhead when parameterised queries are used but it is does provide additional protection.
Parameterised Queries
Parameterised queries are a primary defense against SQL injection. The below stored procedure uses a parameter on the where clause which will prevent malicious code being injected.
CREATE PROCEDURE GetUserJoinDate
@UserName nvarchar(50)
AS
SELECT JoinDate
FROM Users
WHERE UserName = @UserName
GO
Parameters are not always effective however, and building dynamic SQL using string concatenation can introduce http://armodexperiment.com/ vulnerabilities. The below stored procedure performs the same operation by using dynamic SQL.
CREATE PROCEDURE GetUserJoinDate(@UserName nvarchar(45)) AS DECLARE @sql nvarchar(255) SET @sql = 'SELECT JoinDate FROM Users WHERE UserName = ' + @UserName + ' EXECUTE sp_executesql @sql GO
In this case the below SQL passed in as the UserName parameter will execute malicious SQL on the database:
'; delete from users --
This is a very simplistic sample and is an unusual use of dynamic SQL, dynamic SQL is more commonly used in other scenarios where the developer may believe it is the only option. For example SQL is often passed in to build such as sorting where parameters
CREATE PROCEDURE GetUsers
@Sort nvarchar(50)
AS
DECLARE @sql nvarchar(255)
SET @sql = 'SELECT UserName FROM Users ' + @Sort
EXECUTE sp_executesql @sql
GO
This allows partial sql to be passed in for sorting:
exec @Sort = 'order by UserName ASC'
This stored procedure could be purged of dynamic SQL and written as below:
CREATE PROCEDURE GetUsers
@Sort int = 1
AS
SELECT UserName FROM Users
ORDER BY CASE
WHEN @Sort = 1 THEN ( Rank() OVER (ORDER BY UserName ASC) )
WHEN @Sort = 2 THEN ( Rank() OVER (ORDER BY UserName DESC) )
WHEN @Sort = 3 THEN ( Rank() OVER (ORDER BY CreateDate ASC) )
GO
There are numerous scenarios such as this where dynamic SQL can be removed. If dymanic SQL is absolutely necessary then string concatenation should be avoided and parameters placed within the SQL which will will ensure that the parameter is properly escaped:
CREATE PROCEDURE GetUserJoinDate @UserName nvarchar(45) AS DECLARE @sql nvarchar(255) SET @sql = N'SELECT JoinDate FROM Users WHERE UserName=@UserName' EXECUTE sp_executesql @sql GO
These examples have focused on stored procedures on the database, but this applies equally to SQL created in the application :
//Vulnerable
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand("SELECT JoinDate FROM Users
WHERE UserName='" + userName + "'", conn);
//Secure SqlConnection conn = new SqlConnection(connectionString); SqlCommand command = new SqlCommand(“SELECT JoinDate FROM Users WHERE UserName=@UserName”, conn); command.Parameters.Add(new SqlParameter(“UserName”, userName);
Permissions
The database is only vulnerable to SQL injection to the extent of the user’s permissions on the SQL Server database. It is thus essential to audit and limit the permissions on the database, a detailed discussed of permissions is beyond the scope of this article but a good starting point for auditing SQL Server permissions is Auditing SQL Server Permissions
IIS Global Filtering
As an additional defense for querystring injection, IIS can filter requests for dangerous keywords. This can be done globally using the applicationhost.config (located at %systemroot%system32inetsrvconfigapplicationhost.config ). Under the tag add your rules as below:
<filteringRules>
<filteringRule name="SQLInjection" scanQueryString="true">
<appliesTo>
<add fileExtension=".asp" />
<add fileExtension=".aspx" />
</appliesTo>
<denyStrings>
<add string="--" />
<add string=";" />
<add string="/*" />
<add string="@" />
<add string="char" />
<add string="alter" />
<add string="begin" />
<add string="cast" />
<add string="create" />
<add string="cursor" />
<add string="declare" />
<add string="delete" />
<add string="drop" />
<add string="end" />
<add string="exec" />
<add string="fetch" />
<add string="insert" />
<add string="kill" />
<add string="open" />
<add string="select" />
<add string="sys" />
<add string="table" />
<add string="update" />
</denyStrings>
</filteringRule>
</filteringRules>
/* Courtesy Wade Hilmo */
This will deny requests with these keywords in the querystring.
Leave ValidateRequest On
ValidateRequest is inbuilt in ASP.NET and throws an error whenever a page is submitted with potentially malicious content. However, ValidateRequest is very aggressive and out of frustration developers frequently resort to turning it off in across the entire application. Instead, it can be selectively disabled for controls, pages in ASP.NET and for controllers in ASP.NET MVC (using [ValidateInput(false)] ). This gives more granular control over where there are potential exposures to SQL injection.
Consider Using an ORM
Object Relation Mappers such as Entity Framework or Nhibernate take control of the SQL generation and execution and generally do an excellent job of protecting against SQL Injection.