Data Encryption in Transit - Expert Oracle and Java Security - page 129

You will be happy to note that when we test this, these attempts at subterfuge are not successful. In
both these cases, as is intended, Oracle database says, “give me something to plug into the test WHERE
LAST_NAME = ?; ”.
We will be giving Oracle database this string: “King' or 'a'='a”, which one might imagine being
wrapped in single quotes to become:
WHERE LAST_NAME = 'King' or 'a'='a';
However, Oracle database sees our string as a single data element and checks to see if anyone's
LAST_NAME is (in escaped form): “King\' or \'a\'=\'a” or “{King' or 'a'='a}”.
Executing the HR Package Specification and Body
Now that we have described the procedures in the hr_sec_pkg package, we will go ahead and execute the
CREATE statements for the package specification and the package body. Execute the two blocks in the file
named Chapter7/HR.sql to create the hr_sec_pkg package specification and body. After you have created
hr_sec_pkg , you will need to grant execute on the package to the hrview_role role
GRANT EXECUTE ON hr.hr_sec_pkg TO hrview_role;
Inserting an EMPLOYEES Record: Update a Sequence
We need a fixed EMPLOYEE_ID , number 300 for a record in the EMPLOYEES table in order for our example
code to work. When the sample EMPLOYEES table is initially installed, there are about 100 records, with
EMPLOYEE_ID s from 100 to about 200. Generally, inserts into the EMPLOYEES table use the next value of the
sequence , EMPLOYEES_SEQ like this (do not execute this yet—this is for reference only):
INSERT INTO employees
(employee_id, first_name, last_name, email, phone_number, hire_date,
job_id, salary, commission_pct, manager_id, department_id)
VALUES
( employees_seq.NEXTVAL , 'David', 'Coffin', 'DAVID.COFFIN',
'800.555.1212', SYSDATE, 'SA_REP', 5000, 0.20, 147, 80);
Every time that SEQUENCE.NEXTVAL is called, the value is incremented. To see the current (next) value
of EMPLOYEES_SEQ , execute this command:
SELECT last_number FROM user_sequences WHERE sequence_name='EMPLOYEES_SEQ';
 Note You can find a script of the commands in this section in the file named Chapter7/HR.sql .
There is no sanctioned way to manually set the LAST_NUMBER for a sequence. However, we can adjust
the increment value to get the desired effect. First, assure that the current LAST_NUMBER returned in the
command above is less than 300 (our example EMPLOYEE_ID .) If it's not, you may have to substitute a
number larger than the LAST_NUMBER in our example code or update the data at EMPLOYEE_ID 300.
To get set up to insert our example EMPLOYEES record at EMPLOYEE_ID = 300, we need to get the
LAST_NUMBER of EMPLOYEES_SEQ to be equal to 300 We are going to do that with an anonymous (unnamed)