Database Reference
In-Depth Information
WHERE employee_id = m_employee_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
m_err_no := SQLCODE;
m_err_txt := SQLERRM;
appsec.app_sec_pkg.p_log_error( m_err_no, m_err_txt,
'HR p_update_emp_mobile_nos' );
END p_update_emp_mobile_nos;
END hr_pub_pkg;
/
GRANT EXECUTE ON hr.hr_pub_pkg TO hrview_role;
The process of selecting data from the new
hr_pub_pkg.p_select_emp_mobile_nos_by_id
procedure
is very similar to the procedure we reviewed earlier for selecting data from the
v_employees_public
view.
However, in the case of the data from
v_emp_mobile_nos
, there is no sensitive data, so none of the
columns is returned in encrypted form. This procedure also gets called from the delayed
Thread
run()
method of the
existingEmpComboBox_actionPerformed()
method of
AddUser
. That call is shown in Listing
12-22. The only unique thing here is that we disable the
osUserIDTextField
when we populate it from
this query. We do that because we do not allow the user ID to change once set for an employee.
Disabling this field allows the value to be presented, albeit in grayed-out form, but doesn't allow the data
value to be edited. If this query does not find values in
v_emp_mobile_nos
for the employee ID, then we
enable the
osUserIDTextField
.
Listing 12-22.
Abbreviated run() Method when Selecting Existing Employee, Part 2
// Select from mobile_nos where emp id
stmt = (OracleCallableStatement)conn.prepareCall(
"CALL
hr.hr_pub_pkg.p_select_emp_mobile_nos_by_id
(?,?,?,?)");
...
stmt.setInt(1, employeeID);
...
if (rs.next()) {
// Will not let you change a user ID for an employee
osUserIDTextField.setEnabled(false)
;
osUserIDTextField.setText(rs.getString(1));
pagerNumberTextField.setText(rs.getString(2));
smsPhoneNumberTextField.setText(rs.getString(3));
smsCarrierCodeComboBox.setSelectedItem(rs.getString(4));
} else {
osUserIDTextField.setEnabled(true)
;
}
These two Oracle stored procedures (shown in Listings 12-20 and 12-22) represent dynamic queries
where the query includes the requirement that
employee_id = user_selected_employee_id
. Dynamic
queries like this are susceptible to SQL injection, unless they are encapsulated in Oracle stored
procedures like these. This is a design for security concern that should be considered in every security
code review.
