on 2021 Dec 09 12:05 PM
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
72 | |
9 | |
9 | |
8 | |
8 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.