2

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.

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).

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

Morgane
  • 153
  • 4
  • 13
  • 2
    Your `password` field is text, but `PWDENCRYPT` returns `VARBINARY`. When you display that in Management Studio you'll see a hexstring, but if it's assigned back to text the bytes will be interpreted as if they were code points for characters (which they're not, and they shouldn't be stored that way). You can convert the result to a hexstring (`CONVERT(NVARCHAR(256), PWDENCRYPT(...), 2)` and store that (taking care that your column is actually long enough) but it's a better idea to create a new, dedicated `VARBINARY(128)` column to store the hash specifically. – Jeroen Mostert Jul 31 '18 at 12:01
  • Ok yes I thought about this after, and I saw that PWDENCRYPT is an old function, is it better to use the function HASHBYTES ? – Morgane Jul 31 '18 at 12:09
  • It's not quite as simple as that -- doing it securely in the database is a bit more involved than just calling `HASHBYTES`. See, for example, [this question](https://stackoverflow.com/q/5985708/4137916) (selected moslly at random). A plain hash over passwords without salt is still not secure, and what's more, unless your connection to SQL Server is itself secure, you're still passing passwords in plain text over the network. Generally, it's recommended to perform the hashing client-side, and leave the database to store the binary blobs. – Jeroen Mostert Jul 31 '18 at 12:15
  • So what you mean is that I should crypt the passwords from the application ? I don't know at all what's the better to do about that, because I have also to update all the existing passwords.. – Morgane Jul 31 '18 at 12:19
  • Updating the old passwords is a one-time affair and doesn't necessarily have to happen in a single `UPDATE` statement on the server; you could just have a one-off application issue the `SET HashedPassword = ..., Salt = ... WHERE ID = ...` statements. Writing the hashing code in SQL is still also possible, though, as long as you use the same algorithm. – Jeroen Mostert Jul 31 '18 at 12:24

0 Answers0