Databases Reference
In-Depth Information
that number of seconds for the lock to free before raising an error. See the APEX online help for more
details on
APEX DML LOCK WAIT TIME
and a related parameter
FSP DML LOCK ROW
.
SQL Injection
After HBGary indicated that it was on the trail of Anonymous, one of Anonymous's first steps was to find
a SQL Injection vulnerability in the HBGary website. There are many open source scanning tools that
can scan a website for vulnerabilities. These tools are generally intended to be run by the owner of the
site in an effort to secure it. High quality firewalls can often detect such a scan before it completes, but
not always. These firewalls can be expensive and, though they may prevent someone discovering a SQL
Injection vulnerability, they don't protect against it once it is discovered.
A SQL Injection Primer
In the early days of database-driven websites few people thought about SQL Injection. Up until this
point most SQL run against the database was very controlled and the statements came from purpose-
built applications with proprietary interfaces. Users were unlikely to be able to bypass screen edits and
enter data that the developer never expected. Some technologies were very prone to the vulnerabilities,
while others were by nature almost immune.
SQL Injection is simply adding additional SQL to a statement, changing the way it runs from the way
it was intended to run. Consider the following SQL statement:
select user id
from my users
where email = :P EMAIL
This statement uses a bind variable,
:P EMAIL
. If you run this statement in Oracle SQL Developer or
the APEX SQL Worksheet you will be prompted for the value of
:P EMAIL
. The tools recognize this as a
bind variable and present the database with the SQL Statement and the value of
:P EMAIL
. Many old web
development tools, and even some more modern tools, take a different approach. These tools build up a
query string and present it to the database. Consider the pseudo code below:
declare input string pEMail
declare string sQuery = "select username from my users where email = '" + pEMail + "'"
declare array aResults
open database connection
execute sQuery store results in aResults
for i in aResults loop
print aResults[i]
end loop
close database connection
This seems like a reasonable approach to having a programming language that is not database
specific execute a SQL statement and get the results. The pseudo code above takes an input of
pEMail
and dynamically builds a SQL statement to return
USERNAME
s associated with the given email address.
Given an input of
pEMail = john.doe@mycompany.com
, the resulting SQL statement would be
select
username from my users where email = 'john.doe@mycompany.com'
.
PL/SQL can accomplish the same task using
execute immediate
, though it is more difficult than
typical PL/SQL.
