on 2013 Aug 06 7:55 PM
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.
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
User | Count |
---|---|
45 | |
9 | |
8 | |
6 | |
5 | |
5 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.