Varchar table created insert into user uname

Chapter 1 ■ Developing SuCCeSSful oraCle appliCationS

Another impact of not using bind variables, for developers employing string concatenation, is security—specifically something called SQL injection. If you are not familiar with this term, I encourage you to put aside this book for a moment and, using the search engine of your choice, look up SQL injection. There are over five million hits returned for it as I write this edition. The problem of SQL injection is well documented.

a SQl plus session logged in and connected with SYSDBa privileges. You are just begging someone to come by and type

in some command, compile it, and then execute it. the results can be disastrous.

17

Chapter 1 ■ Developing SuCCeSSful oraCle appliCationS

example; they could all be worked around by someone wanting to steal your data.

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