Oakies Blog Aggregator

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:

#333333; font-family: 'Trebuchet MS', Verdana, Arial, sans-serif; line-height: 18px; text-align: left;">Hey Tom,#333333; font-family: 'Trebuchet MS', Verdana, Arial, sans-serif; line-height: 18px; text-align: left;">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:

#333333; font-family: 'Trebuchet MS', Verdana, Arial, sans-serif; line-height: 18px; text-align: left;">the problem David mentions in #333333; font-family: 'Trebuchet MS', Verdana, Arial, sans-serif; line-height: 18px; text-align: left;">http://www.accuvant.com/capability/accuvant-labs/security-research/lateral-sql-injection-revisited-exploiting-numbers #333333; font-family: 'Trebuchet MS', Verdana, Arial, sans-serif; line-height: 18px; text-align: left;">only arises since NUM_PROC is owned by SYS,#333333; font-family: 'Trebuchet MS', Verdana, Arial, sans-serif; line-height: 18px; text-align: left;">as far as I can see, correct ? 

#333333; font-family: 'Trebuchet MS', Verdana, Arial, sans-serif; line-height: 18px; text-align: left;">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

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

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:

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

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.

It’s always worth browsing through the list of Oracle’s bug fixes each time a new release or patch comes out because it can give you clues about where to look for problems in your production release – and how to anticipate problems on the upgrade. This article is an example of a fix that I found while while looking at the note for 11.2.0.3 quite recently.

Over the last thre or four years I’ve made several commentsabout how subquery factoring could result in changes in execution plans even if the “factored subquery” (or “common table expression” to use the more appropriate technical term) was moved in line. In the 11.2.0.3 bug fixes list, though, you’ll find the following bug fixes:

Bug 11740670 Different execution plans with and without subquery factoring (WITH clause)
Bug 9002661 Transitive predicate were not generated in WITH clause.

So here’s an example that’s been sitting on my laptop for a few years, waiting for the right bug fix (it’s using my standard setup of 8KB block size, LMT with 1MB uniform extents, but not ASSM, and CPU costing disabled):

create table emp (
	dept_no		not null,
	sal,
	emp_no,
	padding,
	constraint e_pk primary key(emp_no)
)
as
select
	mod(rownum,6),
	rownum,
	rownum,
	rpad('x',60)
from
	all_objects
where
	rownum <= 20000
;

-- collect stats, compute, no histograms

select
	outer.*
from
	emp outer
where
	sal > 1000000
and	outer.sal > (
		select
			avg(inner.sal)
 		from	emp inner
		where	inner.dept_no = outer.dept_no
	)
;

with subq as (
	select
		outer.*
	from
		emp outer
	where
		sal > 1000000
	and	outer.sal > (
			select
				avg(inner.sal)
 			from	emp inner
			where	inner.dept_no = outer.dept_no
		)
	)
select	*
from	subq
;

In 11.1.0.7 (and earlier) the first version of this query uses a simple filter subquery, and the second version inlines the factored subquery (which should, therefore, produce the same plan) but then unnests the subquery and uses “group by placement” to resolve the plan as a hash join followed by hash aggregation. This gives us two completely different plans:

Plan for first form of the query
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    72 |    70 |
|*  1 |  FILTER             |      |       |       |       |
|*  2 |   TABLE ACCESS FULL | EMP  |     1 |    72 |    35 |
|   3 |   SORT AGGREGATE    |      |     1 |     8 |       |
|*  4 |    TABLE ACCESS FULL| EMP  |  3333 | 26664 |    35 |
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL"> (SELECT AVG("INNER"."SAL") FROM "EMP"
              "INNER" WHERE "INNER"."DEPT_NO"=:B1))
   2 - filter("SAL">1000000)
   4 - filter("INNER"."DEPT_NO"=:B1)

Plan with factored subquery moved in line
-------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost  |
-------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    83 |    93 |
|*  1 |  FILTER              |      |       |       |       |
|   2 |   HASH GROUP BY      |      |     1 |    83 |    93 |
|*  3 |    HASH JOIN         |      |  1667 |   135K|    71 |
|*  4 |     TABLE ACCESS FULL| EMP  |     1 |    72 |    35 |
|   5 |     TABLE ACCESS FULL| EMP  | 20000 |   214K|    35 |
-------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL">AVG("INNER"."SAL"))
   3 - access("INNER"."DEPT_NO"="OUTER"."DEPT_NO")
   4 - filter("SAL">1000000)

Under 11.2.0.3, however, the first plan appears for both forms of the query.

On the plus side – this is good, because Oracle is now behaving consistently. On the minus side – this is bad, because someone, somewhere, is going to be the unlucky person who managed to get a good execution plan by accident because of the bugs.

Maxim: when upgrading to 11.2.0.3, keep a close eye on any queries you’ve got that include subquery factoring – in fact, you might decide to capture SQL Baselines (without activating them) for every single one of them before doing the upgrade so that you can enforce the pre-upgrade plan if the post-upgrade plan is less efficient.

Footnote: I haven’t finished with this example – there’s an interesting follow-up that appeared in the remainder of my test code when I hinted the subquery into materialization. But that’s a story for another day.