Database Reference
In-Depth Information
We are saying that the
m_employee_id
parameter is of the same type as the
EMPLOYEE_ID
column in
the
EMPLOYEES
table. We will use this form of “type specification by reference” whenever appropriate to
further establish the relation between the data we are receiving and the table it is destined for. This
practice is helpful for at least two reasons. The first is that our procedure will only accept data that is
appropriate for the field into which it will be inserted or updated. This is a further protection against a
SQL injection attack (see a detailed discussion in the next section). The second reason anchoring
datatypes is good is that we can change the definition of that column in the table without having to also
change this procedure.
Listing 7-13.
Update Sensitive Data in the Employees Table,
p_update_employees_sensitive
PROCEDURE p_update_employees_sensitive(
m_employee_id employees.employee_id%TYPE,
m_first_name employees.first_name%TYPE,
m_last_name employees.last_name%TYPE,
m_email employees.email%TYPE,
m_phone_number employees.phone_number%TYPE,
m_hire_date employees.hire_date%TYPE,
m_job_id employees.job_id%TYPE,
crypt_salary RAW
,
crypt_commission_pct RAW
,
m_manager_id employees.manager_id%TYPE,
m_department_id employees.department_id%TYPE,
m_err_no OUT NUMBER,
m_err_txt OUT VARCHAR2 )
IS
test_emp_ct NUMBER(6);
v_salary VARCHAR2(15); -- Plenty of space, eventually a NUMBER
v_commission_pct VARCHAR2(15);
BEGIN
m_err_no := 0;
v_salary := appsec.app_sec_pkg.f_get_decrypt_data( crypt_salary );
v_commission_pct :=
appsec.app_sec_pkg.f_get_decrypt_data( crypt_commission_pct );
SELECT COUNT(*) INTO test_emp_ct FROM employees WHERE
employee_id = m_employee_id;
IF test_emp_ct = 0
THEN
INSERT INTO employees
(employees_seq.NEXTVAL, first_name, last_name, email, phone_number,
hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES
(m_employee_id, m_first_name, m_last_name, m_email, m_phone_number,
m_hire_date, m_job_id, v_salary, v_commission_pct, m_manager_id,
m_department_id);
ELSE
-- Comment update of certain values during testing - date constraint
UPDATE employees
SET first_name = m_first_name, last_name = m_last_name, email = m_email,
phone_number = m_phone_number,
-- Job History Constraint -- hire_date = m_hire_date, job_id = m_job_id,
salary = v_salary, commission_pct = v_commission_pct,
