How to Avoid SQL Injection

SQL Injection Security Threats

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. 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 ways not intended. 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 by creating a simple test application. The target database is the SQL Server Northwind database. To simplify creation of the application, the Employees table is used as a list of authorized application users. The LastName column serves as the application’s Username and the FirstName column is used as the Password. The user can log in by using either dynamic SQL or a stored procedure. Providing a username and password for authentication by an application is known as "forms-based authentication."
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 username and password are authorized by the application. Those who do not know a valid username and password are rejected. In the real world, the application would authenticate the user only by either dynamic SQL or a stored procedure; it would not provide a choice of methods. The test application provides a choice of using either approach to demonstrate both the vulnerability to SQL injection as well as how to protect against it. The dynamic SQL approach to user authentication opens the SQL injection vulnerability. The stored procedure protects 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, whereas the test application displays “Welcome!” An invalid username and password causes the application to display “Hacker!”

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. Instead, it only provides an indication as to the intent of what should be executed. Only SQL Profiler indicates what is actually executed.

The Altered Logic Threat

The test application accepts the input Username and Password through simple text boxes. The user is free to enter text other than usernames or passwords. A hacker with basic SQL knowledge can be authenticated without even attempting to guess a valid username and password.
By placing a partial SQL statement into the Username 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 LastName = ‘ fragment of the template query. This is done to maintain syntactical correctness of the modified query.

2. The or 1=1 fragment causes the count to always return a count greater than zero (assuming of course that the table has rows).

3. The double dash is a SQL inline comment which causes the entire rest of the dynamically built SQL statement to be ignored. Any input in the Password textbox is ignored.

The Multiple Statement Threat

Unauthorized access to an application has different levels of severity depending on the purpose of the application. 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. The rationalization is 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. Such reasoning is naive and fatally flawed. What if the SQL savvy hacker decides to inject completely new SQL statements?
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 is comprised of 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, drop table statements could be injected to remove tables from the database.

Prevention Through Code

To provide the absolutely most effective security, multiple techniques are required to protect your databases. The first line of defense is prevention at the user interface.
Whenever you are working with a database, you must first understand your data so you will better be able to protect it. In the test program, the LastName column of the Employees table is used as if it were a password in a table of usernames. This column has a maximum length of 20 characters, yet the test program does not limit user inputs to 20 characters. This is an egregious oversight: The worst attacks illustrated in this article could easily have been prevented by limiting the input to 20 characters. Not all input fields are short, so input length checking is only part of an overall defense.
Assuming that characters such as semicolons and double dashes are not valid in a username, 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, there exists the possibility of a very clever exploit using 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 validate 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. 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 the Password input box is mapped to the EmployeeID column, then any user input should be checked 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 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

No matter what the inputs for @Username and @Password are, the stored procedure will always execute only the select statement shown. The SQL statement is predefined; it will never change based on the inputs. This stored procedure accepts two inputs, both strings. No matter what those input strings contain, they are always treated as just strings. Even a semicolon is treated as just another character, not as a SQL statement separator.
Although stored procedures overcome the fundamental weakness of dynamic SQL, it comes at a price. A stored procedure must be written in advance for all possible queries, and this is not always practical. For example, a search page for real estate listings does not lend itself to stored procedures. 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 number of stored procedures required to accommodate every possible select string would be unwieldy. Dynamic SQL is required in such cases.

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 shut down 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. Forethought and well-designed applications are instrumental in protecting your interests. While dynamic SQL has its uses, a determination should be made early on as to whether or not it would be the best choice. If possible, stored procedures should be considered early in the design stage, as their execution is not dependent on nor changed by user input. Code should also be thoroughly examined 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.