Importance SQL injection
According to a 2006 report from Fortify Software, the top 3 software security vulnerabilities are:
1. Cross-Site Scripting (21.5%)
2. SQL Injection (14.0%)
3. PHP includes (9.5%)[1]
The impact of SQL injection attacks
1) Gathering of sensitive data to manipulating database information.
2) Executing system level commands to denial of service of the application
The impact also depends on the database on the target machine and the roles and privileges the SQL statement is running with. Basically there are two types of SQL injections, the First Order Attacks and the Second Order Attacks. The outcome of first order SQL injection attack is immediate, and we commonly refer to that type of attack as SQL injection attacks. The second order SQL injection is an attack that takes into consideration the web application logic and tries to find how someone can use to an SQL query to cause similar damage using not show obvious means (e.g. SQL inject the cookie!). For example, if user input is stored in a database by using one ASP page, and then the user input is retrieved from the database and is used to construct dynamic SQL statements in a different ASP page, an attacker can inject SQL commands into an SQL statement and then misuse it. This is generally known as a Second Order SQL injection vulnerability. [5]
In First Order Attacks, the attacker can simply enter a malicious string and cause the modified code to be executed immediately. In the Second Order Attacks The attacker injects into persistent storage (such as a table row) which is deemed as a trusted source and the attack is subsequently executed by another activity.
When dealing with SQL injection issues, the following three things should be taken into consideration:
1) User input filtering (black and white listing for first order SQL injections).
2) User output filtering (black and white listing for second order SQL injections).
3) User privilege attributes (for limited access to database functionality).
White listing versus Black listing
One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from a whitelist of safe values or identify and escape a blacklist of potentially malicious values. White listing can be a very effective means of enforcing strict input validation rules compared to black listing.[2] Because when you do white listing you are allowing only the characters your application can use to pass through. But when you are doing black listing you have to constantly update your black list with all new malicious input found (e.g. detect all possible input based the database provided functionality).
Parameterized SQL statements
In order to talk about parameterized SQL statements, someone has to understand what a parameterized stored procedure and a parameterized prepared statement. Both Stored Procedures and prepared statements are compiled and cached by the database and? that is why it reduces the processing burden on the client.
Parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, blacklisting is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers can:[2]
1) Target fields that are not quoted
2) Find ways to bypass the need for certain escaped meta-characters
3) Use stored procedures to hide the injected meta-characters
In VB. NET you can create a safe parameterized select statement using the following API:
<%@ import Namespace=”System.Data” %>
<%@ import Namespace=”System.Data.SqlClient” %>
<%@ Import Namespace=”System.Web.Security” %>
<%@ Import Namespace=”System.Text.RegularExpressions.Regex” %>
A code example would be something like that:
Sub LoginBtn_Click(ByVal Sender As Object, ByVal E As EventArgs)
Dim passwordFormatMatch As Match = Regex.Match(UserName.Text, “[a-zA-Z][0-9]“)
Dim connectionString As String = “Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=E:\mydb\test.mdb”
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)
Dim queryString As String = “SELECT [Users].[Usernames], [Users].[Passwords] FROM [Users] WHERE (([Users].[Usernames] = @Usernames) AND ([Users].[Passwords] = @Passwords))”
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_usernames As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_usernames.ParameterName = “@Usernames”
dbParam_usernames.Value = UserName.Text
dbParam_usernames.DbType = System.Data.DbType.[String]
dbCommand.Parameters.Add(dbParam_usernames)
Dim dbParam_passwords As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_passwords.ParameterName = “@Passwords”
dbParam_passwords.Value = UserPass.Text
dbParam_passwords.DbType = System.Data.DbType.[String]
dbCommand.Parameters.Add(dbParam_passwords)
dbConnection.Open()
Dim dataReader As System.Data.IDataReader =? dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Prepared Statements
1. Instances of PreparedStatement contain an SQL statement that has already been compiled. This is what makes a statement “prepared.”[6]
2. The SQL statement contained in a PreparedStatement object may have one or more IN parameters. An IN parameter is a parameter whose value is not specified when the SQL statement is created. Instead, the statement has a question mark (”?”) as a placeholder for each IN parameter. The “?” is also known as a parameter marker. An application must set a value for each question mark in a prepared statement before executing the prepared statement.[6]
Stored procedures
Stored procedures are implemented differently from database to database. For simplicity when referring to stored procedures we will mean Microsoft SQL Server 2000 stored procedures. A stored procedure is a group of Transact-SQL statements compiled into a single execution plan[7], and can provide customized granular access control, based on privileges of the user? account accessing the database.
That is a code example:
IF (@QuantityOrdered < (SELECT QuantityOnHand
FROM Inventory
WHERE PartID = @PartOrdered) )
BEGIN
-- SQL statements to update tables and process order.
END
ELSE
BEGIN
-- SELECT statement to retrieve the IDs of alternate items
-- to suggest as replacements to the customer.
END
Code example [7]
In SQL Server version 6.5 and earlier, stored procedures were a way to partially precompile an execution plan. At the time the stored procedure was created, a partially compiled execution plan was stored in a system table. Executing a stored procedure was more efficient than executing an SQL statement because SQL Server did not have to compile an execution plan completely, it only had to finish optimizing the stored plan for the procedure. Also, the fully compiled execution plan for the stored procedure was retained in the SQL Server procedure cache, meaning that subsequent executions of the stored procedure could use the precompiled execution plan.[7]
Stored procedures Versus Prepared Statements
Prepared Statements pros:
1. Prepared Statements are some sort of precomputed SQL queries and are always type-safe. That is the reason why Prepared Statements are always not vulnerable to SQL injection.
2. Don’t tie you in to a particular database vendor if you write portable SQL code.
Prepared Statements cons:
1. Prepared Statements do not provide customized access control based on user privileges.
Stored procedures pros
1. Stored procedures can provide customized access control based on user privileges.
Stored procedures cons
1. Stored procedures are not always safe from SQL injection, only parameterized stored procedures are safe from SQL injection.
2. Do tie you in to a particular database vendor by splitting the web application business logic into both the web application and the database.
Single point of failure (SPF)
Handling SQL injections only as an input validation issue is wrong. Trying to make sure that you are clean from SQL injections by filtering user inputs introduces a bad security practice called single point of failure and gives you a false sense of security. Now depending on the current situation you might not have to use white listing and black listing at the same time or might not be able use both for various reasons.
To make it more clear, if you have a multi tier web application you can set up different filters into different tires (by using the term tier I mean different machines or software layers, e.g. one filter for the web application server and one filter for the database server). The following diagram is self explanatory:
What is a filtering?
It gets a little confusing to completely understand what is filtering. A filter generally speaking can only do three things:
1) Remove characters (e.g. Look for “‘ or 1=1 –” character patterns).
2) Replace characters (e.g. replace ‘ with ”).
3) Encode characters (e.g. Similar to XSS encoding).
Now when we are talking about black list filtering we mean that you must have a specified list of character patterns that you want to cut off (e.g. ‘ or 1=1 –, ‘ or 9=9 — e.t.c) or have a list of illegal characters that you want to cut off (e.g. –,=,+ e.t.c).White listing is the opposite , which means that you must have a set of allowed characters that you can forward to the web application without causing any security issues.
When and why?
You can use white listing and black listing at the same time when your white list must allow some characters that can be used to create unwanted character patterns. For example you web application must allow the single quote, the equal sign, the dash character and numerical values (meaning ‘,1,= and -), but cannot allow to create a single quote , or one equals one dash dash, meaning the ‘ or 1=1 — character pattern.
Examples:
User input –> ‘ or 1=1 — (White listing) –> OK –> (Black listing) –> Blocked (bad character pattern)
User input –> ” or 1=1 — (White listing) –> Blocked (bad character “)
Some real world examples (ASP .NET 1.1)
Included in this post are VB.NET samples that can be used to screen incoming query-string, form and cookie values for potential Sql injection values. However because valid input data varies from website to website, it is not possible to write a one-size-fits-all screening mechanism. You can modify the sample code included in this post to tighten or loosen the character sequences as appropriate for your website. [3]
Also as a reminder, if a website makes heavy use of dynamically constructed Sql (as opposed to parameterized Sql or parameterized stored procedures) it is a best practice to escape all single quotes contained in un-trusted web input. Since it is not possible to make this replacement using the HttpModule/BeginRequest approaches shown below, you can instead scrub a website’s code and perform the escaping in all places where dynamic Sql is being built.[3]
//C# snippet
private string SafeSqlLiteral(string inputSQL)
{
return inputSQL.Replace("'", "''");
}
You can screen all incoming query-string, form and cookie values by running code during the BeginRequest event. A central location to register this code is in a website’s global.asax file. The sample code below will check incoming data and automatically redirect to a page called “Error.aspx” if suspicious character sequences are found.[3]
First you will need to add a new namespace import at the top of your global.asax file:
<%@ Import namespace="System.Globalization" %>
Next place the following variable definition and private function somewhere in your global.asax file between the <script> tags:
//Defines the set of characters that will be checked.
//You can add to this list, or remove items from this list, as appropriate for your site
public static string[] blackList = {"--",";--",";","/*","*/","@@","@",
"char","nchar","varchar","nvarchar",
"alter","begin","cast","create","cursor","declare","delete","drop","end","exec","execute",
"fetch","insert","kill","open",
"select", "sys","sysobjects","syscolumns",
"table","update"};
//The utility method that performs the blacklist comparisons
//You can change the error handling, and error redirect location to whatever makes sense for your site.
private void CheckInput(string parameter)
{
CompareInfo comparer = CultureInfo.InvariantCulture.CompareInfo;
for (int i = 0; i < blackList.Length; i++)
{
if (comparer.IndexOf(parameter,blackList[i],CompareOptions.IgnoreCase) >= 0)
{
//
//Handle the discovery of suspicious Sql characters here
//
Response.Redirect("~/Error.aspx"); //generic error page on your site
}
}
}
You then need to register the HttpModule with ASP.NET.
If you are running ASP.NET 2.0 on IIS6, or ASP.NET 2.0 on IIS7 in Classic Mode, place the bolded module registration shown below inside of the system.web/httpModules section:
<system.web>
?€¦
<httpModules>
?€¦
<add name="SampleSqlInjectionScreeningModuleCS" type="Sample.SampleSqlInjectionScreeningModuleCS"/>
?€¦
</httpModules>
?€¦
</system.web>
However if you are running ASP.NET 2.0 on IIS7 in Integrated Mode, you instead need to place the bolded module registration shown below inside of the system.webServer/modules section:
<system.webServer>
?€¦
<modules>
?€¦
<add name="SampleSqlInjectionScreeningModuleCS" type="Sample.SampleSqlInjectionScreeningModuleCS" preCondition="managedHandler"/>
?€¦
</modules>
?€¦
</system.webServer>
Lastly place the following function definition somewhere in your global.asax file between the <script> tags. This is the function definition that tells ASP.NET to run string checks during the BeginRequest event. If your global.asax file already has a function called Application_BeginRequest, you should instead place the contents of the function definition below into your existing version of Application_BeginRequest.[3]
void Application_BeginRequest(object sender, EventArgs e)
{
foreach (string key in Request.QueryString)
CheckInput(Request.QueryString[key]);
foreach (string key in Request.Form)
CheckInput(Request.Form[key]);
foreach (string key in Request.Cookies)
CheckInput(Request.Cookies[key].Value);
}
Conclusion
When you have a system in production it is everyone responsible to maintain secure the web application system. If you have multiple security defenses then the web application can remain secure even lots of modifications have been made to the system.
Reference [1]:http://st-curriculum.oracle.com/tutorial/SQLInjection/html/lesson1/les01_whylearnit.htm
Reference [2]: http://www.owasp.org/index.php/SQL_injection
Reference [3]: http://forums.asp.net/t/1254125.aspx
Reference [4]: http://www.iec-usa.com/Browse05/GLSS.html
Reference [5]: http://support.microsoft.com/kb/954476
Reference [6]: http://java.sun.com/j2se/1.5.0/docs/guide/jdbc/getstart/preparedstatement.html
Reference [7]:? http://msdn.microsoft.com/en-us/library/aa174792.aspx#sql:stored_procedure
