Hi,

To store (and use) our users password in the database, we use the couple of function ENCRYPT and DECRYPT.

To encrypt the password we use the following instruction :

UPDATE USR SET UsrPass = ENCRYPT('toto', 'TheVeryLongKey', '(AES256') WHERE ...

Lately we decide to change the key of encryption, so to get all the users password of a database we execute the folowing select :

SELECT UsrID, CAST(DECRYPT(UsrPass, 'TheVeryLongKey, 'AES256') AS LONG NVARCHAR) AS UsrPass FROM USR ORDER BY UsrID"

For most of the our client database we didn't have problems but on two database we have this error : ERROR : -851 08W63 Decryption error: Input must be a multiple of 16 bytes in length for AES

I d'ont understand why we have this error and how to "fix" it.

asked 09 Dec '21, 12:05

Ben8sens's gravatar image

Ben8sens
13181118
accept rate: 37%

Aside: Do you need to store actual (albeit encrypted) passwords? Often it is more secure to never store them but only their (probably salted) hash. That's what SQL Anywhere does itself, too.

(09 Dec '21, 15:42) Volker Barth
Replies hidden
1

FWIW: Is the according column a (LONG) BINARY? AFAIK, otherwise character set conversion could lead to issues.

(10 Dec '21, 03:44) Volker Barth
Replies hidden

Yes the column is a long binary.

(10 Dec '21, 06:36) Ben8sens

It is the fastest way for us to secure this part, but I know it is not the optimal way to do it.

(10 Dec '21, 06:38) Ben8sens

We had a similar situation where we had to change the encryption key. After much QA and discussions we decided to prompt the users with new passwords. We also lost the password history for each user but it was deemed that it is a one time effort for the users. There may be bad data in the databases due to whatever reasons.

(10 Dec '21, 09:36) chinmaydixit
Replies hidden

There may be bad data in the databases due to whatever reasons.

Oops. That's undesireable.

(10 Dec '21, 10:31) Volker Barth
showing 3 of 6 show all flat view

I finally found the solution : the "usrpass" was not encrypted for only one user in those twoe database !

Thank you all for your responses. I will have a little chit chat with some people on monday :).

Fun fact, when I execute "SELECT UsrID, CAST(DECRYPT(UsrPass, 'TheVeryLongKey', 'AES256') AS LONG NVARCHAR) AS UsrPass FROM USR ORDER BY UsrID" in interactive sql I don't have the error message, it manage to give me a readable string.

permanent link

answered 10 Dec '21, 11:51

Ben8sens's gravatar image

Ben8sens
13181118
accept rate: 37%

Other than the typo in the encryption algorithm '(AES256', your commands look OK, i.e.

SELECT CAST(DECRYPT(
ENCRYPT('toto', 'TheVeryLongKey', 'AES256'), 'TheVeryLongKey, 'AES256') AS LONG NVARCHAR)

does return 'toto'. This would imply that somewhere in your database there are rows where the column contains a value not inserted using this method. You can run select UsrId from USR where length(UsrPass) % 16 <> 0 to find these rows. As for what to do about them, I don't know. It would depend on what is in these rows - perhaps an unencrypted value got inserted accidentally?

permanent link

answered 09 Dec '21, 12:23

Graeme%20Perrow's gravatar image

Graeme Perrow
9.6k379124
accept rate: 54%

You can also check with the isencrypted function whether the data has been encrypted with the specified algorithm and key.

(09 Dec '21, 15:38) Volker Barth
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:

×46

question asked: 09 Dec '21, 12:05

question was seen: 165 times

last updated: 10 Dec '21, 11:51