SQL Injection
By Tom Kyte
Excerpt from Tom Kyte article


This is such an important topic, and not as many people are aware of it as I thought. Before we start with an answer, let's define the term SQL injection. SQL injection occurs when an application program accepts arbitrary SQL from an untrusted source (think "end user"), blindly adds it to the application's SQL, and executes it. It would be similar to the operating system's taking a couple of lines of C code from a user, compiling it on the fly, and just executing it in the operating system kernel. You'd never stand for that—it would subvert security (who knows what code goes there), and it would stand a good chance of crashing something. And yet, many application developers do it every day in their applications. They accept arbitrary input from end users and just concatenate it right into their SQL statements, parse (compile) the SQL, and execute it.

I'm going to use an excerpt from my book Effective Oracle by Design to drive this point home. The excerpt is from a chapter that talks about bind variables (imagine that—I actually wrote about bind variables!) and their importance for scalability and performance. To give extra ammunition to the developers and DBAs who want to use bind variables, I provided this clear example of SQL injection being used to bypass authentication checks:

Without Bind Variables, Your Code is Less Secure

Another concern when you don't use bind variables revolves around security, specifically the risk of SQL injection. To understand how it works, suppose that we have an application that asks a user for a username and password. We execute this query:


select count(*)
from user_table
where username = THAT_USER
and password = THAT_PASSWORD;


This seems innocent enough, right? Well, let's use SQL*Plus to test that theory and see what happens with and without bind variables.


SQL> create table user_table
2 ( username varchar2(30),
3 password varchar2(30) );
Table created.

SQL> insert into user_table
2 values ( 'tom',
3 'top_secret_password' );
1 row created.

SQL> commit;
Commit complete.

SQL> accept Uname -
> prompt "Enter username:"
Enter username: tom

SQL > accept Pword -
> prompt "Enter pass: "
Enter pass: i_dont_know' or
'x' = 'x


Note the password we just used. It incorporates a little SQL, doesn't it? Since we are just gluing strings together, not binding, the end user can actually type in arbitrary SQL and have it executed! Our application takes this string now and continues on:


SQL> select count(*)
2 from user_table
3 where username = '&Uname'
4 and password = '&Pword'
5 /
old 3: where username = '&Uname'
new 3: where username = 'tom'
old 4: and password = '&Pword'
new 4: and password =
'i_dont_know' or 'x' = 'x'

COUNT(*)
----------
1


Look at that. Apparently, the password 'i_dont_know' or 'x' = 'x' is our password. But if we use bind variables instead and accept the exact input from the end user, we see this:


SQL> variable pword varchar2(30);
SQL> exec :uname := 'tom';
SQL> exec :pword := -
> 'i_dont_know'' or ''x'' = ''x';

PL/SQL procedure successfully
completed.

SQL> select count(*)
2 from user_table
3 where username = :uname
4 and password = :pword
5 /
COUNT(*)
----------
0


We get the correct answer.

Think about this the next time you put an application out on the internet. How many hidden vulnerabilities might you have lurking in there if you develop your application using string concatenation instead of bind variables? Think of the "neat" side effects someone could have on your system. Consider this password:


SQL> accept Pword -
> prompt "Enter password: "

Enter password: hr_pkg.fire_emp( 1234)


Whoops, this person may have just executed a stored function as the user who is connected to the database. While he might not get logged on, he nonetheless got connected to your system and fired someone. Is this unbelievable? Absolutely not. Search www.google.com for SQL injection, and you'll see results 1 through 10 of about 15,800. Just consider the implications.

If you don't believe the performance-related arguments for using bind variables in your system, maybe this last bit will be enough to persuade you. Bind variables add security.

It is interesting to note that when I wrote the above excerpt in 2003, Google had about 16,000 pages indexed. Today, in 2004, there are close to 250,000! SQL injection is a growing problem.

This problem does not exist solely in Visual Basic Active Server Pages (ASP) applications or JavaServer Pages (JSP)—it is just as easy to fall into this trap with mod_plsql and PL/SQL or, in fact, any language. Think about the "innocent" stored function in Listing 1.

Code Listing 1: The "innocent" stored function


SQL> create or replace
2 procedure remove_emp( p_schema in varchar2,
3 p_ename in varchar2 )
4 is
5 l_str long;
6 begin
7 l_str := '
8 begin
9 delete from ' || p_schema ||
10 '.emp where ename = ''' || p_ename || ''';
11 delete from ' || p_schema ||
12 '.bonus where ename = ''' || p_ename || ''';
13 end;';
14 execute immediate l_str;
15 end;
16 /

Procedure created.

SQL> create table t ( x int );

Table created.

SQL> desc t

Name Null? Type
--------- ----- ------------------
X NUMBER(38)

SQL> begin
2 remove_emp
3 ( 'scott',
4 'KING''; execute immediate ''drop table t''; --' );
5 end;
6 /
begin
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 4
ORA-06512: at "OPS$TKYTE.REMOVE_EMP", line 11
ORA-06512: at line 2


SQL> desc t
ERROR:
ORA-04043: object t does not exist


Note how even though the block of generic code—written to be schema-independent—in Listing 1 failed, it succeeded. It processed the first delete—DELETE FROM SCOTT.EMP WHERE ENAME = 'KING'—but it then successfully executed the drop table T command (which had the not-so-nice side effect of committing your delete prematurely as well). The next DELETE succeeded, but the second DROP TABLE failed. So, you've endangered transactional consistency here (because an end user providing you with an employee name has taken over transactional control), but you've also given the end user the ability to execute any SQL—or, in this case, PL/SQL—that person wants to, using the privileges of the owner of the procedure (who presumably has lots of access, because it is a "generic" routine able to delete from lots of stuff).

So, how do we do the following:

Detect people who have engaged in SQL injection in the past?
Detect people still doing so?
Detect code/applications susceptible to SQL injection (and determine how to fix them)?
Detect database vulnerabilities that will allow a malicious user to engage in SQL injection?

Well, No. 1 is a hard nut to crack; unless you had auditing enabled, know a pattern to look for, and have a particular event you are trying to explain, it will probably not be possible. For example, if a table mysteriously went "missing" and I had auditing enabled (or perhaps I was using logminer if the event was recent enough), I would know the account that was used and I would start looking for SQL injection possibilities.

For No. 2, you might well be able to use V$SQL to find SQL injections. What you are looking for is any set of queries in V$SQL that differ only in the literal SQL they contain. That is a sure indication that the application is just taking string inputs from somewhere and concatenating them into SQL. This is horrible for many reasons, but it would be the first place to look for potential SQL injection attacks.

Unfortunately, if you are using CURSOR_SHARING = FORCE/SIMILAR, this approach will not work so well, because all queries would have their literals removed. You would not simply be able to locate the duplicate queries in the shared pool; you would have to look at each statement and see what you see. But assuming you are using CURSOR_SHARING = EXACT, you can use the technique outlined on asktom.oracle.com/~tkyte/literals.html. It was originally designed to find "bad SQL" that wasn't being bound properly, but because that is what we need to find, it'll work just as well for us here. These unbound SQL statements are the ones that should be considered suspect: They are bad for your system's performance, but they may hold the keys to the kingdom as well.

What you need to do now is find out where the literals in these SQL statements come from. If you discover, "Oh, that comes from that text input field on that screen over there," you are definitely exposed. Anytime the application accepts SQL from an untrusted source (that would be almost anything outside of the application itself), just executing that SQL has a level of danger. Executing SQL from the middle tier (logged in as some superuser) or when passed to a definer rights routine (because a stored procedure executes by default with the privileges of the definer of the routine) is especially dangerous.

No. 3 can be accomplished only through coding standards, code reviews, and adherence to the standards. Any code that accepts a string from an untrusted source and executes it (does not bind the string value) is open to SQL injection. That statement says it all. If you have a middle-tier application that uses a common account (with lots of privileges), if you have a client/server application that uses a common account (with lots of privileges), or if you use dynamic SQL in PL/SQL definer rights routines, you are very much open to this bug—unless your developers used bind variables. You might even consider asking your third-party vendors about their applications. If their application accepts input from you and just puts it into SQL and executes it, you have to trust the application vendors and assume that everything will be OK, because you don't have access to the application code.

For No. 4, I recommend a fairly new book authored by David Knox, Effective Oracle Database 10g Security by Design (Oracle Press, 2004). It does not have a checklist of "run this script, and it'll tell you if you are OK" (there are such security checklists out there) but rather espouses the concept of "you need to design security into your system from Day 1," in much the same fashion as I espouse the concept of "you must design performance, scalability, and maintainability into your system from Day 1."

Bad, Boring and a flop show…

Yes. I’m talking about 2007 cricket world cup. Too many negatives than positives. First of all is the crowd turnover in the grounds. To my knowledge the maximum crowd turnover was on the opening match between West Indies and Pakistan with the total crowd of 17000+. The average for this world cup is 5000 odd. Comparing this with the UEFA champion’s league, these numbers are way less.

The tournament format was equally awful. Too many games for the liking, even for cricket fanatics. Some might argue that including minnows is good for the game, but the talent gap between the established ones and the minnows were so huge that the most of the games were just a formality. Records after records were created against them (from highest runs in world cup to consecutive sixes, Century after century partnership to win with max difference).The likes of Bangladesh and Ireland shocked the world with wins against and India and Pakistan respectively but they were far less consistent to make Super 8 a spectacular one.

The death of Bob Woolmer is still a mystery. No inroads into his investigation after nearly a month. Mafia, betting are all rumored for his death. If that’s going to be the reason then definitely it has come at a wrong time. Without Indian subcontinent, there is not much revenue for cricket. With this comes the mafia and betting which are definitely bad for cricket. We are almost caught in a Catch 22 situation.

The sheer dominance of Australia in the world of cricket: Most of the critics almost wrote them off after their poor show against England and New Zealand. But they proved everyone wrong. Their professionalism towards cricket is to be learnt by other cricketing nations. No one really challenged them. Even the first ranked team before the world cup, South Africa just managed 140 odd runs. Not a single match of Australia was close enough. Their dominance makes it a boring and one sided encounter. Haha… What about the Srilankan ploy of resting Murali and Vaas for the Super 8 match against Australia? They were the ones who went for the most runs against them in the finals.

Filling up the Crossword

One shortcut technique is to the use reverse dictionaries.

Take a look at this Onelook reverse Dictionary

Ex: c*:board game will list all the board games starting with "C"

Hindu Crossword

Recently started attempting The Hindu crossword.
The max I have filled till now is the one on 17th Apr 2007

SQL query to print numbers from dual without using union

Select rownum from (select 1 from dual group by cube(1,1,1,1)) where rownum<10;

Excerpt from Asktom

Dinner @ Ravikanth's House

Yesterday night Ravikanth invited us to his home for dinner and it’s definitely a day to remember at least for the food we had. First we were served Puliyodharai with Chutney along with Cauliflower and Lady's finger curries. Then we had Rice and Dahl. Rice/Dahl along with ghee is one of my childhood favorites and is still one.
Then the special one. Vadai Kari. Almost I had 8-10 Vada’s and then too Ravi and his wife were forcing me for more. It took me some time to convince them that I’m through and it was time for some curd rice. At last we finished with a bowl of Payasam.
I had not planned to go to my native for the Tamil New Year celebration, and was a bit sad about missing the special dishes my grandma and mom used to make, but not any more.
Special thanks to Ravi and his wife for arranging such a treat for some vetti guy like me.

From Small to XL...
I remember buying a small sized T-shirt in Benetton two years back. Today I'm wearing a XL sized one from Basics. I really wonder how I have grown like this. I was just 58 when I came to Bangalore with a waist line of 28. Now my weight is hovering around 70 and waist line at 34. Wow what a growth in 2 odd years. I owe it all to the beer I’ve had for the past two years. Still growing... Hurray...

10-Apr-2007

Its my birthday today. Have turned 23. One more year towards the inevitable. Wanna quit smoke from today. Dont know whether I would keep my words. Let me try.

Daddy
Sylvia Plath

You do not do, you do not do
Any more, black shoe
In which I have lived like a foot
For thirty years, poor and white,
Barely daring to breathe or Achoo.

Daddy, I have had to kill you.
You died before I had time ----
Marble-heavy, a bag full of God,
Ghastly statue with one gray toe
Big as a Frisco seal

And a head in the freakish Atlantic
Where it pours bean green over blue
In the waters off the beautiful Nauset.
I used to pray to recover you.
Ach, du.

In the German tongue, in the Polish town
Scraped flat by the roller
Of wars, wars, wars.
But the name of the town is common.
My Polack friend

Says there are a dozen or two.
So I never could tell where you
Put your foot, your root,
I never could talk to you.
The tongue stuck in my jaw.

It stuck in a barb wire snare.
Ich, ich, ich, ich,
I could hardly speak.
I thought every German was you.
And the language obscene

An engine, an engine,
Chuffing me off like a Jew.
A Jew to Dachau, Auschwitz, Belsen.
I began to talk like a Jew.
I think I may well be a Jew.

The snows of the Tyrol, the clear beer of Vienna
Are not very pure or true.
With my gypsy ancestress and my weird luck
And my Taroc pack and my Taroc pack
I may be a bit of a Jew.

I have always been scared of you,
With your Luftwaffe, your gobbledygoo.
And your neat mustache
And your Aryan eye, bright blue.
Panzer-man, panzer-man, O You ----

Not God but a swastika
So black no sky could squeak through.
Every woman adores a Fascist,
The boot in the face, the brute
Brute heart of a brute like you.

You stand at the blackboard, daddy,
In the picture I have of you,
A cleft in your chin instead of your foot
But no less a devil for that, no not
Any less the black man who

Bit my pretty red heart in two.
I was ten when they buried you.
At twenty I tried to die
And get back, back, back to you.
I thought even the bones would do.

But they pulled me out of the sack,
And they stuck me together with glue.
And then I knew what to do.
I made a model of you,
A man in black with a Meinkampf look

And a love of the rack and the screw.
And I said I do, I do.
So daddy, I'm finally through.
The black telephone's off at the root,
The voices just can't worm through.

If I've killed one man, I've killed two ----
The vampire who said he was you
And drank my blood for a year,
Seven years, if you want to know.
Daddy, you can lie back now.

There's a stake in your fat black heart
And the villagersnever liked you.
They are dancing and stamping on you.
They always knew it was you.
Daddy, daddy, you bastard, I'm through.

Intro…

I did my U.G in Metallurgy from NIT Trichy. Just like one of the sheep in the herd, ended up in a IT company… doing nothing special other than supporting customers, bug fixing and stuff that’s irrelevant to what I should have… Love reading books… mostly fiction. Not so keen in spiritual ones… Hardcore gamer… football fanatic… Off late lost interest in cricket…