Skip to content

Log Buffer #257, A Carnival of the Vanities for DBAs

With new year many new projects, new technologies, new frameworks and new ideas are springing up at the speed of light and bloggers in the database arena are keeping up with this pace and this Log Buffer Edition is also living up to that pace and covers some of those posts in Log Buffer #257. [...]
Posted 03 February 2012 † Fahd Mirza § Oracle § Uncategorized ‡ Comments Off °

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'
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 then 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.



Posted 02 February 2012 † Thomas Kyte § Uncategorized ‡ Comments Off °

Big Data…

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
Posted 02 February 2012 † Thomas Kyte § Uncategorized ‡ Comments Off °

Viewing Figures

It’s time for another landmark. The blog has just hit 2,500,000 views (taking three weeks less than the previous half million increment) – here’s a little breakdown on the numbers.

First the top five across the lifetime of the blog:

AWR / Statspack menu 40,714 Updated from time to time
NOT IN 33,983 Feb 2007
Cartesian Merge Join 29,633 Dec 2006
dbms_xplan in 10g 28,831 Dec 2006
SQL Profiles (10g) 21,227 Feb 2007

If we look at just the last year, the top five are:

AWR / Statspack menu 18,782 Updated from time to time
NOT IN 8,916 Feb 2007
dbms_xplan in 10g 6,212 Dec 2006
Oracle Core 6,181 Dec 2011
Analysing Statspack (1) 5,924 Nov 2006

The Cartesian Merge Join has finally been pushed off the top 5 in recent history, but perhaps that’s only a temporary effect while the Addenda to Oracle Core is still fresh in people’s minds. It’s interesting to see how many old articles are still popular reading, though.

The other statistic of note is the number of followers the blog has, currently: 828

While we’re looking at numbers and success stories for the 12 months – the UKOUG annual conference reviews have just come in, and one of mine managed to score 5.83 out of 6 for “Presentation Skills” and 5.65 for content – with one commentator saying:

“Jonathan Lewis is a legend in Oracle circles and now, after my first session with him as speaker, I know why! This was a really well-thought out talk with loads of great ideas and info. My only criticism is that I’d like to attend it again to pick up the bits I missed the first time!”

Don’t worry – it was the one about “Beating the Oracle Optimizer”, 60 minutes featuring seven different strategies for optimising a two-table join, and I’ll be presenting it at least a couple more times in the UK (probably 27th March and 13th June in Manchester and Linlithgow).

I also won two the of UKOUG’s “Inspiring Presenter” awards this year. Not sure I’ll be able to do that again next year since Connor McDonald was in town this year – if you ever get a chance to hear him, make sure you don’t miss it.

 

 


Posted 02 February 2012 † Jonathan Lewis § Uncategorized ‡ Comments Off °

E-Business Suite and APEX integration (overview)

For me there are two main use cases to integrate Oracle E-Business Suite (EBS) with Oracle Application Express (APEX):

  1. In my APEX application I want to include/integrate data that exist in EBS
    e.g. I want to include my employees that are in EBS HR

  2. The EBS application needs to be customised and I use APEX to do that
    e.g. I want to show a calendar with the birthdays of my employees
    or I want a chart about the different age categories in my company
    or I want to bulk update some information and there is no screen to do that

In both cases you want things to go seamlessly, you shouldn't be aware that you go from one technology to another. APEX and EBS data are in the Oracle database, so accessing data is not a big issue. Updating data in EBS you need to be careful with, as directly updating data through tables is not allowed (supported).

When I was reviewing ways to retrieve data from EBS and create/update/delete data in EBS from APEX I found a couple of methods that are interesting to explore further:

Retrieve data from EBSUpdate data in EBS
1. ViewsX
2. APIsXX
3. OITs (Open Interface Tables)
X
4. Webservices (incl. BPEL/SOA)XX

There are other ways to integrate or interface with EBS e.g. through EDI (Electronic Data Interchange) and EAI (Enterprise Application Integration), but from an APEX perspective the above four seemed to me the most interesting ones to explore further. In my next posts I will dig into integrating APEX and EBS with the above techniques.

References to interesting readings:

Previous related posts:
Posted 02 February 2012 † Dimitri Gielis § Uncategorized ‡ Comments Off °

Google Connect is dead, long live the Google+ badge…

The Google Friend Connect badge I’ve been displaying on my website homepage and this blog is no more. Google have canned that widget.  Instead it’s been replaced by a Google+ badge. Unfortunately, the G+ badges currently only support Google+ pages, so I can’t connect it to my regular G+ profile, only the ORACLE-BASE.com G+ page. Not ideal, but that’s the way it goes. :)

Currently, the G+ badge options are not particularly WordPress widget friendly. Even the small option is a bit wider than the width of the sidebar on my blog theme.

I’m not planning on double-posting, so feel free to connect to both profiles if you like. You won’t get everything twice. For the time being, my regular G+ profile is what I’ll be using and the ORACLE-BASE.com G+ page is really only present to support the badge for those people who like to show the site some love. :)

Cheers

Tim…


Posted 02 February 2012 † Tim... § Uncategorized ‡ Comments Off °

blog maintenance

Time to upgrade Drupal again. Yesterday version 7.12 was released and this blog is currently running a very outdated (and probably insecure) 7.4. Although Drupal 7 included automatic update for modules and themes, updating the core Drupal software still needs manual intervention and takes time.

Over the years, the main self-hosted blog platforms I have used are:

  • WordPress - one-click updates. Quick and easy. By far the best and most robust solution. Never let me down.
  • Habari - Official Habari releases were fairly infrequent so I chose to I track the latest development version so upgrade was manual but as simple as typing '$ svn update'. Rolling back was needed on a couple of occasions but possible simply by reverting to the previous SVN version ($ svn update -r ).
  • Drupal - manual update. Involves taking the site offline, copying files, thinking, run 'update.php', copying files back again, bringing the site back online and a little time. Slightly tedious as Drupal tend to to release a new version of the core software every month or so with a nagging email reminder to do the right thing.

I have also noticed that my sitemap hasn't been generated in 6 months and doesn't include the most recent entries. In addition, some (old) posts have been marked as 'Never Update' but after some housekeeping to modify some permalinks to fix various '404 - Not found' errors, these old entries now need to be regenerated.

Tags:

Posted 02 February 2012 † andyc § Uncategorized ‡ Comments Off °

BYOD Is Absolutely Top-Down

I’m glad someone finally pointed this out about the BYOD movement: Who Has Apples at Work? In Many Cases, It’s the Bosses (h/t Between the Lines)

From my long-past days in IT, I remember the added pressure of supporting executives, regardless of what they were carrying.

When it came to support, we jumped, e.g. I once was called to an offsite meeting to work on the COO’s non-standard Thinkpad; we used to support different printer configurations for execs and their admins; certain small laptops (now called ultrabooks) were only for high-level execs, etc.

From what I’ve seen, IT makes no bones about giving better support to execs. After all, these people can get you fired if you refuse to support this or that non-standard device, and similarly, they remember you later as a go-to person, which never hurts.

So yeah, BYOD is driven top-down; IT will sit up and take notice if the CEO is using an iPad exclusively, not only because it might break, but also because, it means every other exec will follow suit very quickly, if only for the cool factor. It might take 1,000 line managers to get the same level of attention.

What have you seen in your BYOD experience?

Find the comments.Possibly Related Posts:

Posted 02 February 2012 † Jake § Uncategorized ‡ Comments Off °

Is Siri a Disappointment?

Well, this is interesting: Is it time to say goodbye to Siri?

A few months ago, when I finally upgraded my wife’s OG iPhone, I initially bought the 4, but settled on the 4S because she thought Siri sounded useful and cool. I told her my opinion, basically that voice was a neat feature that no one ever used consistently beyond the first week or so.

I recall telling Anthony (@anthonyslai) the same thing about Android’s voice input feature years ago too.

For a while there, Siri looked like the exception, not the rule. I’ve been eagerly watching hackers do very cool things with Siri, all the while noticing that my wife never uses Siri. When I quizzed her, she said it was good enough, i.e. down a lot, wrong, slow.

Even though Siri is awesome technology and by far the best voice-based interface I’ve seen to date, it’s still not good enough to create new behaviors, at least for her. Now, I’m reading similar stories from other people, purely anecdotal, but still curious.

So, is this your experience with the 4S or what you’ve heard from people who have one?Possibly Related Posts:

Posted 02 February 2012 † Jake § Uncategorized ‡ Comments Off °

Trending, Unplugged Vacations

I suspect many people now take workcations, given how common tablets and smartphones are and how easy it has become to do work on the go, and similarly how hard it has become to disconnect from the internets.

One trend I wholeheartedly endorse is the rise of the unplugged getaway, which is tougher (and pricier) than you think.

Have some fun imagining yourself at one of these places: 5 of the World’s Best Unplugged Getaways.

Years ago, I went to a tiny little coral cay off the coast of Australia and experienced this type of disconnection. No TV, phone, internet or even air conditioning in the rooms, which happily made it absolutely imperative to get out and enjoy the beauty of the Great Barrier Reef.

It was liberating and refreshing, and I’d highly recommend it.

Thoughts on forced, but recreational disconnection?Possibly Related Posts:

Posted 01 February 2012 † Jake § Uncategorized ‡ Comments Off °