Database Reference
In-Depth Information
characters rather than text.) However, a better way is to always use parameterized input. We do that with
our stored procedures that take parameters. We are not building dynamic queries but are pumping
parameters into PL/SQL that has already been staged in Oracle database. The database
binds
the
variables to our query/update framework.
We can also prevent SQL injection from the client with prepared statements in Java like the
following. The value of
userInputEmpID
is bound to the query parameter at the question mark (?).
String query = "SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID =
?
";
PreparedStatement pstmt = connection.prepareStatement( query );
pstmt.setString( 1,
userInputEmpID
);
Having a
PreparedStatement
accept our parameter and populate the query with it, keeps malicious
code from being tacked onto the query. Once again, the
PreparedStatement
is staged in Oracle database
and our parameters are set there, Oracle database binds them to the update/query.
If you need to place code for an Oracle database query in your application (java or other), use a
PreparedStatement
, as seen previously, rather than concatenating user input into a query string.
Demonstrating Failure to SQL Inject in Stored Procedure
I have included two more procedures in the
hr_sec_pkg
that will demonstrate attempts at SQL injection
in stored procedures. I don't hail from Missouri, but I am from the “Show-Me State” of mind: trust, but
verify. Let's try SQL injection when doing a select query on
LAST_NAME
. In the procedure partially shown
in Listing 7-15,
p_select_employee_by_ln_sens
, we will pass in a tenth parameter, the
LAST_NAME
and
modify our select in the procedure to use it:
Listing 7-15.
Select Employees Data by Last Name and Attempt SQL Injection
PROCEDURE p_select_employee_by_ln_sens(
...
m_last_name
employees.last_name%TYPE )
IS BEGIN
...
OPEN resultset_out FOR SELECT
...
FROM employees
WHERE last_name = m_last_name
;
Let's also see if we can sneak some SQL injection in by embodying it in a
RAW
(like we do with our
encrypted data updates) and by casting the
RAW
to a
VARCHAR2
in the
WHERE
clause. We do that in a test
procedure named
p_select_employee_by_raw_sens
in Listing 7-16.
Listing 7-16.
Select Employees Data by RAW Value and Attempt SQL Injection
PROCEDURE p_select_employee_by_raw_sens(
...
m_last_name RAW )
IS BEGIN
...
OPEN resultset_out FOR SELECT
...
FROM employees
WHERE last_name = UTL_RAW.CAST_TO_VARCHAR2( m_last_name )
;