Database Reference
In-Depth Information
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.
EODA@ORA12CR1> exec NOT_inj(sysdate)
select username
from all_users
where created = :x
PL/SQL procedure successfully completed.
It is a plain and simple fact that if you use bind variables you can't be subject to SQL injection. If you do
not
use
bind variables, you have to meticulously inspect every single line of code and think like an evil genius (one who knows
everything
about Oracle, every single thing) and see if there is a way to attack that code. I don't know about you, but
if I could be sure that 99.9999 percent of my code was not subject to SQL injection, and I only had to worry about the
remaining 0.0001 percent (that couldn't use a bind variable for whatever reason), I'd sleep much better at night than if
I had to worry about 100 percent of my code being subject to SQL injection.
In any case, on the particular project I began describing at the beginning of this section, rewriting the existing
code to use bind variables was the only possible course of action. The resulting code ran orders of magnitude faster
and increased many times the number of simultaneous users that the system could support. And the code was more
secure—the entire codebase did not need to be reviewed for SQL injection issues. However, that security came at a
high price in terms of time and effort, because my client had to code the system and then
code it again
. It is not that
using bind variables is hard, or error-prone, it's just that they did not use them initially and thus were forced to go
back and revisit virtually
all
of the code and change it. My client would not have paid this price if the developers had
understood that it was vital to use bind variables in their application from day one.
Understanding Concurrency Control
Concurrency control is one area where databases differentiate themselves. It is an area that sets a database apart
from a file system and databases apart from each other. As a programmer, it is vital that your database application
works correctly under concurrent access conditions, and yet time and time again this is something people fail to test.
Techniques that work well if everything happens consecutively do not necessarily work so well when everyone does
them simultaneously. If you don't have a good grasp of how your particular database implements concurrency control
mechanisms, then you will:
•
Corrupt the integrity of your data.
•
Have applications run slower than they should with a small number of users.
•
Decrease your applications' ability to scale to a large number of users.
