Multi tool use

Clash Royale CLAN TAG

Update a table by using PWDENCRYPT

I have an application which use a database and a users table with login and password. The passwords weren't crypted so I searched how to crypt them.
I use SQL Server 2017, and I found the PWDENCRYPT function and PWDCOMPARE.

PWDENCRYPT

PWDCOMPARE

So I would like to update my users table to encrypt all the passwords and then edit my application to use pwdcompare.

I tested before and there are some behaviors I don't really understand, and I didn't find examples on the internet except for the basic utilisation like PWDENCRYPT('password') or PWDCOMPARE('password',password_hash).

PWDENCRYPT('password')

PWDCOMPARE('password',password_hash)

In SQL Server when write:

select PWDENCRYPT('password')


I have the encrypt password, here it is:

0x02001691959A1D475E3DB65AE8F7E7B70E7929B8EF873F213C7B99DEED82D0E6B35289CB172C1998DDEDE058F9015FA2679EED387E718B4E06EB389223AA152C8793D8BA0CBA


And if I compare this answer and the password the return is 1.
But I make probably something wrong when I try to update a table because the response isn't the same.

If I try to update only one password:

update test_table set password=PWDENCRYPT(password) where id=1


I have one assigned line, but when I go to my table test_table the password doesn't have the same format:

撷굪才踔韩ᰝ奦�畔ι规㿙⩬츀稉肉맿õ雟脀ꐁ燲턃悂샽冄⁽褜⃊޾


I thought it was normal so I tried to compare my string 'password' and this:

select PWDCOMPARE('password',撷굪才踔韩ᰝ奦�畔ι规㿙⩬츀稉肉맿õ雟脀ꐁ燲턃悂샽冄⁽褜⃊޾)


And here I have a syntax error around '' or another caracter depending on the crypted password.

I searched why but I didn't find for SQL server if there is a particular method to update the table. And for the time being I just tested to update one line in a table but after I'd like to update all the passwords to crypt them.

Thank you for reading

password

PWDENCRYPT

VARBINARY

CONVERT(NVARCHAR(256), PWDENCRYPT(...), 2)

VARBINARY(128)

1 Answer
1

That works fine for me. What data type is your password column. PWDENCRYPT returns varbinary.

password

PWDENCRYPT

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.