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:

1. 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.
2. Start a new trace by clicking the New Trace icon in the upper left corner of the screen.
3. 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.

4. 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.

5. 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.)

6. 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. www.microsoft.com/sql www.appsecinc.com/resources/alerts/mssql/ Microsoft Security Bulletins: MS02-007, “Serious DoS possibilities,” Q317979; MS02-008, “Access to Local Files,” Q318202/203; MS02-020, “Unchecked Buffers,” Q319507; MS02-039, “Buffer Overruns in SQL Server 2000 Resolution Service.” Q316333, “Cumulative Patch for SQL 2000 SP2.”

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.