Anatomy of SQL Injection
Here's a simple SQL injection example walkthrough to demonstrate both how easy the mistakes are to make and how simple they can be to prevent with some design and programming rigor.
The sample web application
contains a simple customer data search page named SQLInjection.aspx
that is vulnerable to SQL injection. The page contains a CompanyName
input server control and a data grid control to display the search results from the Microsoft sample Northwind database that ships with SQL Server 2005 Express Edition. The query executed during the search includes a very common mistake in application designit dynamically builds a SQL query from user-provided input. This is the cardinal sin of web application data access because it implicitly trusts what the user posts, and sends it straight to your database. The query looks like this when initiated from the Search button click event:
protected void btnSearch_Click(object sender, EventArgs e)
String cmd = "SELECT [CustomerID], [CompanyName], [ContactName]
FROM [Customers] WHERE CompanyName ='" + txtCompanyName.Text
SqlDataSource1.SelectCommand = cmd;
GridView1.Visible = true;
In the intended scenario, if a user inputs "Ernst Handel" as the company and clicks the Search button, the response shows the customer record for that company, as expected. But an attacker could easily manipulate this dynamic query, for example, by inserting a UNION
clause and terminating the rest of the intended statement with comment marks (). In other words, instead of entering "Ernst Handel," the attacker would input the following:
Ernst Handel' UNION SELECT CustomerID, ShipName, ShipAddress
The result is that the SQL statement executed on the server ends up appending the malicious request. It transforms the dynamic SQL to this:
|Figure 1. Successful SQL injection: By appending additional commands and escape characters to the input, an attacker can view private data from other parts of the database.|
SELECT [CustomerID], [CompanyName],
WHERE CompanyName ='Ernst Handel'
UNION SELECT CustomerID, ShipName,
This is a perfectly legal SQL statement that will execute on the application database, returning all the customers in the Orders
table who have processed orders through the application, as shown in Figure 1
Typical SQL Safeguards
You can see now how easy it is to both create a SQL injection vulnerability in your application and to exploit it. Fortunately, as mentioned before, SQL injection can usually be prevented easily with a few simple countermeasures. The most common and cost effective way to prevent SQL injection is to properly validate all inputs in the application that are ultimately used as data access. Any input that originates with userseither directly through the web application or persisted in a data storemust be validated on the server for type, length, format and range before processing your data-access commands on the server. Unfortunately, code-based countermeasures are not foolproof and can fail when:
- Validation routines aren't properly designed.
- Validation is performed only on the client layer.
- Validation misses even a single field in the application.
An additional layer of defense to prevent SQL injections involves properly parameterizing all the SQL queries in your application, whether in dynamic SQL statements or stored procedures. For example, the code would have been safe if it had structured the query like the following:
SELECT [CustomerID], [CompanyName], [ContactName]
WHERE CompanyName = @CompanyName
Parameterized queries treat input as a literal value when executed as part of the SQL statement; thereby making it impossible for the server to treat parameterized input as executable code. Even if you use stored procedures, you must still
take this extra step to parameterize input, because stored procedures provide no inherent protection from SQL injection over embedded queries.
Even with these simple fixes, SQL injection is still a big problem for many organizations. The challenge in your development team is to educate every developer about these types of vulnerabilities, put meaningful and effective security standards in place to prevent attacks, enforce the standards and conduct security assessments to validate that nothing was missed. This introduces a lot of variables in your efforts to secure your applications, so you would be much more productive if you were to select a data-access technology that renders these SQL injection attacks impossible. This is where LINQ comes in.