|
Hi, I'm running Sybase 12.0.1 database as a network server. I followed an example given in the documentation to encrypt a column (char(16)) in one of my table using AES. I executed the following INSERT statement and encrypted the column "AccNum" within this statement: INSERT INTO "DBA"."statstble" ("TelLnNum","AccNum","CallType","CallSubClass","CallDate","CallTime","CallStatus") VALUES(1, ENCRYPT('01234','8U3dkA'),'Host Authentication','N/A', '2013-08-06','04:45:23.567','PASS') I see the encrypted value of the column in the new inserted row. But when I decrypt this column using the exact same key with the following SQL statement, I am unable to retrieve the original value (01234): SELECT CAST(DECRYPT( AccNum, '8U3dkA' ) AS CHAR(100)) FROM "DBA"."statstble" where calltype = 'Host Authentication' Please share your ideas on what I am doing wrong. |
|
The column you're storing it into should be of type Thanks a lot. That was indeed the problem. However, I didn't have to create a long binary type column to store the encrypted value. I just increased the column storage to char (100) and it worked fine. Thanks again for your help.
(07 Aug '13, 21:39)
vgjain
Replies hidden
100 characters will be enough for shorter strings, however, the result of an encryption is binary data and not character data, so I'd strongly suggest to use long binary or varbinary data types. The builtin function exprtype() can be used to check the type of any expression - the following returns long binary: SELECT EXPRTYPE('SELECT ENCRYPT(''01234'',''8U3dkA'')', 1); With byte_length() you can check the length of a particular string, and the following returns 32 bytes: SELECT BYTE_LENGTH(ENCRYPT('01234','8U3dkA')); Note: If you do use character types to store binary values, you'll be risking character set conversion that might modify the encoded values - cf. this note from the docs on varbinary:
(08 Aug '13, 03:43)
Volker Barth
The length is definitely an issue, and I'd also recommend
(08 Aug '13, 06:40)
Graeme Perrow
|