div.b-mobile {display:none;}

Thursday, February 02, 2012

All about Security - SQL Injection

I recently did a web seminar on Oracle Database Security (you can see a replay of it here).  We had over 1,300 live attendees (glad I couldn't see you all - that would be scary) and the feedback was pretty good.

We also received a few questions, well, actually - a lot of questions.  I'm going to try to tackle them here bit by bit.  I'm going to start with my favorite topic - questions centered around SQL Injection.  I'll center on the core concepts around SQL Injection in this article and then do a followup article regarding the Oracle Database Firewall - a tool useful for detecting and blocking SQL Injection attacks.

During the presentation - I talked about how insidious SQL Injection is - and how hard it can be to detect. In fact, I've written about this before, in this article.  The interesting thing about that article on injecting is the very last part of it, the section on "selective system grants".  If you read that small section you'll see a comment "Note: Revised content—to prevent SQL injection— for this procedure submitted by Roy Jorgensen.".  What that means is - the original article I submitted had a SQL Injection bug in it - right after I just spent pages going over SQL Injection!  That wasn't too embarrassing was it (it was).  But it does point out how easy it is for a SQL Injection bug to sneak into code - even when the coder knows full well what SQL Injection is and how it happens!

Anyway, during the web seminar I talked about a slide I use - with a full stored procedure on it - that contains a SQL Injection bug.  I ask the audience, usually full of developers and DBAs to tell me how the code can be SQL Injected..  I tell them right out - this code can be injected and if I were to put it in my schema and grant you execute on it - you could use this to read pretty much any table I own.

I usually hear crickets at this point in time, no hands, no volunteers.  Here is the slide:

Note that the input to this procedure is a binary Oracle date - it is fixed length, 7 bytes of data - the century, year, month, day, hour, minute and second.   The input is not a string, the input cannot contain things like "or 1=1" - typical SQL Injection attack strings.  It can only contain an Oracle date.  So - the question is - how can I 'trick' this stored procedure into showing me anything I want to see in the schema that owns the procedure (thus bypassing any and all security the application tier might have put in place - there are no restrictions on what I can and cannot see now).

Before we get there - let's talk about the bit of code that will be problematic - that is line 10.  As noted there is a double implicit conversion going on there.  That line of code is really:

Where created = to_date( to_char( p_date ) );

There is an implicit to_char on the date field in order to concatenate it to the query string.  Then, at runtime there is an implicit to_date on the string we concatenated in so we can compare it to a date.  This is a very common thing I see in code all of the time (implicit conversions) - but it is pure evil.  Not only will we discover it is the cause of a SQL Injection issue - but here it is a logic bomb as well.

First of all - by default - that to_date( to_char() ) conversion will have a side effect of effectively truncating the time component from the date field.  That is evil.  If you wanted to truncate the time off - please use TRUNC() on the date - it is much faster, more efficient, and expresses clearly that you intend to truncate the time component.  To_date(to_char()) does none of that.  Secondly - the conversion by default will also lose the century.  If you were trying to look for things created during the war of 1812 - you would lose, you cannot search for 1812 - it would become 2012 (well, right now as I write this it would be 2012 - in 38 years it will become 2112 and you won't be able to search for 2012 anymore...).

Also consider that I said "by default".  By default the NLS_DATE_FORMAT is DD-MON-RR (currrently, it has been different in the past!).  What happens to this code when someone decides to change it?  Your application might well start querying up entirely different data!

So, the implicit conversion by itself is bad - but the real issue is the SQL Injection flaw.  If you just run this procedure, by default - it certainly looks OK:

ops$tkyte%ORA11GR2> exec inj( sysdate )

        select *
          from all_users
         where created = '02-FEB-12'

PL/SQL procedure successfully completed.

that looks OK - seems pretty safe - until, until someone who has read the documentation comes along.  They might run your code like this:

ops$tkyte%ORA11GR2> alter session set
  2  nls_date_format = 'dd-mon-yyyy"'' or ''a'' = ''a"';

Session altered.
ops$tkyte%ORA11GR2> exec inj( sysdate )

        select *
          from all_users
         where created = '02-feb-2012' or 'a' = 'a'

PL/SQL procedure successfully completed.

Now that is surprising,  you might not even know you could do that in an NLS_DATE_FORMAT.  It is really hard to protect against something you don't even know you can do - isn't it?  I've had people look at that example and scoff at it - saying "so what, they were allowed to see that table".  Ok, take it a step further, I'd like to know what tables you own - so I can start querying them.  I'll just do this:

ops$tkyte%ORA11GR2> alter session set
  2  nls_date_format = '"''union select tname,0,null from tab--"';
Session altered.

ops$tkyte%ORA11GR2> exec inj( null )

Select *
  from all_users
 where created = ''union select tname,0,null from tab--'


Now you can see where this is going...  I find one SQL Injection bug in one procedure and I've unlocked the entire schema.  

So, the question now comes up - how do I protect myself from this?  What can I do to ensure I'm not subject to SQL Injection in this code?

There are two ways - the hard way and the easy way.  

The hard way involves writing code to validate everything and having serious code reviews of any code that uses string concatenation to build their SQL statements - any code that takes a parameter as input and concatenates it to a SQL query must be read and reviewed by many people - many people who will be super critical of the code.  In this case, the resulting code would have to be:

where created = to_date( ''' || to_char(p_date,'yyyymmddhh24miss') ||''', ''yyyymmddhh24miss'')';   

You need to have a coding standard that says:

  1. You shall never use implicit conversions ever, as in never.
  2. You shall always use an explicit date mask with dates, as in every single time, you will not rely on defaults (because defaults can inject you and because defaults can radically modify your logic unintentionally!)
And now you have to comb through all of your code looking for these bad practices (you should anyway - you have major logic bombs just waiting to explode in your code if you rely on default NLS settings and implicit conversions).

The easy way however is the way to go.  The easy way is - just use bind variables!  If you use bind variables, you cannot be SQL Injected - this is true for PL/SQL, for Java, for any and all languages.  If you use bind variables you cannot be SQL Injected - period.  It is that simple, really and truly.  If the code was:

  7          l_query := '
  8          select *
  9            from all_users
 10           where created = :x';
 11          open c for l_query USING P_DATE;
there is no way the end user can trick that SQL query into becoming anything other than what it is - in fact, for this example, the code should have been:

   cursor c is select * from all_users where created = p_date;
   open c;

and nothing more - it shouldn't have even been using dynamic SQL.  In Java/C#/C++/etc - you would be using dynamic SQL and you should be using bind variables.   So, that answered all of these questions I received:

where can I find an illustration of SQL injection?
can u share the sql injection demo code
Can you share that SQL injection slide?
Can you show a code example of the SQL injection bug that nobody noticed during your presentations?
Can you show us or point us to the site of the example of SQL injection bug?
Is SQL injection all about binding, or is there more?

Another question was:

should application layer deal with the SQL injection attacks prevention as that layer understands what the proper data access patterns look like rather than database?

My response to that is - the application layer should definitely be aware of SQL Injection and use secure coding practices which would include:
always use a bind variable unless you have an excellent technical reason not to - and then you must submit your code for review to at least five people who do not like you - they must be motivated to rip your code apart, critically review it, make fun of it - so they find the bugs.
However - we need to also employ defense in depth - for when the inevitable bug slips through.  When I next write about this - I'll be going over the Oracle Database Firewall - a tool that can provide at least one more layer of defense.

The last question on this topic was: 

What is the dbms_assert PL/SQL package? How does it help prevent SQL injection? Should my organization be using it?

For that - I'll just forward you onto an excellent paper on this subject written by Bryn Llewellyn.  You can find that paper here.



Anonymous Roy Jørgensen said....

Thank you! I learned something new today about date format mask ...

Thu Feb 02, 04:21:00 PM EST  

Anonymous Anonymous said....

Something new I learned today about who versus that.

Thu Feb 02, 05:02:00 PM EST  

Blogger Thomas Kyte said....


I knew about the who versus that :) you should have seen how many times I did it wrong before hitting "publish" the first time.

I think I've gotten rid of the last two "who versus that" issues....

Thu Feb 02, 05:06:00 PM EST  

Anonymous David Litchfield said....

Hey Tom,
Funnily enough I just published a paper about doing the same thing with NUMBER concatenations. This was an addendum to a paper I wrote in 2008 on exploit DATE concatenations - the same problem you discuss here. You can get the recent paper here: http://www.accuvant.com/capability/accuvant-labs/security-research/lateral-sql-injection-revisited-exploiting-numbers and the first paper here: http://www.databasesecurity.com/dbsec/lateral-sql-injection.pdf

Thu Feb 02, 05:12:00 PM EST  

Blogger Thomas Kyte said....


holey moley - here I've been sitting thinking that numbers were fairly safe.

I'll have to revise my future wording to include a bit about numbers too.

Nice write up.

Thu Feb 02, 05:16:00 PM EST  

Anonymous Anonymous said....

Great post, defenitly everyday we learn something new. Thanks a lot...

Thu Feb 02, 05:16:00 PM EST  

Anonymous Chris Saxon said....

That's interesting and scary - as I posted, databases fear needles too...

Are there other NLS settings which can also cause this kind of attack?

Thu Feb 02, 05:17:00 PM EST  

Blogger Thomas Kyte said....


See David Litchfields paper above - numbers are affected too. So - yes.

Thu Feb 02, 05:19:00 PM EST  

Anonymous Anonymous said....

Slightly off topic ... can I also suggest:

select *
from all_users
where created = inj.created

That p_ prefixing system drives me nuts.

Fri Feb 03, 03:36:00 AM EST  

Blogger Thomas Kyte said....


once you have a naming convention in place, it is really hard to change. I've used p_, l_, g_ for parameter, local, global variable for over 18 years now....

It is a matter of choice/style, not really a technical issue...

Fri Feb 03, 07:14:00 AM EST  

Anonymous Anonymous said....

the problem David mentions in


only arises since NUM_PROC is owned by SYS,
as far as I can see, correct ?

So, it's not really a problem since nobody ever does something as SYS, correct.

By the way David, if you happen to read this: what's the Scottish Schools Indoor record for long jump ? ( just for the sake of interest )

Fri Feb 03, 07:26:00 AM EST  

Blogger Thomas Kyte said....

@last anonymous

No, not at all - you don't need to bring sys into the picture at all.

see http://tkyte.blogspot.com/2012/02/all-about-security-sql-injection-redux.html

Fri Feb 03, 08:04:00 AM EST  

Anonymous David Litchfield said....

Hey @anonymous,
Regardless of who created the plsql object, if it is created with definer rights and others can execute it, then it can be abused to run SQL injection. Further, despite no-one using SYS, there are extant SYS owned, definer rights, publically executable packages in the RDMBS that *are* vulnerable to SQL injection allowing attackers to execute arbitrary SQL as SYS. Prior to 10gR2 SQL injection flaws in default packages were very common but then Oracle tightened up their coding standards and cleaned up their code. There still are SQL injection flaws to be found but they're much, much less common.

Wrt the the Scottish school's indoor long jump, I broke the record with a jump of 6m 80 (22.4ft) in 1993 but a PCL injury ended my T&F career.

If you've got any more questions about Oracle security feel free to ask - david at davidlitchfield dot com.

Fri Feb 03, 01:57:00 PM EST  

Blogger Jared said....

Thanks Tom! I've always considered implicit conversions evil, but I did not realize just how evil they are.

Time to hit the docs, again...

Fri Feb 03, 01:58:00 PM EST  

Anonymous Anonymous said....

I was just wondering how an external would have an alter session privilege. Thanks for replying in advance...

Thu Feb 09, 10:41:00 AM EST  

Blogger Thomas Kyte said....


you do not need alter session to set your nls_date_format.

create a user with just create session and they can issue many alter session commands.

Besides, you have to worry about inside attackers as well.

Suppose I'm a developer. I have create session, create procedure. You own this injectable procedure and have it in some admin account.

I can use those limited privileges to completely take over your account.

I'll just code an authid current_user function that uses execute immediate to issue any command - and I'll trick you into calling my function.



for complete details on how to do that.

Thu Feb 09, 10:47:00 AM EST  

Anonymous Anonymous said....

Coding standards aside, does Oracle have a good reason for making this session parameter free form? In other words can we expect to see some validation being applied to this parameter in the future to reduce the occurance of this problem?

In our case we use third party code and because of the license we cannot change it. This makes it pretty difficult to prevent these type of problems, when all we can do is suggest to the third party changes to their code.

Thu Feb 09, 10:56:00 AM EST  

Blogger Thomas Kyte said....


you can do a lot more than suggest to a 3rd party, you can tell them how many forums and blog posts you will be writing about the trivially easy to exploit security hole in their application - you have a lot of power in that fashion.

what possible kind of validation could anyone do on a free form element? the NLS_date_format is allowed - by design - intentionally - to have a string in it. It always has. IT IS NOT THE BUG. It is not the cause, it is not the problem.

the problem is in the developed code which is

a) relying on a default nls_date_format - huge no-no

b) using implicit conversions - bad thing big time

c) not using bind variables - we all know what I have to say about that.

The problem is not the format.

Thu Feb 09, 11:00:00 AM EST  

Anonymous Anonymous said....

I am not suggesting that there is a bug as that would suggest that it is not doing it's intended purpose. Rather I am wondering if there is some validating that can be applied similar to the to_date function. Am I missing some aspect of what this parameter is used for?

Thu Feb 09, 11:12:00 AM EST  

Blogger Thomas Kyte said....


I have to admit I don't know what you mean at all.

"similar to the to_date..." ??? how so - can you explain?

what sort of validation are you asking for here - give an example or something.

Thu Feb 09, 12:05:00 PM EST  

Anonymous Anonymous said....

Hey Tom,
I cut my teeth on Pascal and Ada - strong typing - love it. Ive always hated implicit conversions in pl/sql - why cant we get a compiler flag to disable or at least get warnings when the compiler sees them?

Thu Feb 09, 05:56:00 PM EST  

Blogger Thomas Kyte said....


there have been talks about it, having some warnings.

it is a conundrum though - in most cases - there would be so many warnings that it would be almost useless. But there have been discussions.

Thu Feb 09, 06:34:00 PM EST  

Anonymous Dan T. said....

or how about "then versus than"? I don't think you ever should say "rather then". (shudders)

Fri Feb 10, 11:34:00 AM EST  

Blogger Thomas Kyte said....

@Dan T

fixed that - I have a really bad habit of using then for than... I thought I got them all before publishing ;)

Sun Feb 12, 06:41:00 PM EST  

Blogger Scott Wesley said....

That's a very sneaky injection - great post Tom!

re: conversion warnings - what about PLW-07204?

Sun Feb 12, 10:40:00 PM EST  

Blogger Thomas Kyte said....


it gets it a little bit

ops$tkyte%ORA11GR2> create table t ( dt_col date );

Table created.

ops$tkyte%ORA11GR2> create or replace procedure test
2 authid definer
3 as
4 l_date date := '01-jan-2012';
5 l_str varchar2(20);
6 begin
7 l_str := l_date;
9 select dt_col into l_date
10 from t
11 where dt_col = l_str;
13 dbms_output.put_line( l_date );
14 end;
15 /

Procedure created.

ops$tkyte%ORA11GR2> alter procedure test compile
2 PLSQL_Warnings = 'enable:all'
3 reuse settings
4 /

SP2-0805: Procedure altered with compilation warnings

ops$tkyte%ORA11GR2> show errors procedure test

-------- -----------------------------------------------------------------
11/21 PLW-07204: conversion away from column type may result in
sub-optimal query plan

but it misses more than it got. line 4, 7, 13 all have implicit conversions too.


ops$tkyte%ORA11GR2> create or replace procedure test
2 authid definer
3 as
4 l_date date := '01-jan-2012';
5 l_cursor sys_refcursor;
6 begin
7 open l_cursor for '
8 select dt_col into l_date
9 from t
10 where dt_col = ''' || l_date || '''';
12 fetch l_cursor into l_date;
13 close l_cursor;
15 dbms_output.put_line( l_date );
16 end;
17 /

Procedure created.

ops$tkyte%ORA11GR2> alter procedure test compile
2 PLSQL_Warnings = 'enable:all'
3 reuse settings
4 /

Procedure altered.

ops$tkyte%ORA11GR2> show errors procedure test
No errors.

it wouldn't help in a sql injection case either...

Mon Feb 13, 07:28:00 AM EST  

Blogger EVIL said....

Hi Tom, We always learn, not new things but new ways to do things from you

Mon Feb 20, 06:06:00 AM EST  

Blogger Cyryl Balma-Tivola said....

As always, clear and accurate!

Wed Mar 07, 03:36:00 AM EST  

Blogger Saket said....

Thanks Tom for a complete article, i didn't knew before today that
l_date date := '01-jan-2012'; is implicit conversion,so what would be correct declaration in that case which wont require implicit conversion
is this the ans: DATE '01-jan-2012'

also is correct to assume that whenever we are using a concatenated string as in -->
select dt_col into l_date
from t
where dt_col = ''' || l_date || '''' to open a cursor/dynamic SQL;
if the dt_col is any other type than char/varchar then in that case there is going to an implicit conversion eg: if dt_col is number then to_number() will be used if date then to_date() and may be if its clob then to_clob()and so on...?

Mon Mar 19, 04:35:00 AM EDT  

Blogger Thomas Kyte said....


is this the ans: DATE '01-jan-2012'

that is one approach, but it is not "bind friendly" as date literals cannot be bound it. It is more likely that:

to_date( :x, 'dd-mon-yyyy' )

is what you are looking for - where :x is a plsql variable or a host variable in a client program.

and yes, anytime you concatenate anything that is not a string - it will have to be converted to a string since strings are the only things that can be concatenated in plsql.

Mon Mar 19, 09:22:00 AM EDT  

Anonymous Anonymous said....

Shouldn't this be the fault of the SQL manufacturers? I think so. The format string should never be allowed to escape out of itself. Sorry, this is not a developer problem, this is the fault of people who develop SQL. Idiots.

Thu Aug 09, 04:12:00 PM EDT  

Blogger Thomas Kyte said....

@last anonymous

what you've just said makes no sense whatsoever.

define what it even *means* to have "a format string escape out of itself"

what does that even *mean*

blame the tool - good job. It is the fault of whoever developed the language you program in - they allow a string to escape out of itself using concatentation. Idiots.

there, now we are even.

this has nothing to do with SQL.

this has to do with a programmer concatenating strings together to build a piece of executable code - and then blindly executing it. Period.

Thu Aug 09, 05:17:00 PM EDT  

Blogger Vishwas Soni said....

Just check it out the article on sql-injection-tutorial-website-hacking

Mon Oct 29, 06:41:00 AM EDT  


<< Home