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 ) ;
 
Search WWH ::




Custom Search