Re: SQL Encrypt Function in a Trigger

[Scott Klement <midrange-l@xxxxxxxxxxxxxxxx>]

I'm not familiar with writing triggers in SQL, hopefully someone else 
can help you with that part of it.

But, I wanted to write to point out that RC2 is very weak encryption.  I 
don't recommend using it for credit card numbers or anything else of 
importance.

In V5R4, IBM added an ENCRYPT_TDES function to SQL, which is better.
In V6R1, they added an ENCRYPT_AES function which is what I recommend.



Eftimios Pantzopoulos wrote:
> _______________________________________________________________________________________
>
> Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
> _______________________________________________________________________________________
>
>
> I'm trying to encrypt a 16 byte credit card number and thought I'd try the SQL encrypt function. I found some code on the net that uses a trigger, but can't get my interpretation to work. When I use the encrypt function in an insert statement it works fine, but when I include it in a trigger it fails. Here is the sequence of events:
>
> *       Create a test file of 1 field long enough to hold the encrypted form (16+8=32)
>
> CREATE TABLE MXP02(FLD01 CHAR (32 ) for bit data)
>
> *       Test by using insert. This woks fine:
>
> INSERT INTO QGPL/MXP02 (FLD01)
> VALUES(encrypt_rc2('1234567890123456','password'))
> 1 rows inserted in MXP02 in QGPL.
>
> *       Create a before trigger to encrypt the data before insert (I'm not an SQL/PL expert. I copied and hacked this code:
>
> create trigger insert_mxp02
> before insert on mxp02
> referencing new row as n
>
> for each row
> begin
> set n.fld01= encrypt_rc2(n.fld01,'eftimios');
> end
>
> *       I get the following message when I try to insert a record into the MXP02 table:
>
> SQL trigger INSERT_MXP02 in *N failed with SQLCODE -303 SQLSTATE 220
>
> The IBM SQL Error Code finder tells me (amongst other things):
>
> A FETCH, SELECT, CALL, SET, or VALUES INTO cannot be performed because the data type of host variable &2 is not compatible with the data type of the corresponding list item.
>
> Is there some other sort of declaration I need internal to the trigger?
>
> Thanks in advance.
>
> Mike Pantzopoulos