Database Reference
In-Depth Information
Rewrite and Refactor Method to Distribute Two-Factor Code
We are going to make one more pass at the
distribute2Factor()
method. We need to incorporate the
application_id
in several places. While we are here, we are also going to
refactor
the code with a bit
more security and organization.
If you look back at this method in the last chapter, you will see that we have two dynamic queries:
one query we build to get data from the
HR.emp_mobile_nos
table and others, and another query we build
to update the
v_two_fact_cd_cache
view. For security, we prefer parameterized procedures and
functions to dynamic queries. This method and these dynamic queries run in the Oracle database and
are unlikely to be susceptible to SQL injection, but we should consider the possibility. What would it take
to do SQL injection in those queries?
The first query takes two parameters. It takes the
oraFmtSt
string for formatting dates, which is
defined locally—that is tamper-proof. It also takes the
osUser
name, which is passed from
f_send_two_factor
, which is in turn passed from our secure application role procedure and is derived
from our SSO process. I can surmise that, in order to accomplish SQL injection in that dynamic query,
the user would have to have an extremely bizarre user name in the operating system—not likely.
The second query (update or insert) takes the two-factor code that we generated locally (tamper-
proof), the IP Address which is sensed by Oracle database (only suspect in our wildest dreams), and the
employee ID, which we got from our
HR
tables and must meet rigid type constraints,
NUMBER(6)
. Once
again, this is not a likely candidate for SQL injection.
Our impetus for moving these queries out of our Java code and into stored procedures, then, is not
justified as a counter to SQL injection. We will do it in any case, because having the database logic in a
stored procedure makes our Java code less dependent on the data organization and more tolerant of
database changes. If the DBAs or our application security manager requires that data tables be changed
or moved, our procedure can be modified to accommodate the changes without requiring a change to
the Java code. We would prefer that database changes only affect native database structures, not Java.
Procedure to get Employee Addresses for Two-Factor Code
Delivery
We build a procedure to get the pager, phone, and other numbers that we will use to distribute our two-
factor authentication code. At the same time, with a single query, we can get the employee e-mail
address and the IP address of the session. We will also get the cached two-factor authentication code for
this user on this application, and the timestamp of the cache. Getting all these data elements at once,
based on the user ID and the application ID provides sufficient data for all our two-factor code
distribution tests and delivery.
Take a look at the list of parameters in Listing 10-8. You'll see that they are mostly
OUT
parameters—
we are returning a lot of data. We only pass three parameters to this procedure: the user ID, the date
format string we discussed in the last chapter, and the application ID that we introduced in this chapter.
We will place
p_get_emp_2fact_nos
in the
appsec_only_pkg
package.
Listing 10-8.
Procedure to Get Addresses for Two-Factor Code Distribution
PROCEDURE p_get_emp_2fact_nos(
os_user hr.v_emp_mobile_nos.user_id%TYPE,
fmt_string VARCHAR2,
m_employee_id OUT hr.v_emp_mobile_nos.employee_id%TYPE,
m_com_pager_no OUT hr.v_emp_mobile_nos.com_pager_no%TYPE,
m_sms_phone_no OUT hr.v_emp_mobile_nos.sms_phone_no%TYPE,
