As Published In January/February 2005

On Injecting and Comparing
By Tom Kyte

Our technologist speaks on safe injections, comparing tables, and materialized views.

I was in the midst of reading your book Effective Oracle by Design, where you talk a little about SQL injection. That got me curious and led me to search on the Web.

From what I've read, I guess there isn't much of a defense available against SQL injection. How would you recommend that I go about detecting people who have engaged in SQL injection in the past and are still doing so? How would I detect code/applications that are susceptible to SQL injection, and how would I fix them? And last, how do I detect database vulnerabilities (such as authorization) that will allow a malicious user to engage in SQL injection?

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:

1. Detect people who have engaged in SQL injection in the past?
2. Detect people still doing so?
3. Detect code/applications susceptible to SQL injection (and determine how to fix them)?
4. 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."

Normally, I do pretty much a cut-and-paste from the Ask Tom site into this column and edit it. To make the preceding question and answer more readable, however, I actually wrote this more or less from scratch, using the material from the thread on Ask Tom. I encourage you to read the original discussion at asktom.oracle.com/~tkyte/sqlinj.html for further proof that SQL injection is real, it exists, and using bind variables is the solution that solves it pretty much entirely.

Comparing the Contents of Two Tables

I have two tables named A and B. They have identical columns and have the same number of rows via select count(*) from A and from B. However, the content in one of the rows is different, as shown in the following query:

SQL> select * from A where C1=1;
     C1              C2          C3 
 ------    ------------    --------
      1    AAAAAAAAAAAA         100

SQL> select * from B where C1=1;

     C1              C2          C3 
 ------    ------------    --------
      1    AAAAAAAAAAAB         100

The only difference is the last character in column C2. It is an A in table A and a B in table B. I would like to write SQL to compare or see if tables A and B are in sync with respect to their content rather than the number of rows, but I don't know how to do it.

OK, we'll do the specific solution to this problem with columns C1, C2, and C3, and then we'll see how to generalize this to any number of columns. The first and immediate answer I came to was this:

(select 'A', a.* from a
 MINUS
 select 'A', b.* from b) 
UNION ALL
(select 'B', b.* from b
 MINUS
 select 'B', a.* from a)

That is, just take A minus B (which gives us everything in A that's not in B) and add to that (UNION ALL) the result of B minus A. In fact, that is correct, but it has a couple of drawbacks:

• The query requires four full table scans.
• If a row is duplicated in A, then MINUS will "de-dup" it silently (and do the same with B).

So, this solution would be slow and also hide information from us. There is a better way, however, that uses just two full scans and GROUP BY. Consider these values in A and B:

SQL> select * from a;
 
         C1   C2   C3
 ----------   --   --
          1    x    y
          2   xx    y
          3    x    y
 
SQL> select * from b;
         C1    C2    C3
 ----------    --    --
          1     x     y
          2     x     y
          3     x    yy

The first rows are the same, but the second and third rows differ. This is how we can find them:

SQL> select c1, c2, c3, 
  2         count(src1) CNT1, 
  3          count(src2) CNT2
  4     from 
  5   ( select a.*, 
  6          1 src1, 
  7          to_number(null) src2 
  8       from a
  9      union all
  10    select b.*, 
  11         to_number(null) src1, 
  12         2 src2 
  13      from b
  14   )
  15  group by c1,c2,c3
  16 having count(src1) <> count(src2)
  17 /
 
  C1   C2    C3   CNT1    CNT2
 ---   --    --   ----    ----
   2    x     y      0       1
   2   xx     y      1       0
   3    x     y      1       0
   3    x    yy      0       1
 

Now, because COUNT(<expression>) returns a count of the non-null values of <expression>—we expect that after grouping by all of the columns in the table—we would have two equal counts (because COUNT(src1) counts the number of records in table A that have those values and COUNT(src2) does the same for table BCNT1 and CNT2, that would have told us that table A has this row twice but table B has it three times (which is something the MINUS and UNION ALL operators above would not be able to do).

To give credit where credit is due, you'll want to read the original Ask Tom discussion that got us to this answer: asktom.oracle.com/~tkyte/compare.html. What I found interesting in that thread was the back and forth we had to go through in order to come to the final query. Ultimately, a combination of Marco Stefanetti's technique with a minor addition I made led to this query, but you'll see the genesis of a pretty good idea there.

Missing the View

Is there a way to create a column in a materialized view that does not contain data yet has the desired datatype? I have a date column but no existing date column in the master table. If the materialized view does not create the column as a date datatype, then any comparisons on this field will fail. I have tried to use 'sysdate * NULL' in its place, but this does not work.

The CAST function is pretty good at this; it works in CREATE TABLE AS SELECT as well as in CREATE MATERIALIZED VIEW:

SQL> create materialized view mv
  2  as
  3  select empno, ename, 
  4     cast ( null as date ) dt
  5    from emp
  6  /
Materialized view created.

SQL> desc mv

 Name    Null?     Type
 -----   --------  ------------
 EMPNO   NOT NULL  NUMBER(4)
 ENAME             VARCHAR2(10)
 DT                DATE

Selective System Grants

Next Steps ASK Tom Oracle Vice President Tom Kyte answers your most difficult technology questions. Highlights from that forum appear in this column. READ more on SQL injection Effective Oracle by Design more on comparing tables

I want to give users the ability to execute "alter system set user_dump_dest" only. I want to give them the ability to turn on extended tracing but write out the trace files to a different directory, such as /tmp, rather than to the default directory. Is there a way to do this easily?

Stored procedures are great for this!

A stored procedure, by default, runs with the base privileges of the definer of the routine. As a user with the ability to use ALTER SYSTEM, all you need to do is

create or replace procedure set_udump (p_udump in varchar2)
as
begin
 if ( p_udump NOT LIKE '%=%' )
 then
      execute immediate 'alter system set 
      user_dump_dest = '''||p_udump||''' scope=memory';
 else
    raise_application_error(-20000,'Sorry, but for safety 
    reasons this procedure does not allow "=" in the parameter value');
 end if;
end;
/

Note: Revised content—to prevent SQL injection—for this procedure submitted by Roy Jorgensen.

The owner of the procedure needs to have ALTER SYSTEM granted directly, not via a role. See asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html for details on that nuance. So we run

SQL> grant alter system to ops$tkyte;
Grant succeeded. 

SQL> grant execute on set_udump to scott;
Grant succeeded.

Now, connected as SCOTT, run

SQL> exec set_udump( '/tmp' );
PL/SQL procedure successfully 
completed.
 

This approach works for any privilege you want to grant selectively like this. Stored procedures—with their ability to run as definer—provide an excellent security mechanism!