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.

asked 06 Aug '13, 18:55

vgjain's gravatar image

vgjain
16112
accept rate: 0%

edited 06 Aug '13, 23:15

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371108


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.

permanent link

answered 06 Aug '13, 23:18

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371108
accept rate: 51%

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:

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

(08 Aug '13, 03:43) Volker Barth

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.

(08 Aug '13, 06:40) Graeme Perrow
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×33

question asked: 06 Aug '13, 18:55

question was seen: 25,910 times

last updated: 08 Aug '13, 06:40