Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2011
    Posts
    31

    Unanswered: Encrypt function contents

    Good morning, I have Table A with column1 and Table B with column 2. Both have the same data type. I am trying to confirm if DB2 will place the same 'encrypted' value in each table when the contents of the column are identical when using the ENCRYPT function. Using a column that contains Surname and encrypting it I was hoping that when the contents matched 'SMITH' for example in would place the same encrypted value in each of the tables (Table 1 & Table 2). Running tests show them to have different values. I realize I can unencrypt successfully but this question pertains to the value that is stored in DB2.

    Results
    Table 1 surname = 'SMITH' encrypted contents 0ccd33ff0333d59981b36bba60c37f53e4697d1fc881830a58 6f457375bee47546a6d795c01eab021b971bde60e46edc79f8 c70164e6f9ed12be9c1816cd66a2

    Table 2 surname = 'SMITH' encrypted contents 0c33cdff0333d5ae70031dcbdcef8227

    thanks.....

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    seems to me you have already answered your own question. From my understanding the only way for them to be the same would be if you set up your own column function/fieldproc that does your own home rolled encryption.
    Dave

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by chippib View Post
    Running tests show them to have different values.
    I don't know how you did the tests so I can't tell what you did wrong, but given the same input data, hint, and password the ENCRYPT() function will always return the same results.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Nov 2011
    Posts
    31
    Thanks for the reply. I was remiss in not attaching how I tested. I will add these details shortly. In the interim for clarification purposes to your response. The encrypted column value for each common Surname value ('SMITH') is identical in each table. The values attributed to 'SMITH' in TABLE B are not similar to TABLE A, but are identical within the same table. Are you implying that assuming the data types are identical and the same password is used for the encrypt function (HINT is irrelevant) the resulting encrypted contents will be the same for all tables where you encrypt the same VARCHAR value ? That would seem to be a security breach as eventually someone would be able to determine that '0ccd33ff0333d59981b36bba60c37f53e4697d1fc881830a5 8' is the encrypted version of 'SMITH'.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by chippib View Post
    someone would be able to determine that '0ccd33ff0333d59981b36bba60c37f53e4697d1fc881830a5 8' is the encrypted version of 'SMITH'.
    Really? How? Are you confusing encryption with hashing, may be?
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Nov 2011
    Posts
    31
    Good morning, I'm referring to selecting the ENCRYPTED column as is (i.e. without decrypting it with the assigned password).

    select cola_encrypt from tablea;
    vs
    select decrypt_char(cola_encrypt,'PASSWORD') from tablea;

    I've read that the Encryption algorithm uses an internal encryption algorithm with a RC2 block cipher with padding; the 128-bit secret key is derived from the password using an MD5 message digest.

    My apologies for the confusion in not explaining this very well. I interpret from your response is that if I have identical text to be encrypted on different tables, providing I use the same encryption password and the data types are similar I should yield the same ENCRYPTED value?

    thanks

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    seems to me you have already answered your own question.

    I already answered this before. If you want the encrypted value to be the same no matter where you store it then you should investigate rolling your own encryption via a function/trigger/fieldproc or combination of such. As you have already PROVEN to yourself DB2 does not store the encrypted data the same across tables.
    Results
    Table 1 surname = 'SMITH' encrypted contents 0ccd33ff0333d59981b36bba60c37f53e4697d1fc881830a58 6f457375bee47546a6d795c01eab021b971bde60e46edc79f8 c70164e6f9ed12be9c1816cd66a2

    Table 2 surname = 'SMITH' encrypted contents 0c33cdff0333d5ae70031dcbdcef8227
    Dave

  8. #8
    Join Date
    Nov 2011
    Posts
    31
    My apologies Dave, my last reply was in response to a posted thread by n_i yesterday afternoon. Perhaps I misinterpreted the thread but I thought the individual implied that the encrypted values for 'SMITH' should be same in ALL tables assuming the datatypes and encrypted password was the same.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •