Database Reference
In-Depth Information
Our error handling is identical to what we did in the last chapter: we hand back the error number
and message through two of the
OUT
parameters. If there is no error, we continue by getting the secret
password key artifacts into local method members, and we also get the
CURSOR
into a
ResultSet
so we can
walk through the data. Since there are about 100 entries in the
EMPLOYEES
table, we will only show the
first one (by reading the
ResultSet.next()
in an
if
block rather than in a
while
block).
Most of the columns are clear text, so we just print them out. But the
SALARY
and
COMMISSION_PCT
values are handed to us by Oracle database as
RAW
encrypted data. (Note that the encrypted
SALARY
is in
the 8
th
position in the
ResultSet
, and the
ResultSet
is in the seventh position in the
Statement
. Those
element numberings are independent.) We will send those
RAW
values to the
getDecryptData()
method,
along with the secret password key artifacts. If we haven't yet built our local copy of the secret password
key, we will build it there; in any case, we will decrypt the data and return it as a
String
. We will also
print that out.
For our demonstration purposes only, we are also going to print out the actual
RAW
(if not
null
),
within parentheses, “()”. If we want to track that across multiple runs through this code, we will see that
it is different each time—owing to using different secret password keys in different Oracle sessions.
At the end of each call to our stored procedures for data encryption, we will close the
OracleCallableStatement
. We may also close the
ResultSet
, even though it is unnecessary—it is
inherently closed when we close the
Statement
, because it was acquired through the
Statement
.
However, it is good practice to explicitly close each
ResultSet
, especially because it is common practice
to reuse a
Statement
—you may open multiple
ResultSets
during the life of a single
Statement
, and to
assure that you are freeing up Oracle resources, you should close each
ResultSet
when you are done
with it.
Selecting All Columns in Encrypted String
Our second example procedure for selecting encrypted data from Oracle database,
p_select_employees_secret
does not select and encrypt individual columns, but rather selects all the
columns and concatenates them into a single, comma-delimited
VARCHAR2
to be encrypted. In this case,
none of the data is sent in clear-text form. At the client, if individual columns of the data are needed, you
will need to parse the decrypted string to acquire individual data elements.
Also in this example, we are using a comma as a delimiter between fields. This assumes that there
are no commas in the data—not often a valid assumption. You can use a different delimiter that is less
likely to occur in the data, like a caret (^)or tilde (~). This is an example only, and you would need to
evaluate your specific requirements for the data before building a procedure like this for your
application.
We call the procedure, decrypt the data and print the results as in partial Listing 7-23.
Listing 7-23.
Encrypt All Data Selected from Employees, from
p_select_employees_secret
stmt = ( OracleCallableStatement )conn.prepareCall(
"CALL hr.hr_sec_pkg.p_select_employees_secret(?,?,?,?,?,?,?,?,?)" );
...
if( rs.next() ) {
System.out.
print
( OracleJavaSecure.
getDecryptData( rs.getRAW( 1 )
,
sessionSecretDESPassPhrase,
sessionSecretDESAlgorithm, sessionSecretDESSalt,
sessionSecretDESIterationCount ) );
if( null != rs.getRAW( 1 ) )
System.out.
print
( " (" +
rs.getRAW( 1 ).stringValue()
+
")" );
