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."