Database Reference
In-Depth Information
■
this code prints out only five records at most. it was developed to be executed in an “empty” schema. a schema
with lots of existing tables could cause various effects that differ from the results shown next. one effect could be that
you don't see the table i'm trying to show you in the example—that would be because we print out only five records.
another might be a numeric or value error—that would be due to a long table name. none of these facts invalidate the
example; they could all be worked around by someone wanting to steal your data.
Note
Now, most developers I know would look at that code and say that it's safe from SQL injection. They would say
this because the input to the routine must be an Oracle
DATE
variable, a 7-byte binary format representing a century,
year, month, day, hour, minute, and second. There is no way that
DATE
variable could change the meaning of my
SQL statement. As it turns out, they are very wrong. This code can be “injected”—modified at runtime, easily—by
anyone who knows how (and, obviously, there are people who know how!). If you execute the procedure the way the
developer “expects” the procedure to be executed, this is what you might expect to see:
EODA@ORA12CR1> exec inj( sysdate )
select *
from all_users
where created = '12-MAR-14'
PL/SQL procedure successfully completed.
This result shows the SQL statement being safely constructed—as expected. So, how could someone use this
routine in a nefarious way? Well, suppose you've got another developer in this project—the evil developer. The
developers have access to execute that procedure, to see the users created in the database today, but they don't have
access to any of the other tables in the schema that owns this procedure. Now, they don't know what tables exist in
this schema—the security team has decided “security via obscurity” is good—so they don't allow anyone to publish
the table names anywhere. So, they don't know that the following table in particular exists:
EODA@ORA12CR1> create table user_pw
2 ( uname varchar2(30) primary key,
3 pw varchar2(30)
4 );
Table created.
EODA@ORA12CR1> insert into user_pw
2 ( uname, pw )
3 values ( 'TKYTE', 'TOP SECRET' );
1 row created.
EODA@ORA12CR1> commit;
Commit complete.
