SQL injection is a type of web application security vulnerability whereby an attacker supplies malicious data to the application, tricking it into executing unanticipated SQL commands on the server. These attacks are fairly easy to prevent, but they're also both common and pernicious because they allow attackers to run database commands directly against the production data. In the most extreme cases, attackers can not only gain unfettered access to all data, but can also drop tables and databases or even gain control of the database server itself. When SQL injection is possible in a web application, it is very easy for an attacker to detect it and to then exploit it. So it stands to reason that even if SQL injection mistakes are not the most frequent security mistakes made by developers, they very well may be the most frequently uncovered and exploited in the wild. One easy way to detect SQL injection vulnerability is to insert a meta character into an input that we know an application will use to craft a database access statement. For example, on any web site that contains a search input field, an attacker can input a database meta character such as a tick mark (') and click the Search button to submit the input. If the application returns a database error message, the attacker not only knows that he has found a database-driven portion of the application, but also that he may be able to inject more meaningful commands and have the server execute them.
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. The 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 database. The query executed during the search includes a very common mistake in application design-it dynamically builds a SQL query from user-provided input. This implicitly trusts what the user posts, and sends it straight to the database. The query looks like this when initiated from the Search button click event:
protected void btnSearch_Click(object sender, EventArgs e)
Eliminate SQL Injection attacks with LINQ
Internal Use 3
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 "xyz" 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 "xyz," the attacker would input the following:
xyz' 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:
SELECT [CustomerID], [CompanyName], [ContactName] FROM [Customers] WHERE CompanyName = 'xyz' UNION SELECT CustomerID, ShipName, ShipAddress FROM ORDERS--'
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.
Typical SQL Safeguards
One can see now how easy it is to both create SQL injection vulnerability in your application and to exploit it. But, 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 users-either directly through the web application or persisted in a data store-must be validated on the server Eliminate SQL Injection attacks with LINQ Internal Use 4 for type, length, format and range before processing the 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 the 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]
FROM [Customers] 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 one uses stored procedures, they 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 the 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 efforts to secure the application, so it would be much more productive if we were to select a data-access technology that renders these SQL injection attacks impossible. This is where LINQ comes in.
At its simplest, LINQ adds standard patterns for querying and updating data in any type of data store-from SQL databases to XML documents to .NET objects. When building database-driven applications, the component of LINQ that enables developers to manage relational data as objects in C# or VB is known as "LINQ to SQL" LINQ to SQL enables one to treat data in the applications as native objects in the programming language one is using, abstracting the complexity of relational data management and database connections. In fact, one can display and manipulate database data through LINQ without writing a single SQL statement. At runtime, LINQ to SQL Eliminate SQL Injection attacks with LINQ Internal Use 5 translates queries embedded or "integrated" in the code into SQL, and executes them on the database. LINQ to SQL returns the query results to the application as objects, completely abstracting the interaction with the database and SQL. There is no faster way to eliminate the possibilities of SQL injection in web applications than to eliminate SQL from the application. With LINQ to SQL, one can do that.
Securing Data Access with LINQ
LINQ to SQL, when used exclusively for data access, eliminates the possibility of SQL injection in the application for one simple reason: every SQL query that LINQ executes on your behalf is parameterized. Any input provided to the query from any source is treated as a literal when LINQ builds the SQL query from the embedded query syntax. Furthermore, LINQ's integration with Visual Studio assists developers in building valid queries through IntelliSense and compile-time syntax checking. The compiler catches a lot of query misuse that might introduce functional defects or other types of vulnerabilities into the application. In contrast, SQL statements are parsed and interpreted on the database only at runtime. The only attack vector against LINQ to SQL is for an attacker to try to trick LINQ into forming illegal or unintended SQL. But, the languages and compilers are designed to protect from that. With that in mind, here's how one can implement the customer search example using LINQ to SQL to protect against SQL injection attacks. The first step is to create the object model of the relational data in the database. Visual Studio includes a new Object Relational Designer (O/R Designer) that enables one to generate the full object model for the database by dragging tables onto the design surface and defining relationships. To build the object model for our Customers table, create a LINQ to SQL database file in the application by selecting "Add New Item." on the project and choosing the "LINQ to SQL File" template, which opens in the O/R Designer. To automatically build the complete object model for the Customers table, select that table in the Server Explorer and drag it on to the O/R Designer design surface. In this example, the O/R Designer adds a file named Customers.designer.cs that defines the classes you'll use in code rather than writing code to interact directly with the database. After defining the object model classes for the data in the Customers table, you can query the data directly in code for the customer data search page. The Page_Load method for the LINQ-powered page (LINQtoSQL.aspx.cs), instantiates the CustomersDataContext class created by the O/R Designer, reusing the same connection string used previously in the SQLInjection.aspx page. The LINQ query below retrieves a collection of Customer objects that match my where clause:
protected void Page_Load(object sender, EventArgs e)
Eliminate SQL Injection attacks with LINQ
Internal Use 6
string connectionString =
CustomersDataContext db = new
from customer in db.Customers
where customer.CompanyName ==
Using LINQ to SQL, if I provide "xyz" as the Search value, the SQL statement generated by LINQ at runtime and executed on the server looks like this:
SELECT [t0].[CustomerID], [t0].[CompanyName],
[t0].[ContactName], [t0].[ContactTitle], [t0].[Address],
[t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country],
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CompanyName] = @p0
ORDER BY [t0].[CompanyName]}
As we can see, the WHERE clause is parameterized automatically; therefore, it's impervious to attack with conventional SQL injection attacks. No matter what values a user provides as input to the search page, this query is type-safe and will not allow input to execute commands on the server. If you input the attack string used earlier for the SQL injection exploit, the query returns no rows. In fact, the most harm that a user could do with this query is to perform a brute force attack, using the search function to enumerate all the company records in the Customers table by guessing every possible value. But even that Eliminate SQL Injection attacks with LINQ Internal Use 7 only provides the Customers values already exposed on that page, and gives attackers no opportunity to inject commands that provide access to additional tables or data in the database.
As the examples have shown, it's easy to introduce SQL injection vulnerabilities into web applications, and easy to fix them with proper diligence. But nothing inherently protects developers from making these simple, yet dangerous mistakes. However, Microsoft's LINQ to SQL technology removes the possibility of SQL injection attacks from database applications by letting developers interact directly with object models generated from relational data rather than directly with the database itself. The LINQ infrastructure built into C# and Visual Basic takes care of formulating legal and safe SQL statements, preventing SQL injection attacks and enabling developers to focus on the programming language.