Database Reference
In-Depth Information
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 topic 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.
■
SQl injection is a security hole whereby the developer accepts input from an end user and concatenates that
input into a query, then compiles and executes that query. in effect, the developer accepts snippets of SQl code from the
end user, then compiles and executes those snippets. that approach allows the end user to potentially modify the SQl
statement so that it does something the application developer never intended. it's almost like leaving a terminal open with
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.
Note
It is a fact that if you do not use bind variables, that if you use the string concatenation technique in
PROC2
shown
earlier, your code is subject to SQL injection attacks and must be carefully reviewed. And it should be reviewed by
people who don't actually like the developer who wrote the code—because the code must be reviewed critically and
objectively. If the reviewers are peers of the code author, or worse, friends or subordinates, the review will not be as
critical as it should be. Developed code that does not use bind variables must be viewed with suspicion—it should be
the exceptional case where bind variables are not used, not the norm.
To demonstrate how insidious SQL injection can be, I present this small routine:
EODA@ORA12CR1> create or replace procedure inj( p_date in date )
2 as
3 l_username all_users.username%type;
4 c sys_refcursor;
5 l_query varchar2(4000);
6 begin
7 l_query := '
8 select username
9 from all_users
10 where created = ''' ||p_date ||'''';
11
12 dbms_output.put_line( l_query );
13 open c for l_query;
14
15 for i in 1 .. 5
16 loop
17 fetch c into l_username;
18 exit when c%notfound;
19 dbms_output.put_line( l_username || '.....' );
20 end loop;
21 close c;
22 end;
23 /
Procedure created.
