SQL injection is a serious threat to any vendor’s SQL database in which applications use dynamic SQL (i.e., SQL compiled while the application is running). A hacker knowledgeable of SQL can exploit weaknesses presented by such applications, but good application design can mitigate the risks. Instead of focusing on satisfying just the literal business requirements, designers must carefully consider how an application can be used by a hacker in unintended ways. Additionally, DBAs must work with developers to grant only the most minimal of permissions to an application.

Creating a Test Application

It is easier to understand how SQL injection works if you create a simple test application, the SQL Server Northwind database. To simplify creation of the application, the Employees table is used as a list of authorized application users. A user is authenticated by entering a first name and last name found in the Employees table. Think of first name and last name as mapping to username and password in a real-world application. The user can login by using dynamic SQL, a parameterized query, or a stored procedure.

You can download the ValidateUserProc.sql stored procedure and the application code as either injectSQL.cs or injectSQL.vb.

Although this sample application is a Windows application, it could be a Web application or even a Java application. The vulnerability is a direct consequence of using dynamic SQL and completely independent of the operating system, database vendor, and programming language used to write the application.

Understanding the Test Application

Superficially, it appears that the test application works as intended and fulfills the basic business objective. Users who know a valid first name and last name are authorized by the application. Those who do not know a valid first name and last name are rejected. In the real world, the application would authenticate the user only by using only one method; it would not provide a choice of methods. The test application provides a choice of different database calls to demonstrate both the vulnerability to SQL injection as well as how to protect against it. The dynamic SQL approach to user authentication creates the SQL injection vulnerability, but both the parameterized query and the stored procedure protect against SQL injection.

To keep the application simple, only a single screen was created. After entering a valid username and password into a real application, the user would, of course, be taken to another screen, since the test application display indicates the user is “authorized.”  An invalid first name and last name would cause the application to display “unauthorized.”

Whether using dynamic SQL, a stored procedure, or a parameterized query, the SQL statement being executed by the test application is logically equivalent to this:

select EmployeeID from Employees
where FirstName = 'Andrew' and LastName = 'Fuller'

A valid first name and last name results in the EmployeeID being returned from the query. An invalid username and password combination results in no data being returned.

Understanding Dynamic SQL

When a program builds and executes a SQL string at execution time, it is known as dynamic SQL. Inspecting the application code does not provide an accurate indication of which SQL statement is actually executed; it only provides an indication as to the intent of what should be executed. Only SQL Profiler indicates what is actually executed. Examine the following application code that constructs the SQL string for the test application:

.CommandText = "select EmployeeID from Employees where FirstName = '" + _
               FirstName.Text + "' and LastName = '" + LastName.Text + 
"'"

It appears to be logically equivalent to the SQL statement described in the previous statement. If the inputs are Andrew and Fuller for first name and last name, respectively, then the SQL Profiler indicates the actual SQL executed is:

select EmployeeID from Employees
where FirstName = 'Andrew' and LastName = 'Fuller'

This is completely consistent with the intended design of the application. Invalid inputs were detected, and the user is denied access.

The Altered Logic Threat

The test application accepts the input first name and last name through simple text boxes, and the user is free to enter text other than first names or last names. A hacker with basic SQL knowledge can be authenticated without even attempting to guess a valid first name and last name.

The user entered the following string and was authorized:

' or 1=1—

By placing a partial SQL statement into the First Name textbox, a hacker “injects” the SQL fragment and thus alters the SQL statement that is executed. The injected SQL fragment actually consists of three different fragments, each with a different purpose:

1. The single quote closes out the First Name = ‘ fragment of the template query. This is done to maintain the modified query’s syntactical correctness.
2. The or 1=1 fragment causes the query to always return rows (assuming of course that the table has rows).
3. The double dash is a SQL inline comment that causes the entire remainder of the dynamically built SQL statement to be ignored. Any input in the Last Name textbox is ignored.

The SQL Profiler reveals what was actually executed:

select EmployeeID from Employees
where FirstName = '' or 1=1--' and LastName = ''

The application design intends for first names and last names to be entered, but SQL injection alters the SQL logic and makes them superfluous.

The Multiple Statement Threat

Unauthorized access to an application has different levels of severity depending on the application’s purpose. Sometimes people incorrectly rationalize the potential harm from security threats. For example, if a Web application provides fee-based access to publications, unauthorized logins could be dismissed as lost revenue by rationalizing that the cost to impose additional security features outweighs the cost of lost subscriber revenue. After all, there is a high probability that a person who hacks into a fee-based publication service won’t pay to access the site if the hacking attempts fail. However, such reasoning is naïve and fatally flawed — what if the SQL-savvy hacker decides to inject completely new SQL statements?

Consider the following SQL code:

' or 1=1;update Products set UnitPrice = 0—

Once again, the SQL Profiler reveals what was actually executed — actually, two separate SQL statements:

select EmployeeID from Employees
where FirstName = '' or 1=1;

update Products set UnitPrice = 0--' and LastName = ''

A semicolon is a valid SQL character for separating one SQL statement from another. It is particularly useful when multiple statements are entered on a single line or into a single string. A semicolon tells the SQL parser that the complete string comprises individual SQL statements to execute separately.

The hacker is not limited to injecting DML statements (insert, update, delete). How about a drop table statement? Assuming that the application has rights to drop tables, a hacker could use drop table statements to remove tables from the database. Consider the following input:

' or 1=1;update prices set cost = 0;drop table audit_trail;shutdown--

Not only would the audit_trail table be dropped, but the database would be shutdown immediately afterwards.

Prevention Through Code

To provide the greatest security for your databases, you must use multiple techniques. The first line of defense is prevention at the user interface.
Whenever you are working with a database, you must first understand your data, because the better you understand your data, the better you will be able to protect it. In the test program, the FirstName column of the Employees table is used as if it were a username in a table of usernames. This column has a maximum length of 10 characters, but the test program does not limit user inputs to 10 characters. This is an egregious oversight. The worst attacks illustrated in this article could easily have been prevented by limiting the input to 10 characters. Still, not all input fields are short, so input length checking is only part of an overall defense. What if the maximum field length were 20 characters and the program restricted inputs to a maximum of 20 characters? Here is an attack that only requires 19 characters to shut down the database:

' or 1=1;shutdown—

Assuming that characters such as semicolons and double dashes are not valid in an input field, then regular expression validation can be used to detect the invalid characters and reject the input. Not only is restricting the set of valid input characters a Procrustean solution, a a very clever hacker could use the SQL char function to provide the value of an individual ASCII character without explicitly having the character in the injected SQL input. Despite the limitations of rejecting input based on certain characters, it should be used when it is appropriate. Visual Studio.NET has a regular expression validator control that greatly simplifies using regular expressions in ASP.NET Web pages.

Data type checking is helpful in detecting rogue input. User interfaces often accept date, time, and numeric input in text fields, and although users can type whatever they want in a text field, programs can check the input data to see if it is the correct data type. For example, if an input textbox is mapped to the EmployeeID column, then you should check any user input to see if it is integer data. Only if the input is of the correct data type would the input be passed to the database server for processing. All of the rogue statements shown above would fail an integer data type validation check.

The fundamental flaw of dynamic SQL is not that rogue inputs are allowed, but that rogue input can be executed. Dynamic SQL is convenient for developers, but it does not lock down the actual SQL during the application design stage.
Prevention Through Stored Procedures

Stored procedures are compiled when they are created; the SQL statement is frozen at creation time. Using the first rogue SQL fragment of

' or 1=1—

with the Stored Proc Login button, SQL Profiler reveals what is actually executed:

exec ValidateUser @FirstName = N''' or 1=1--', @LastName = N''

Where @FirstName contains the following characters: ' or 1=1--

No matter what the inputs for @FirstName and @LastName, the stored procedure will always execute only the select statement defined when the stored procedure was created. The SQL statement that is executed can never change based on the inputs. This stored procedure accepts two inputs, both strings that, no matter what those input strings contain, are always treated as just strings. Even a semicolon is treated as just another character, not as a SQL statement separator.

It is critically important to understand that a stored procedure does not protect against SQL injection if the stored procedure itself uses dynamic SQL. A stored procedure that calls sp_executesql to build the SQL string at execution time is just as vulnerable as an application that builds a SQL string at execution time. A point of clarification is needed: A stored procedure is not necessarily vulnerable to SQL injection if it calls sp_executesql. It depends entirely on how sp_executesql is used.

Here is what the SQL Profiler shows is executed when there is an attempt to bypass authentication while using a parameterized query:

exec sp_executesql
N'select EmployeeID from Employees
where FirstName = @FirstName and LastName = @LastName',
N'@FirstName nvarchar(4000),@LastName nvarchar(4000)'
, @FirstName = N''' or 1=1--'
, @LastName = N''

As you can see, a parameterized query is similar to a stored procedure in that it treats the inputs as parameters, not as strings that can alter the text of a query string. If you download and examine the code samples, you will see that the code to make a parameterized query is very similar to the code to call a stored procedure.

There is a misconception that dynamic SQL is required whenever the number of items in a SQL “where” clause is variable. This is not necessarily true. Consider the case of a search page for real estate listings. A customer is presented with multiple search criteria (price, number of bedrooms, bathrooms, and so on). Not all search criteria would be used at all times, so the SQL “where” clause would change as the search criteria change. Dynamic SQL is all too often used in a case like this. But you can enhance the code that constructs a dynamic SQL statement at runtime to build a parameterized query instead. It is also possible to build a stored procedure that processes a varying number of “where” clause restrictions, since, although a parameterized query does protect against SQL injection, stored procedures provide the greatest security. Parameterized queries require that the application be granted access to tables and views, which is not the preferred way of securing data.

When stored procedures can be used exclusively, it is not necessary to grant the application permission to any table or view.

Calling stored procedures in the .NET environment is covered in “Calling Stored Procedures from ADO.NET.”

Prevention Through Least Privileges

The most basic security concept of all is the principle of least privileges. Never grant any more privilege to a user or an application than the absolute minimum to accomplish a task. Typical end user applications should not allow application users to execute indiscriminate DML, drop tables, or shutdown databases. A hacker who attempts to drop a table, but does not have rights to do so, will not succeed in the attempt.

Conclusion

Implementing security best practices can prevent unintended access to your database. Using forethought and well-designed applications is instrumental in protecting your interests. While dynamic SQL has its uses, you should make a determination early on as to whether or not it would be the best choice. If possible, you should consider stored procedures early in the design stage, as their execution is not dependent on, nor changed by, user input. You should also thoroughly examine code to see that it does not lend itself to invasion. Developers must think like a hacker in order to fully evaluate the weaknesses in their applications.

--

John Paul Cook is a database and systems architect in Houston, Texas. His primary focus is helping large enterprise customers succeed with SQL Server, Oracle, and the .NET framework.