Different encryption in SQL

Question

Different encryption in SQL

Navigation

#1 by (4 votes)

1

I have a table (Users) with 2 rows

nameUser como varchar(50)
passUser como varbinary(200)

The question is that I am entering test data for later processing but I have noticed that entering the same password gives different encryption, for example, to insert values I am using this line of code:

INSERT INTO Users(nameUser, passUser)VALUES('gato', 
EncryptbyPassphrase('keySecret','perro'))

but when I show the table, this comes out:

IF I am using the same key (keySecret) and the same text (dog), do not they have to give me exactly the same?

I am also trying (according to me) to do a SELECT validating that the pass is equal to what is entered

SELECT nameUser
FROM Users
WHERE passUser=EncryptbyPassphrase('keySecret','perro')

I would appreciate knowing why the same EncryptbyPassphrase does not matter.

sql encriptacion tsql

asked by Baker1562 15.08.2018 в 22:46

source

1 answer

Control of schedules with moment js Does anyone know what is the cause of the syntax error?

score 4 · Accepted Answer

The nature of EncryptbyPassphrase is Non-deterministic, that is, each time you invoke it, even if you use the same parameters, the result will be different:

SELECT  EncryptbyPassphrase('Mi frase secreta','perro')
-- Output 0x01000000424CE2500564F0FF5614419368F0BCBC9D5379A91F7744C7
SELECT  EncryptbyPassphrase('Mi frase secreta','perro')
-- Output 0x01000000D18F1C9DD9FF90AE115FA57A321872BA89F9A3913A7D7E8B

And this is due to the nature of the encryption (TRIPLE DES algorithm with a 128 key bit length.) that provides a more secure mechanism. In basic algorithms, you would always get the same result for the same password and data to encrypt, which makes it much easier to attack and consequently the algorithm is much weaker.

This non-deterministic nature has the disadvantage that you can not compare the encrypted values directly, you should decipher them in the first place, for example:

SELECT nameUser
       FROM Users
       WHERE EncryptbyPassphrase('keySecret',passUser) = EncryptbyPassphrase('keySecret','perro')

Or much better:

SELECT nameUser
       FROM Users
       WHERE DecryptByPassphrase('keySecret',passUser) = 'perro'

Anyway, encrypting the passwords to save them in the database is not the best idea, they steal the table and they stole all the passwords. What is styled is to save a HASH, that is, a number that represents the password but does not contain it, the authentication processes then only compare these numbers. I leave you an additional link if you are interested: