I am encrypting a field that is password
for it my table is called usuario
that has these fields:
idlogin
username
llave
Nombres
dni
To encrypt I am using this procedure when inserting a record:
create PROCEDURE RegistrarUsuario
@username nvarchar(50),
@llave nvarchar(50),
@nombres nvarchar(500),
@dni nvarchar(8)
AS
BEGIN
INSERT INTO dbo.usuario
(username,llave,Nombres,dni)
VALUES (@username
, ENCRYPTBYPASSPHRASE('password', @llave)
,@nombres
,@dni
)
end
GO
But when registering in the key field that is encrypted, it comes out blank:
Initially, the field
Llave
must be declared of typeVARBINARY
, this so that the encrypted value is saved there. For this, the definition of the table should be as follows (or at least something similar):Next, the save for the field
Llave
should be with:So that the INSERT is as follows:
Now, to recover the encrypted password, it will be with:
So that you
SELECT
are as follows:Here you can see thedemostración
Finally, your Stored Procedure should be as follows, note that the data type of the parameter
@llave
is aVARCHAR
and not aNVARCHAR
:Here you can see the seconddemostración
Note: I'm not sure if the terms encrypted and decrypted exist but I hope this will help you understand the answer better.
One of the most common solutions is to use an
salt
additional for each entry.Example in PHP, but the concept applies:
When creating the record, generate a salt and a hash by concatenating the password with the salt.
Store in database
$salt
and$saltedPassword
.When verifying if the key is valid, you use the same concatenation with the salt but this time with the key to verify and compare it with
$saltedPassword
:This way you are less vulnerable to password cracking en masse with a hash dictionary attack.