SQL Server 2000 Security – Part 13 – SQL Injection attack

In articles of this
, 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

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:

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:

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, http://www.databasejournal.com/Articles.asp?ArticleID=666,
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).


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles