Preventing SQL Injection Attacks

Introduction

Security is the most important attribute for any system. Providing secure experience is one of the key principles in the process of gaining customer confidence for a system. Now days, almost all the websites are asking to store user’s personal information in servers to understand the customer and serve better. It’s the responsibility of an organization to confirm that customer’s data is safe and accessed in a secured manner.

In any technology stack, there are number of different approaches to develop a system. Choosing an approach depends on various factors such as budget, resource, timeline and expertise in the technology. In addition to those factors, data security is unavoidable factor in a system design and every developer should have enough knowledge on possible security attacks on the system and follow the best practices in development to protect the application from those attacks.

SQL Injection is one of the most common attacks that can be triggered against any application that talks to database. In this article, we will be discussing the various forms of SQL Injection and techniques to secure the data from those attacks with examples written in SQL Server and Asp.Net with C#.

What is a SQL Injection Attack?

SQL Injection is a technique used to run unintended SQL statements in a database. It takes the advantage of allowing un-validated user input which is merged with the SQL statement templates to generate ad-hoc SQL statements.

In most of the cases, SQL Injection is used to launch the denial of service attack on web applications. The severity of the attacks depends on the role or account on which the SQL statement is executed.

An attacker needs to know the loop holes in the application before launching an attack; attackers use input format, timing, performance and error messages to decide the type of attack suitable for an application.

Rest of the article will deal with the SQL Injection attack types, examples and techniques to prevent it.

Types of SQL Injection

SQL Injections are classified into two categories based on the time of attack execution.

First Order Attack

Attacker hits the database with malicious string attached to an input field and receives the response immediately.

This attack type is used when the application is very insecure in terms of exposing the detailed error messages to end users, not validating input and output.

Following are some examples of First Order Attack:

Let’s assume that we have a web application with a URL as shown below which displays the product details based on the query string parameter (id) value.

http://www.example.com/product/view.aspx?id=101
C# code for getting product information from database may look like this.

	String strID = Request.QueryString["ID"];
	String strSQLQueryTemplate = "SELECT * FROM tblProduct WHERE ProductId = {0}";
	String strSQLQuery = String.Format(strSQLQueryTemplate, strID);
	DataTable dtProduct = new DataTable();

	SqlDataAdapter adapter = new SqlDataAdapter(strSQLQuery, new SqlConnection("connection string"));

	adapter.Fill(dtProduct);

	productListView.DataSource = dtProduct;
	productListView.DataBind();

In the first line, query string (ID) value is assigned to a variable directly and not checking the type of user supplied value. Application expects value of “ID” to be an Integer, but user can supply a string also as shown below.

http://www.eample.com/Product/view.aspx?ID=101 OR 1=1
“OR 1=1” is always true condition and returns all the rows of in a table.
With that URL structure, SQL Statement generated will be look like as shown below.

	SELECT * FROM tblProduct WHERE ProductId = 101 OR 1=1

When the above statement is executed, all the rows from tblProduct table is returned and displayed to the user. Now, the user confirms that, there is no input validation done in the server and can execute whatever he supply in the query string. Think of what the following input can cause to the application.

http://www.eample.com/Product/view.aspx?ID=101;DELETE FROM tblProduct

Consider a shopping cart application, typical requirement for order summary page will be; Customer can see the items in his cart and can’t see other customer’s order summary. URL for order summary can be similar to following one.

http://www.eample.com/order/view.aspx?ID=1001
Let’s assume that, we have implemented proper input validation and used store procedure /parameterized queries in the application, code behind file may look as shown below.

	String strID = Request.QueryString["ID"];

	/*******************Validation For Input******************/
	if (String.IsNullOrEmpty(strID)) return;
	if (!IsNumeric(strID)) return;


	/*******************Use Stored Procedure Or Parameterized Queries******************/
	SqlConnection sqlConnection = new SqlConnection("Connection String");
	SqlCommand sqlCommand = new SqlCommand("sp_getOrderSummary", sqlConnection);
	sqlCommand.CommandType = CommandType.StoredProcedure;

	//Bind validated input to SQL Command
	SqlParameter sqlParameter = sqlCommand.Parameters.Add("@OrderId", SqlDbType.Int);

	sqlConnection.Open();

	productListView.DataSource = sqlCommand.ExecuteReader();
	productListView.DataBind();

	sqlConnection.Close();

Still, the attacker can steal the data of others by changing the ID value in application URL as the application doesn’t have validation for checking whether the supplied order ID belongs to the current user or not. So, Application code should do this kind of validation also.

Second Order Attack

Attacker hits the database with a SQL statement to insert malicious query in a table. Later, the attacker will execute inserted query from another activity.

Let’s consider a scenario where an attacker will have to insert the malicious query into a database before running it. Think of an application where sellers are allowed upload products, upload form can have two fields for product name and description. UploadProduct.aspx code behind is using store procedures for adding new product. So, attacker can’t trigger first order attack. SQL Injected input value must be stored in the database and should be triggered from another activity to get the desired result.

Let’s assume that the application has a page to show the list of products and its specifications and using the following SQL statement to select the product specification based on name.

SELECT * FROM tblProductSpec Where ProductName = '{0}'

What could happen if the seller has entered the product name as product1';DELETE FROM tblProduct. Query for selecting the product specification will look like as shown below and will delete all the records from tblProduct table.

	SELECT * FROM tblProductSpec Where ProductName = 'product1';DELETE FROM tblProduct

I hope those examples had helped you to understand what the SQL Injection can do for an application.

Tips to Prevent SQL Injection Attacks

1. Implement strong server side validation for all user inputs including cookie values.
2. Escape or filter the special characters in user inputs.
3. Use store procedures whenever possible.
4. Use parameterized queries or ORM.
5. Avoid building SQL statements either in a class file or inside a procedure.
6. Avoid using exec command in SQL Server.
7. Avoid using sa account to connect database from the application.
8. Use low privileged account to execute queries.
9. Configure generic error page for the application and don’t display error information to user.
10. Catch all possible exceptions, implement global exception handler.

Conclusion

I hope this article is helpful to understand the SQL Injection basics and tips to prevent it.