Databases Reference
In-Depth Information
■
Note
The code in Figure 8-18 makes use of the SQL Delimiter feature. By prefacing a string with
q'{
rather
than just a single quote (
'
), all subsequent single quotes are automatically escaped until
}'
is encountered.
This allows the query to only apply a where clause if
P4 LAST NAME
or
P4 FIRST NAME
is not null. As
coded above there is no chance for SQL Injection. Coded differently, though, there is a vulnerability:
declare
l q varchar2(32767);
begin
l q := 'select username, first name, last name
from my users
where 1=1 ';
if :P4 LAST NAME is not null then
l q := l q || q'{ and last name like '}' ||:P4 LAST NAME ||q'{%' }' ;
end if;
if :P4 FIRST NAME is not null then
l q := l q || q'{ and first name like :P4 FIRST NAME ||'%' }' ;
end if;
return l q;
end;
In this code,
:P4 LAST NAME
is concatenated into the string while
:P4 FIRST NAME
is treated as a bind
variable. Fortunately, APEX uses bind variables in all SQL or PL/SQL it generates, and bind variables
prevent SQL Injection. Developers must be cautious not to convert bind variables to concatenated
strings.
Password Cracking
Earlier in this chapter, I discussed changing a user's password to obtain access to his account. Worse
than having your password changed is having it discovered, particularly if you use that password on
multiple systems. When Anonymous used SQL Injection to obtain the list of usernames and passwords,
the passwords were fortunately obfuscated. Data can be obfuscated in a variety of ways, but generally
this falls into two broad categories: encryption and hashing. An encryption algorithm transforms the
source string in such a way to be unrecognizable, but a decryption algorithm exists that can decrypt the
resulting value, transforming it back to the original. Hashing also transforms the source string so as to be
unrecognizable, however, there is no “reverse” algorithm. That is, if f is a hash function and p a
password, there does not exist a function f' such that f'(f(p)) = p. In a real example, given a password
“tiger”, applying the encryption function f(tiger) = ac3xad99d99aa, there does not exist a function that
will convert ac3xad99d99aa back into “tiger”. Passwords are often obfuscated with a one-way hash
function. That's great news, especially if you use the same password in multiple places. Hash algorithms,
though, have the additional property that many f(p) may equal f(p') where p != p'. That is, two different
passwords may generate the same hash: f(tiger) = ac3xad99d99aa and f(leopard) = ac3xad99d99aa.
Unfortunately, a great deal of effort has gone into cracking passwords. Rainbow tables are pre-
computed solutions to common hash algorithms and are readily available. Consider Table 8-1, in which
both tiger and kitten have the same hash value.
