RE: help with prepared statement and encrypted field

["Neill Harper" <neill.harper@xxxxxxxx>]

Hi Nancy

You said that the CCSID of the column is 65535, this means that the data is
stored in the column as is, more to this it also means that the Database has
no way of knowing what CCSID the data was originally in.

If the CCSID of your JDBC job and the green screen job are different then
you will not get the results you expect.

You could try defining the field in the DDS of the file as G (graphic) and
putting in the field level keyword CSSID(1200), this will make the field
Unicode and might solve your problem (although admittedly it might cause you
a few more problems).

The alternative is to set the CCSID of the JDBC job to be the same as your
green screen job.

If the above isn't the problem, it could be that you are using a varying
field type, the first 2 (or possibly 4 bytes) of the varying alpha type
contain the current length of the string, if those bytes are getting
encrypted along with the actual data, that might also be causing you a
problem.

Hope the above helps or at least makes sense.

Cheers


Neill

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx [mailto:java400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Nancy Cochran
Sent: 08 September 2008 17:02
To: 'java400-l@xxxxxxxxxxxx'
Subject: help with prepared statement and encrypted field

Hello,

I've been struggling with this for a few days and cannot seem to get it
right.  Although this is a java question I'll start with a little background
as to what's going on.

We have an RPG stored procedure that takes a string and either encrypts or
decrypts it and returns the value for other applications/programs to use.
This program functions correctly when called from another RPG or CL program.
It uses the below code..

  If Function = 'ENCRYPT';
     EXEC SQL SET :ResultVarying = ENCRYPT_TDES(:InputVarying, :pw);
  Endif;

  If Function = 'DECRYPT';
     EXEC SQL SET :ResultVarying = DECRYPT_CHAR(
               cast(:InputVarying as char(5000) for bit data), :pw);
 Endif;

Returns ResultVarying.

   CREATE PROCEDURE x(
           IN METHOD CHAR ( 7),
           IN INSTR CHAR (5000),
           OUT OUTSTR CHAR ( 5000),
           OUT ERROR CHAR ( 50))
   LANGUAGE RPGLE
   NOT DETERMINISTIC
   MODIFIES SQL DATA
   EXTERNAL NAME x
   PARAMETER STYLE GENERAL WITH NULLS

We have a java web application that invokes this procedure sending in a
credit card number to be encrypted.  The encrypted value is then inserted in
a different file.

When I try to use a Prepared statement to insert this value the statement
works, but the value inserted in the field is not correct.  It looks like
encrypted data, but it is not encrypted correctly.  When I try to decrypt it
from the RPG program in a green screen environment I get 'The data is not a
result of a supported encryption function.'  Somewhere the value is getting
converted, but I don't know where.  I don't know if it's through the
procedure definition or through the jdbc prepared statement.

The field in the file is described by DDS as 32A with CCSID(65535).

I have tried a variety java code to try to get this to work with a prepared
statement.  The only way I could get it to insert without a data mismatch is
by ..
String sql = new String("Insert into ncptest (counter, ccnum) " +
            "Values(?, cast(? as char(32) for bit data)) with nc");
pStmt = conn.prepareStatement(sql);
pStmt.setInt(1, x);
pStmt.setObject(2, encryptedValue.getBytes(), java.sql.Types.CHAR);

Is there anything obvious that I'm doing wrong?   I really appreciate any
ideas.  Thanks in advance for any help.