How to decrypt a single encrypted value

merik · #1 (**permalink**) 05-09-11, 15:08

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?

merik · #2 (**permalink**) 05-11-11, 11:00

Bump, any comments?

n_i · #3 (**permalink**) 05-11-11, 13:12

Quote:

Originally Posted by merik

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

SELECT DECRYPT_CHAR('some_encrypted_value');

This does not look like a valid SELECT statement, decrypt or not.

merik · #4 (**permalink**) 05-17-11, 12:17

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?

n_i · #5 (**permalink**) 05-17-11, 12:28

Quote:

Originally Posted by merik

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

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')).

merik · #6 (**permalink**) 05-18-11, 13:02

Quote:

Originally Posted by n_i

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.

Quote:

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

merik · #7 (**permalink**) 05-18-11, 13:07

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.

n_i · #8 (**permalink**) 05-18-11, 13:56

Quote:

Originally Posted by merik

That gives an error message:

That's why I said "depending on context".

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

merik · #9 (**permalink**) 05-19-11, 11:23

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?

n_i · #10 (**permalink**) 05-19-11, 11:34

To cast something to a different datatype you use, surprisingly, the CAST operator. It is described in the manual.

merik · #11 (**permalink**) 05-19-11, 11:37

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.

tonkuma · #12 (**permalink**) 05-19-11, 11:40

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

tonkuma · #13 (**permalink**) 05-19-11, 11:54

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.

merik · #14 (**permalink**) 05-19-11, 12:19

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?

tonkuma · #15 (**permalink**) 05-19-11, 14:32

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

Quote:

...
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

Quote:

...
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.

Internet Services

Web Development

Digital Marketing

Consumer Tech