In articles of this series, we have been so far providing recommendations regarding security-related configuration settings of SQL Server 2000 from the point of view of a database administrator. We have presented a set of guidelines that apply to any generic installation of SQL Server and can be relatively easily configured on the server, database, or database object level. However, typically, access to data is provided via client applications, which increases the range of potential vulnerabilities and places an equal share of responsibility for data security on software developers. This is especially important since application flaws can have just as catastrophic implications as a misconfigured or unsecured SQL Server installation. At the same time, they cannot typically be mitigated by applying hotfixes or patches (as a matter of fact, other database management systems are also vulnerable to this type of attack), but instead, require adherence to specific programming rules. In this article, we will discuss one of the most common application-based SQL Server attacks known as SQL Injection and explain how it can be prevented.
SQL Injection is based on malicious altering of SQL statements from their intended format, by exploiting weaknesses of a client application that is used to create them. Most commonly, this takes place in Internet or intranet scenarios, where users are expected to enter data via text boxes presented through a Web-page interface, which, in turn is used to populate a dynamically formed query. The most classic case of such attacks (although certainly not the only one) involves a design, in which access to a database is controlled by the username and password pair stored in one of its tables. In the case of a match, a user is automatically redirected to another Web page, from which other SQL statements can be run. One way to determine the match is to use the following query:
SELECT COUNT(*) FROM Users WHERE username='USERNAME' AND password='PASSWORD'
where USERNAME and PASSWORD are extracted from
two textboxes filled out by users during initial Web page login. If a value
returned from this query is equal to zero, the login attempt fails, otherwise
credentials are considered valid and the user is allowed to access the
database. Now, let's consider what happens if the value specified in the first
textbox is set to
' OR 1=1 --.
Our query becomes:
SELECT COUNT(*) FROM Users WHERE username='' OR 1=1 --' AND password='PASSWORD'
Clearly, in this case, the WHERE clause evaluates to TRUE, which means the value returned by the statement will be non-zero (equal to the total number of rows in the Users table), and the user will be redirected to the next page from which other statements can be executed. Similarly, if a value specified in the second textbox is modified by adding a semicolon (which designates a SQL statement separator), an attacker can execute additional, arbitrary statements, such as:
SELECT COUNT(*) FROM Users WHERE username='USERNAME' AND password='PASSWORD';INSERT INTO Users VALUES ('Hacker', '$wordfish')
Instead of running the INSERT statement (which successful completion relies on knowledge of the name and structure of the target table and having INSERT permissions to it), a hacker can attempt something less demanding but potentially more destructive, such as truncating tables or deleting their content. Regardless of the statement used, with some extra ingenuity, a hacker can gain unauthorized access to your databases (or entire server), as long as sufficient security precautions are not implemented.
Another common scenario involves the use of querystring parameters (these
are strings of characters that follow the question mark character in the URL
designation of the target Web page - such as, for example,
where the ArticleID parameter is assigned a value 666). If the value of the
parameter is subsequently passed to a dynamically formed T-SQL query, it is fairly
easy to manipulate it in order to execute malicious code (using the same
techniques as in the examples above, e.g. by following the valid Article
identifier with a semicolon and an INSERT or DELETE statement).
In order to secure your data against SQL Injection attacks, you should first note that the amount of damage that can be done is determined by the security context in which SQL statements are executed. This means that you should always follow the principle of least privileged access, granting to applications only the rights required to operate properly. If their sole purpose is to extract user information, limit permissions to a SQL account used by them to SELECT on target database objects. Another approach to limiting access rights is implementing application roles (for more information about application roles, refer to SQL Server 2000 Security - Part 5 (Application roles) article of this series).
It is also important to implement the most secure authentication method possible. The most preferred is Windows authentication, although its applicability is limited to intranet environments. In addition, Windows authentication might introduce problems in cases where delegation can not be used (issues relating to double-hop impersonation and delegation issues in relation to Windows authentication of SQL Server 2000 were presented in the SQL Server 2000 Security - Part 2 (Authentication) article of this series). If the number of application users is low, you can consider creating separate SQL Server logins for each one. Use these logins to define connection parameters to the target database from your Web application (rather than connecting to the SQL Server using a single account and checking users' credentials against one of database tables, as described in the example presented earlier in this article). This also simplifies auditing, since all users, once logged on, leave a trail of actions associated with their accounts.
Remember, always validate the user's input by testing it for invalid or suspicious characters or check the type of entered data (e.g. ensuring that a number is specified as the value of the querystring ArticleID parameter in one of the examples above). Applications should be designed to check the content of text boxes before forming a SQL statement and submitting it to SQL server, (ASP.NET offers extensive selection of validation controls). They can either reject the input or prompt a user for reentering the data, clean it up by leaving only characters regarded as "safe" (e.g. by replacing them with alternate characters), and restrict the length of text fields. Note, however, that the examples specified at the beginning of this article constitute just a small subset of a wide range of ways SQL Injection attacks can be launched, so expecting to eliminate this vulnerability by input validation alone is likely lead to failure.
A more efficient approach relies on parameterizing all data manipulation language statements by employing stored procedures. Replacing the SQL statement from our initial example with an equivalent stored procedure, and setting the username and password values as its input parameters will render the described attacks ineffective. This is due to the fact in the case of stored procedure implementation, both username and password are treated as literal strings, which means that the SELECT statements will return no rows. In addition, with stored procedures in place, you can limit users' permissions to EXECUTE, rather than relying on less secure SELECT permissions on target tables. Unfortunately, stored procedures, while less vulnerable than dynamically created SQL statements, are still not fully protected from attacks.
Finally, guard carefully information that might be revealed via error messages resulting from executing malformed SQL statements. While the initial SQL Injection attack might fail thanks to the protection mechanisms described above, an attacker might be able to design new, more elaborate and successful ones, by analyzing the content of messages returned from the Web application. Ensure that you make them as generic as possible, hiding any details that can potentially be exploited (IIS allows controlling the level of details of its error messages through standard configuration settings).