SQL injection continues to be the most exploited security vulnerability on the internet. A SQL injection vulnerability can put companies out of business and expose innocent people to sensitive data disclosure and damage, all due to developer ignorance or carelessness. Sadly, although there is much information available on SQL injection exploits and mitigation, SQL injection exploits continue because developers use flawed database access techniques. This article details the best, and simplest, method to prevent SQL injection in SQL Server: parameterized SQL statements.
Parameterized SQL queries and stored procedure calls (with CommandType.StoredProcedure and no dynamic SQL within proc code) prevent unintended SQL from being executed. I’ll discuss exactly why shortly. Parameterized SQL also provides many benefits besides security. Notably:
- allows quotes in data without programmatic escaping
- avoids the need to format date strings according to DATEFORMAT session settings
- handles decimal separators automatically
- improves performance due to plan reuse
- provides cleaner code
Most articles on SQL injection focus on validating user input rather than parameterized queries. Input validation certainly has benefits related to data integrity and user-friendly validation messages but it is at best a secondary defense against injection compared to parameterized queries. Input validation using a blacklist of prohibited keywords and characters is especially weak.
SQL Injection Overview
I am puzzled by the number of examples in articles and forum posts that show building SQL statements with literals using a string concatenation technique. I suspect the main reason for this practice is that developers write code that generates the exact same ad-hoc SQL statement they would write using a query tool like SQL Server Management Studio or Visual Studio. This leads to the nasty habit of not using parameters. For example, a developer might develop and test a query like this in SSMS:
SELECT FirstName, LastName, EmailAddress FROM dbo.Customer WHERE CustomerID = 12345;
After testing the query, the C# developer includes the SQL statement in the application code but, instead of a hard-coded value for CustomerID, the desired value is concatenated with the remainder of the SQL statement as a literal.
var command = new SqlCommand( "SELECT FirstName, LastName, EmailAddress" + " FROM dbo.Customer " + " WHERE CustomerID = @CustomerID;" ,connection); command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = int.Parse(textBoxCustomerID.Text);
The query runs perfectly well and returns the expected result in testing. It is not until after production deployment that a script kiddie or hacker finds the SQL injection vulnerability. A malicious person can easily manipulate this SQL statement in ways unintended by the developer. For example, a hacker could enter CustomerID value “1 OR 1 = 1” in the text box to harvest the names and email addresses of all customers in the database. This vulnerability can also be exploited to execute additional SQL statements in the same batch, which is often used to update data with malicious html script that is subsequently returned and executed by unsuspecting client browsers. There are many ways to exploit a SQL injection vulnerability that result in sensitive data disclosure and/or database manipulation. I won’t detail those here but rather recommend a very simple defense; use a parameterized statement instead of literals.
Mitigating SQL Injection
Below is a functionally identical parameterized query.
SELECT FirstName, LastName, EmailAddress FROM dbo.Customer WHERE CustomerID = @CustomerID;
This query can be incorporated into the C# application code with the parameter and corresponding value added to the parameterized SQL command as a strongly-typed parameter:
var command = new SqlCommand( "SELECT LastName" + " FROM dbo.Customer " + " WHERE CustomerID = @CustomerID;" ,connection); command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = int.Parse(textBoxCustomerID.Text);
As you can see, the effort needed to do the same job with a parameterized command is about the same as the string concatenation method, yet the security benefits are immeasurable.
Why Parameters Prevent SQL Injection
Parameterized SQL is more secure for a couple of reasons. In the case of an ad-hoc query, the SQL statement with parameter markers is hard-coded in the application (or generated via an ORM framework) and passed to SQL Server as-is. Parameter values are passed separately rather than inside the SQL statement itself. This ensures the SQL statement cannot be modified by the values provided.
Similarly, a stored procedure call (using CommandType.StoredProcedure) sends only the stored procedure name to SQL Server with separately passed parameter values. Using stored procedures also allow one to more strictly adhere to the security principal of least privilege. By granting only stored procedure execute permissions, permissions on indirectly referenced objects are not needed as long as the ownership chain is unbroken. This practices limits ad-hoc access to underlying data should an attacker find a back door into the database under the application security context. Personally, I’m a staunch advocate of stored procedures for this and other reasons too. Stored procedures provide a well-defined database interface, allowing queries and database schema to be refactored without breaking changes.
I should add that it is perfectly acceptable to build a SQL statement dynamically in either application code or stored procedures, but only if parameterized and actually needed for the task at hand. A common use case is a dynamic search query that is constructed based on user criteria. Instead of coding a separate query for each possible search permutation, one can add parameterized WHERE clauses as needed. Under no circumstances should a SQL statement string be built with concatenation of untrusted values.
Use Windows Authentication for SQL Server database access. This practice eliminates the need to store security credentials in application configuration files. If SQL authentication must be used, protect the credentials with encryption. This is easily done in .NET by encrypting the connectionStrings section of the app.config file. See http://msdn.microsoft.com/en-us/library/ms254494.aspx.
Adhere to Principal of least privilege. Never use a privileged account, such as a sysadmin role member, for routine application access. Grant permissions only on database objects which are used directly by applications. Use stored procedures so that one can leverage ownership chaining, eliminating the need to grant permissions on tables directly.
Specify strongly-typed parameters. The parameter data type can be inferred (e.g. using SqlCommand.Parameters.AddWithValue method) but a best practice is to explicitly specify a parameter data type that matches the underlying column data type and maximum length. These practices will minimize the size of the SQL Server procedure cache and avoid implicit conversions that can negatively affect performance.