Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2011
    Posts
    38

    Unanswered: How to decrypt a single encrypted value

    I'm running SELECT queries on a database and I don't want sensitive data to be transfered all the time to the client, so I use ENCRYPT for that. I start with:

    SET ENCRYPTION PASSWORD 'SomePassPhrase';

    And then I do:

    SELECT ENCRYPT(SensitiveField) AS EncField, .... FROM table1 WHERE ...;

    Now, occasionally, I want to decrypt one single value of the EncField to its original value. I know that DECRYPT_CHAR does it, but I don't know how to use it. Trying something like

    SELECT DECRYPT_CHAR('some_encrypted_value');

    Gives me an error message. I couldn't figure out the correct format from the documentation either.

    Can you please tell me the correct format?

  2. #2
    Join Date
    Apr 2011
    Posts
    38
    Bump, any comments?

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by merik View Post
    SELECT ENCRYPT(SensitiveField) AS EncField, .... FROM table1 WHERE ...;
    I'm sure you realize that the statement above does _not_ encrypt the column value in the table, so essentially there's nothing to decrypt.

    Quote Originally Posted by merik View Post
    SELECT DECRYPT_CHAR('some_encrypted_value');
    This does not look like a valid SELECT statement, decrypt or not.

  4. #4
    Join Date
    Apr 2011
    Posts
    38
    The encrypt statement does NOT encrypt anything in the table, but does encrypt them in the "output". Now, I want to decrypt a single value.

    I can rephrase my question this way: How can I run a select without a FROM? In MySQL, you could do "select 1" and get 1 as the result. What is the DB2 equivalent?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by merik View Post
    The encrypt statement does NOT encrypt anything in the table, but does encrypt them in the "output". Now, I want to decrypt a single value.
    I'm not sure I understand what you are trying to achieve. Normally one would encrypt data when storing them in the database and decrypt when selecting them. You are storing unencrypted data and encrypt them when selecting. If you need to protect these data from the client, may be you just needn't select them at all?

    Quote Originally Posted by merik View Post
    I can rephrase my question this way: How can I run a select without a FROM? In MySQL, you could do "select 1" and get 1 as the result. What is the DB2 equivalent?
    Depending on context, it might be VALUES(DECRYPT('whatever')).

  6. #6
    Join Date
    Apr 2011
    Posts
    38
    Quote Originally Posted by n_i View Post
    I'm not sure I understand what you are trying to achieve. Normally one would encrypt data when storing them in the database and decrypt when selecting them. You are storing unencrypted data and encrypt them when selecting. If you need to protect these data from the client, may be you just needn't select them at all?
    Because the data is needed in the unecrypted forms in the database for some tasks, but for other pilot tasks, we would rather the actual sensitive data not to be shown on the screen. Rarely, something interesting is found in the pilots and we want to decrpyt it back to the original value, and for that, we need to have a way to DECRYPT_CHAR the encrypted value.
    Depending on context, it might be VALUES(DECRYPT('whatever')).
    That gives an error message:

    Code:
    VALUES(DECRYPT_CHAR('0839d0ffb8040ae54d96661cfb6c1234'))
    The data type, length or value of the argument for the parameter in position "1" of routine "SYSIBM.DECRYPT" is incorrect. Parameter name: "".. SQLCODE=-171, SQLSTATE=42815, DRIVER=4.11.69
     
    Failed queries => 1
     
    Total execution time => 0 ms

  7. #7
    Join Date
    Apr 2011
    Posts
    38
    A working example follows:

    Code:
    CREATE TABLE EMP (SSN VARCHAR(24) FOR BIT DATA);
    
    SET ENCRYPTION PASSWORD = 'Ben123';
    
    INSERT INTO EMP(SSN) VALUES ENCRYPT('289-46-8832');
    
    SELECT DECRYPT_CHAR(SSN) FROM EMP; -- Correctly shows 289-46-8832
    
    SELECT SSN FROM EMP;
    
    VALUES(DECRYPT_CHAR('085fa1ffb804a0d55a34c0bff8d3d3dc3390e31b3ba6bc10')); -- Gives an error message
    I found some information online that I might need to cast the parameter of DECRYPT_CHAR as VARCHAR FOR BIT DATA, but I don't know how to do that.
    Last edited by merik; 05-19-11 at 12:30. Reason: more explanation

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by merik View Post

    That gives an error message:
    That's why I said "depending on context".

    Try SELECT x FROM TABLE(VALUES(DECRYPT...)) t(x)

  9. #9
    Join Date
    Apr 2011
    Posts
    38
    Still the same issue:

    Code:
    The data type, length or value of the argument for the parameter in
    position "1" of routine "SYSIBM.DECRYPT" is incorrect. Parameter
    name: "".. SQLCODE=-171, SQLSTATE=42815, DRIVER=4.11.69
    How can I cast a string (like '12a4ed532') into CHAR FOR BIT DATA type?
    Last edited by merik; 05-19-11 at 12:32.

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    To cast something to a different datatype you use, surprisingly, the CAST operator. It is described in the manual.

  11. #11
    Join Date
    Apr 2011
    Posts
    38
    Okay. So the cast is now working, but I get a new error message:

    Code:
    VALUES(DECRYPT_CHAR(CAST('085fa1ffb804a0d55a34c0bff8d3d3dc3390e31b3ba6bc10' AS VARCHAR(11) FOR BIT DATA)));
    
    The decryption function failed. The data is not encrypted.. SQLCODE=-20146, SQLSTATE=428FE, DRIVER=4.11.69
    However, the data provided is encrypted using the working example above.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SQL20146N The decryption function failed. The data is not encrypted.
    Explanation: The data must be a result of the ENCRYPT function.
    User response: Ensure that the data type is a result of the ENCRYPT function.
    sqlcode: -20146
    sqlstate: 428FE

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You made at least two mistake.
    1) forgot x
    2) CAST to shorter string, then the string must be truncated.

    Code:
    ------------------------------ Commands Entered ------------------------------
    VALUES DECRYPT_CHAR( CAST(x'085fa1ffb804a0d55a34c0bff8d3d3dc3390e31b3ba6bc10' AS VARCHAR(24) FOR BIT DATA) );
    ------------------------------------------------------------------------------
    
    1               
    ----------------
    289-46-8832     
    
      1 record(s) selected.
    Last edited by tonkuma; 05-19-11 at 13:06. Reason: Add comment. Change from VARCHAR(100) to VARCHAR(24)

  14. #14
    Join Date
    Apr 2011
    Posts
    38
    Thank you for your detailed response. It definitely worked.

    Is there any specific part of the documentation that you suggest I should read to understand why a longer string was needed, and how long it should be?
    Last edited by merik; 05-19-11 at 13:19. Reason: more clear

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DB2 for LUW SQL Reference, Volume 1 ---> Chapter 3. Functions ---> Scalar Functions ---> "ENCRYPT" and "DECRYPT_BIN and DECRYPT_CHAR"
    or
    Information Center ---> Database Fundamentals ---> SQL ---> Functions ---> Scalar Functions ---> "ENCRYPT" and "DECRYPT_BIN and DECRYPT_CHAR"

    For example:

    In the description of ENCRYPT
    ...
    The result data type of the function is VARCHAR FOR BIT DATA.
    •When the optional hint parameter is specified, the length attribute of the result is equal to the length attribute of the unencrypted data + 8 bytes + the number of bytes until the next 8-byte boundary + 32 bytes for the length of the hint.
    When the optional hint parameter is not specified, the length attribute of the result is equal to the length attribute of the unencrypted data + 8 bytes + the number of bytes until the next 8-byte boundary.
    In the description of DECRYPT_BIN and DECRYPT_CHAR
    ...
    encrypted-data
    An expression that returns a CHAR FOR BIT DATA or VARCHAR FOR BIT DATA value as a complete, encrypted data string. The data string must have been encrypted using the ENCRYPT function.

Tags for this Thread

Posting Permissions

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