3

How do I hash the admin password in my Users table?

Using SQL Server 2008 R2

I know there is an encryption command is built into PHP but I'm not sure if this is possible using an SQL query.

Example: Table called users, with entry of admin, password is sha1('password')

Mathew Thompson
  • 55,877
  • 15
  • 127
  • 148
Event_Horizon
  • 708
  • 1
  • 11
  • 30
  • Where do you see the "admin password" (maybe you could be more specific) stored as plain text? Are you talking about the password you are using in the connection string your application uses to connect to SQL Server? – Aaron Bertrand Apr 30 '12 at 15:14
  • No no no no, I'm talking about I have a table, called users, and I wish to make an admin account for the table. – Event_Horizon Apr 30 '12 at 15:14
  • 1
    You usually don't encrypt passwords but hash them (i.e. use a function like SHA that isn't reversible but enables you to verify that a password is correct). – Denys Séguret Apr 30 '12 at 15:15
  • Why did you undo my edit? The body of the question still implies that you are talking about the sa password, not passwords you have added to your own table for users that have nothing to do with SQL Server itself. – Aaron Bertrand Apr 30 '12 at 15:17
  • I was in the middle of editing when you changed it. – Event_Horizon Apr 30 '12 at 15:17

3 Answers3

2

Note that none of the above are good way store password hashes. Use PBKDF2 (PKCS #5, RFC2898) to hash the salted password many, many times (OWASP recommends 64,000 times in 2012, doubling every 2 years). Ideally hash it a variable number of times, stored with the random per-user salt.

Even better, bounce the proposed password against a list of known bad passwords, ideally with basic cracking rules already applied (1337 speak translation), so "P@$$w0rd" isn't allowed.

See the OWASP Password Storage Cheat Sheet: https://www.owasp.org/index.php?title=Password_Storage_Cheat_Sheet&setlang=es

PBKDF2
  • 21
  • 2
1

Use HASHBYTES and specify SHA1 as the algorithm. Example:

SELECT HASHBYTES('SHA1', @password);

Edit: as Siva said in the comments though, you should really be storing your passwords differently and at least salting them.

See here: Preferred Method of Storing Passwords In Database

Community
  • 1
  • 1
Mathew Thompson
  • 55,877
  • 15
  • 127
  • 148
  • Should I be using SHA256 if the program may go from internal to external at some point? – Event_Horizon Apr 30 '12 at 15:24
  • Well I know I should be storing them as hashes in the DB, thats kind of why I'm asking how to hash the pass in the first place using SQL. I also know I should be using salts, but I'm not quite sure I understand them completely, are they just another hashed pass added onto the password? Could someone give an example of how they would handle salts using PHP? – Event_Horizon Apr 30 '12 at 15:36
  • I'd go for SHA256 yeah, just to be extra safe, you use the `hash` function in PHP to use SHA256 http://php.net/manual/en/function.hash.php – Mathew Thompson Apr 30 '12 at 16:09
  • As for salting, you're basically just putting a random string of characters in with the password to further prevent it from being cracked. – Mathew Thompson Apr 30 '12 at 16:12
1

There is a difference between encryption and hashing. Encryption translates plain text usually using a cipher and can be reversed to the original input, were a hash cannot be reversed to the original input.

For your query you should use the HASHBYTES function:

DECLARE @YourInput nvarchar(4000);
SELECT @YourInput = CONVERT(nvarchar(4000),'dslfdkjLK85kldhnv$n000#knf');
SELECT HASHBYTES('SHA1', @YourInput);

If you're using SQL Server 2012 you can use: 

SELECT HASHBYTES('SHA_256', @YourInput); // SHA 256 or SHA_512

http://msdn.microsoft.com/en-us/library/ms174415.aspx

Darren
  • 68,902
  • 24
  • 138
  • 144
  • So if I were to hash the password in SQL for that one user, I'd still be able to use it for a login system right? I would just need to pass the password from PHP to SQL hashed? – Event_Horizon Apr 30 '12 at 15:32
  • 1
    @Event_Horizon - you should store everyones password as a hash rather than just the admins. This is better from a security point of view and will also reduce the logic needed for your login system. – Darren Apr 30 '12 at 17:02