cancel
Showing results for 
Search instead for 
Did you mean: 

Unable to decrypt column

Former Member
27,803

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.

Accepted Solutions (0)

Answers (1)

Answers (1)

graeme_perrow
Advisor
Advisor

The column you're storing it into should be of type long binary rather than char(16). If the column is only 16 bytes long, the encrypted data is getting truncated and so it can't be successfully decrypted.

Former Member
0 Kudos

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.

VolkerBarth
Contributor
0 Kudos

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:

Unlike CHAR values, VARBINARY values are not transformed during character set conversion.

graeme_perrow
Advisor
Advisor
0 Kudos

The length is definitely an issue, and I'd also recommend binary rather than char for the reasons @Volker mentions. However, if you never actually retrieve the encrypted data from the column to the client (i.e. you always call decrypt() directly on the column), I don't think the character set conversion thing will be an issue.