In-Depth
Hacking SQL Server
SQL Injection is a fairly new method of hacking your database. Learn how it’s done—and how to protect yourself.
- By Ted Malone
- December 01, 2002
MOST NETWORK ADMINISTRATORS charged with keeping the network infrastructure
secure tend to overlook one of the most visible aspects of the environment:
Web-based applications that interact with internal database systems. Even
an innocent-looking application such as a guestbook can provide a determined
hacker with access to the internal network through a technique known as
SQL Injection.
SQL Injection used to insert a rogue SQL statement into an application,
either to perform some back-end server function or bypass application
security. The problem for most network administrators is that they’re
not usually SQL programmers and, therefore, can’t easily recognize when
a system’s vulnerable to such attacks.
This article provides a cursory background of the SQL language, explains
the various theories behind SQL Injection attacks and provides tried-and-true
methods to protect your SQL Servers. While this article focuses entirely
on Microsoft products and environments, the techniques demonstrated can
easily be applied to other systems.
To fully understand the techniques used by SQL Injection requires a basic
understanding of SQL Statements and how they’re interpreted and processed
by Microsoft SQL Server.
Anatomy of a SQL Statement
Microsoft SQL Server uses a proprietary implementation of the SQL
language known as Transact-SQL (TSQL). TSQL consists of four basic language
elements:
- Data Control Language (DCL): Used to configure various security
parameters. DCL consists of three statements: GRANT, REVOKE and DENY.
- Data Definition Language (DDL): Used to create and modify
database objects. DDL consists of three statements: CREATE, ALTER and
DROP.
- Data Manipulation Language (DML): Used to query database objects,
and insert or update data within the objects. DML Consists of four statements:
SELECT, INSERT, UPDATE and DELETE.
- Additional Language Elements: Usually flow control, logic
control or specific maintenance-related commands.
When a command is submitted for processing, SQL will parse the command
to verify that it’s syntactically correct, then compile and execute the
command, returning either the requested data or command status. SQL Server
understands the concept of command “batches,” which are multiple commands
submitted as one single batch. But, in most cases, SQL parses batches
on a statement-by-statement basis. If the statement is considered valid,
SQL executes each individual statement independent of any other statements
submitted in the batch. (There are a few exceptions to this rule but,
for the most part, this behavior is exactly what the hacker depends on).
For example, consider the following SQL Statement:
SELECT homephone, birthdate FROM employees
WHERE lastname='davolio'
When executed, this statement will return the home phone and birthday
of any employee whose last name is “Davolio.” If you were to add a statement
to the end, SQL Server would see this as two completely separate statements
contained in a single batch. For example:
SELECT homephone, birthdate FROM employees
WHERE lastname='davolio'
SELECT homephone, birthdate FROM employees
WHERE lastname='malone'
When executed, this batch returns the same information as the single
statement above, and returns a second result set with the output of the
second command. SQL Server evaluates each statement individually, even
though they were submitted as part of a batch.
Talking to SQL Server
Application developers have many choices when deciding how to connect
Web applications to back-end data stores. In Microsoft environments, the
most popular language is Active Server Pages (ASP), using ActiveX Data
Objects (ADO) to connect to the data. (Although Microsoft has updated
ASP and ADO under the .NET platform, both ASP.NET and ADO.NET are equally
susceptible to these SQL Injection techniques.) ASP and ADO are used because
they’re very simple to program, and they leverage a developer’s knowledge
of Visual Basic or VBScript (or JavaScript if the developer is so inclined).
Using ADO to make a database connection to SQL Server generally consists
of three steps:
- Create a Connection Object: Done through the use of the Server.CreateObject(“ADODB.Connection”)
command. This establishes a connection between the IIS and SQL servers.
Depending on the connection string used, it could do this via integrated
Windows authentication (using the IIS Anonymous connection) or via SQL
authentication (which requires a username/password combo encoded in
the connection string).
- Create a Command Object: Done through the use of the Server.CreateObject(“ADODB.Command”)
command. This creates a placeholder object to execute the SQL Command.
Several properties of the command object identify the type of command
being created, the connection timeout and other database-specific properties.
- Create a Recordset Object: Done through the use of the Server.CreateObject(“ADODB.Recordset”)
command. This creates a placeholder object used to hold the results
of the command object’s execution.
There are many variations on how these objects are created and utilized.
This article will concentrate on some rather simple examples. For more
information, see the ASP documentation or the ASP resources at www.15seconds.com.
Putting ASP to Work
A simple guestbook application written in ASP could consist of
an HTML form, written to post information to an ASP “responder.” The ASP
responder would retrieve the fields from the form, make the connection
to the SQL Server and execute SQL code to enter the data into the database.
The code might look something like this:
<%
Sal = Request.Form("Salutation")
Lname = Request.Form("LName")
Fname = Request.Form("FName")
Secret = Request.Form("Secret")
Email = Request.Form("Email")
City = Request.Form("City")
State = Request.Form("State")
Set Conn=Server.CreateObject(“ADODB.Connection”)
Conn.Open”Provider=SQLOLEDB;Integrated Security=SSPI;
DATABASE=Schallenge;SERVER=sanjay”
strSQL = "Insert tblChallenge values ('"
strSQL = strSQL & Sal & "','" & Fname & "','"_
& Lname & "','"
& Email & "','"
strSQL = strSQL & City & "','" & State & "','"_
& Secret & "','"
Conn.Execute(strSQL)
Set RS = Nothing
Conn.Close
Set Conn = Nothing
%>
In this example, neither a Command nor a Recordset object was used; the
SQL Command was a simple INSERT that didn’t return any data. Also note
that Integrated Security was used, which means that the IIS Anonymous
user account must have rights to log on to SQL Server and access the database.
Many applications today use simple code like this to make their database
connections. The code listed above, however, is a hacker’s dream when
it comes to being able to inject malicious code into the application.
SQL Injection at Work
In the example, the SQL statement is constructed from a simple
string concatenation. The assumption is that all fields of the form are
alphanumeric, and thus must be surrounded by single quotes. Since SQL
sees the single quotes as a string delimiter, all a hacker needs to do
is insert an extra quote, followed by any SQL code into the last text
field. For example, say the value:
')exec master.dbo.sp_addlogin 'ted'--
was entered for the Secret field. The ASP page would build the SQL statement
to look something like this:
Insert tblChallenge values('Mr','Ted','Malone',
'[email protected]','Aurora','CO','')
exec master.dbo.sp_addlogin 'ted'--')
This would cause SQL to execute the INSERT statement, possibly returning
an error because the last field was empty (if the database were so programmed).
The code would then execute the sp_addlogin command to create a SQL user
called “ted” with no password and access to the master database. If this
command were successful, then the hacker would now have a valid user on
the SQL box. He or she could mount more serious attacks through this user
(assuming, of course, that standard SQL authentication has been enabled).
For this method of injection to work, the administrator configuring the
server would have had to make some pretty obvious mistakes, such as granting
the Web account access to the master database and allowing that account
to execute the sp_addlogin procedure. That said, many applications in
use today are written such that the account being used is an administrator
or a user with elevated privileges.
Another method of SQL Injection is to bypass the logic that might be
contained within a particular SQL statement. For example, say we want
to use SQL to search for a particular value in a table. The statement
might look like this:
SELECT Lastname,Homephone from Employees
WHERE Lastname='Davolio'
This statement would return a list of all employees whose last name is
Davolio. If we made a simple modification to the statement:
SELECT Lastname,Homephone from employees
WHERE Lastname='Davolio' or 1=1
SQL will return a recordset with all employees and their home phone numbers,
due to the fact that 1 will always equal 1.
More Creative Injection Methods
In the above example, the hacker attempted to inject a SQL statement
that provided access into the system by adding additional code into a
text field. This might allow access, but the hacker still has to get through
the firewall and use some sort of client tool in order to get to any data
(which isn’t as difficult as it may seem). This method also leaves behind
an audit trail, which is something the hacker wants to avoid at all costs.
With this in mind, SQL Injection can also be used to obtain seamless access
to the database in improperly written front-end applications. For example,
the simple guestbook detailed earlier might be written such that a user
could enter a name and password to gain access to his or her personal
data for editing. The front-end form would contain the username and password
fields and the back end might be written to look like this:
<%
strUserName=Request.Form("Username")
strPassword=Request.Form("Password")
Set cnConn=Server.CreateObject
("ADODB.Connection")
Set rsUser=Server.CreateObject
("ADODB.RecordSet")
Conn.Open"Provider=SQLOLEDB;Integrated Security=SSPI;
DATABASE=Schallenge;SERVER=sanjay"
strSQL ="Select UserToken
from tblUsers where UserName='"
strSQL = strSQL & strUserName & "' AND Password='"
strSQL = strSQL & strPassword & "'"
set rsUser=Conn.Execute(strSQL)
if rsUser.EOF then
begin
response.write("Access Denied")
bolAccess=0
end
Set rsUser = Nothing
cnConn.Close
Set cnConn = Nothing
%>
The key part of this code is where the strSQL variable is being built
to create the SQL statement. The logic states that if a record is found
during the search, the username and password must have been valid; therefore,
allow access; otherwise, don’t allow access. To circumvent this logon
procedure, all the hacker needs to do is enter the following code in the
username field:
Username: Administrator '--
This will result in the following strSQL string:
Select UserToken from tblUsers
WHERE UserName='Administrator'
--AND password=''
Note the fact that after the administrator username is sought, the rest
of the statement is treated as a comment. If there’s a user account named
Administrator, the hacker is now logged into the system as that user.
You’ve seen examples of how some seemingly innocent code can be used
by hackers to inject SQL statements into applications. Obviously, we’ve
only touched the tip of the iceberg in relation to the different techniques
that could be used. The reality of the situation is that some very simple
code could be used in the front-end application to eliminate most of the
problems. Couple this with a good monitoring procedure for user input
and your SQL server can be made relatively secure from SQL Injection attacks.
Replacing the Bad With the Good
Hackers will use any number of methods to attack your systems.
SQL Injection is a relatively new technique, and hackers are still exploring
the many different possibilities. In all the examples so far, we’ve seen
that the hacker makes use of the single quote to do most of their damage.
For this reason, the obvious fix is to make sure the ASP code intercepts
all single quotes and either filters them or adds additional text to ensure
the quotes don’t affect the SQL strings. VBscript provides a REPLACE function
that looks like this:
strUserName=REPLACE(strUserName,"'", "''")
Using the Replace function ensures that any time a single quote character
is encountered, it will be replaced with two single quotes. This instructs
SQL Server to treat the quote as a character and continue processing.
Using the Replace function to replace single quotes with two single quotes
is a good method to secure against simple SQL injection. Make sure, though,
that you actually do replace the quotes (as shown earlier) rather than
simply filtering them out. Some routines suggest replacing them with NULL
characters, which can result in a new vulnerability rather than securing
your application.
Obviously, this is a rather simple method to secure your applications,
but the KISS (Keep It Simple, Stupid) principle does, indeed, apply here.
The simpler the solution, the easier it is to understand and maintain.
Tracking Down SQL Injection Attempts
After securing your application, you should always follow up with
some form of monitoring to ensure that your efforts were worthwhile. For
SQL Server, the easiest and most effective method for tracking server
activity is the built-in Profiler application. It’s easy to set up SQL
Profiler and you can maintain and run it from a dedicated workstation.
Simple Auditing With SQL Profiler
The first step in setting up an audit trail for SQL Injection would
be to decide where and how to store the audit information. If possible,
store the information in a table in a SQL database. This makes for much
easier report writing when the time comes to report on what you’ve found.
Depending on the level of detail you choose and how busy your server is,
this data can get large quickly, so have a plan in place to manage the
information. A good rule of thumb to use is to figure your audit information
will take up about a quarter to half of the space of your daily database
changes. Once you’ve figured out where you’ll store the audit information,
you can set up Profiler and begin tracing SQL activity. There are six
basic steps to set up Profiler:
- Start the Profiler application from a workstation that can be secured
(preferably in a locked room). Profiler is located in Start|Programs|Microsoft
SQL Server|Profiler by default.
- Start a new trace by clicking the New Trace icon in the upper left
corner of the screen.
- Choose the server to connect to and the logon parameters, as shown
in Figure 1.
Set up the general trace properties by selecting the name of the trace,
the storage mechanism and the standard template. (Obviously there are
many options here; we’ll just touch on the basics.) See Figure 2.
 |
| Figure 1. Use the Connect dialog to connect to
the appropriate SQL Server. |
 |
| Figure 2. Use the Trace Properties dialog to
select trace options. |
- Choose the Events tab and remove all items from the “Selected Event
Class” frame to start with a clean slate. Then choose the “Security”
Audit Class and click Add. Open the TSQL node under Audit Class and
choose the items shown in Figure 3.
 |
| Figure 3. The next step is to add all security
objects to the Selected Events. |
- Select RUN to start the Trace. (Again, there are a lot of options
here, but we’re focusing on the basics.) Once Profiler starts, you’ll
see a screen similar to Figure 4.
 |
| Figure 4. The Profiler screen shows data collected
by the trace. (Click image to view larger version.) |
- There will be varying output on the screen at this point, as the
actual activity on your server is what’s being traced. Figure 4 shows
the application as SQL Query Analyzer and the NT User name of “tmalone”
logged into the server and performing several operations. The upper
section of the screen shows the activity occurring. If you highlight
any row in the upper section, the lower section will display the detail
of that particular command.
Although it can be used for real-time auditing, Profiler’s power lies
in its ability to store this information in SQL, so that any standard
reporting tool can write queries against the data. Obviously, you’ll want
to monitor all user logins to ensure that only authorized users are logging
in, and you’ll want to monitor all statements that aren’t simple SELECT,
INSERT or UPDATE statements.
In some cases, hackers try to circumvent the auditing by using a special
command called sp_password. This command hides any subsequent text entered
within that batch, as Figure 5 shows.
 |
| Figure 5. Caption. (Click image to view larger
version.) |
You’ll definitely want to query your audit tables for any occurrence
of this command to learn who’s executing the command and why.
The more data you store, the more you have to sift through in order to
produce a meaningful report. A tool such as Crystal Decisions’ Crystal
Reports can make this task much easier, but even loading the data to Excel
can help you learn what’s happening on your server.
| Additional
Information |
|
Check out the following security-related resources,
along with some specific SQL Server security issues.
|
|
|
Tip of the Iceberg
This article has discussed some basic concepts related to the SQL Injection
technique of database hacking. There are many, many variations of this
technique, and we’ve only touched the tip of the iceberg. However, having
a basic understanding of what the hacker is trying to accomplish will
go a long way in helping secure your servers and applications.