February 10, 2012 at 2:02 am
Steve Jones - SSC Editor (2/9/2012)
Your code does return the same values, but I'm not sure pre-pending or appending is the issue. I could be wrong, but it seems that length is more important.If I do:
declare @t nvarchar(200)select @t = N'This is my string'
select
Hashbytes('SHA1', @t)
UNION ALL
SELECT Hashbytes('SHA1', @T + N'R@nd0mS!a6lTValue')
UNION ALL
SELECT Hashbytes('SHA1', @T + N'R@nd0mS!a6lTValue2342343')
UNION ALL
SELECT Hashbytes('SHA1', @T + N'R@nd0mS!a6lTValuefvsddgdfgfdgdf')
Adding different values, I get different results. On my machine:
0xB9A02E529093456D139C69FC5E5D4D825B7EC24B
0xCDE457DD8AB6C020E9852FE5B6953E02631A2CB2
0x6872C2C174FD33931D702F321C427D355B28016E
0x208DBF4BE2F339ED5861258F7854F4A6EAAFBE23
The idea here would be to use this in your table. So if I have salaries:
CREATE TABLE employees
( firstname VARCHAR(50)
, pwd VARCHAR(200)
)
GO
INSERT dbo.Employees
VALUES ('Steve', 'Easy')
, ('Bob', 'H@rder')
, ('Andy', 'VeryH$ardP2ssword')
I could hash these as
SELECT HASHBYTES( 'SHA1', Pwd)
FROM Employees
However the results will always be the same, and more important, I could do a copy of the value from Steve's row to Andy's row and then log in as Andy.
However I can salt these to make this type of attack less of an issue:
SELECT HASHBYTES( 'SHA1', Pwd + firstname)FROM Employees
That's simple, and potentially an attacker can still go through all columns in the table, appending and prepending values, but I can make it harder with something like:
SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname)FROM Employees
In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.
Steve,
first of all thanks again for your QotD and for taking the time to dig deeper into this matter.
From what I know, when hashing a string using SHA1 with HASHBYTES, the result is basically a (hex) value of 20 digits (or bytes; see code and results below). Whatever the string to hash consists of, a different result will be calculated (but for a given string it will always be the same).
In your examples you showed several ways, of how to pad a password with additional strings (pre- and/or appending them) to create a more complex (and longer) string to be hashed, thus making it harder to guess (or brute force) the actual password (generally speaking; I'm not an encryption pro and hence cannot discuss the advantages or shortcomings of the various hashing algorithms).
select hashbytes('SHA1', 'abc'), datalength(hashbytes('sha1', 'abc'))
union all
select hashbytes('SHA1', 'cba'), datalength(hashbytes('sha1', 'cba'))
union all
select hashbytes('SHA1', 'abcdef#gh76ij"$%%klmnopq@€rstuvwxyz'), datalength(hashbytes('sha1', 'abcdef#gh76ij"$%%klmnopq@€rstuvwxyz'));
0xA9993E364706816ABA3E25717850C26C9CD0D89D20
0xD9F0509FB7E8BD7D4C4B627DFEC70C0C0E01FB3420
0x90F9C7AC24422A58AF65970D95959F661DFF746B20
Anyway, by changing the word parameter to value in the QotD, my only concern regarding wording was remedied.
As always, this was an excellent question and the following discussion was even better.
Kind regards,
Michael
February 10, 2012 at 3:32 am
Steve Jones - SSC Editor (2/9/2012)
SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname)FROM Employees
In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.
Someone mentioned in another article that all you need to do is run a trace and you can get all this information. So, should the hashing be done on the application side to prevent this type of password retrieval with trace on?
February 10, 2012 at 3:44 am
cengland0 (2/10/2012)
Steve Jones - SSC Editor (2/9/2012)
SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname)FROM Employees
In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.
Someone mentioned in another article that all you need to do is run a trace and you can get all this information. So, should the hashing be done on the application side to prevent this type of password retrieval with trace on?
just a thought - if that SP is encrypted then finding that in the TRACE is not possible (not sure if secured info are generally passed in the plane SQL statements.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
February 10, 2012 at 4:03 am
Raghavendra Mudugal (2/10/2012)
cengland0 (2/10/2012)
Steve Jones - SSC Editor (2/9/2012)
SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname)FROM Employees
In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.
Someone mentioned in another article that all you need to do is run a trace and you can get all this information. So, should the hashing be done on the application side to prevent this type of password retrieval with trace on?
just a thought - if that SP is encrypted then finding that in the TRACE is not possible (not sure if secured info are generally passed in the plane SQL statements.
So you're saying to put the query into an encrypted stored procedure to avoid the trace from getting the passwords? How do you pass parameters to that stored procedure? Wouldn't you do something like:
EXEC sp_somestoreprocedure('salt','password')
Couldn't that command be captured by the trace?
February 10, 2012 at 4:51 am
cengland0 (2/10/2012)
Raghavendra Mudugal (2/10/2012)
cengland0 (2/10/2012)
Steve Jones - SSC Editor (2/9/2012)
SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname)FROM Employees
In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.
Someone mentioned in another article that all you need to do is run a trace and you can get all this information. So, should the hashing be done on the application side to prevent this type of password retrieval with trace on?
just a thought - if that SP is encrypted then finding that in the TRACE is not possible (not sure if secured info are generally passed in the plane SQL statements.
So you're saying to put the query into an encrypted stored procedure to avoid the trace from getting the passwords? How do you pass parameters to that stored procedure? Wouldn't you do something like:
EXEC sp_somestoreprocedure('salt','password')
Couldn't that command be captured by the trace?
I thought SALT is fixed static string in the encrypted SP... you dont need to expose the SALT string as open parameter value, that needs to be kept safe. and on the password use the same concept what you are using now... SALT'ing is just added feature to HASH'ing. Let DB things remain on the DB side, if you want use something more on UI level, you can use any encryption concept besides HASH'ing.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
February 10, 2012 at 8:08 am
cengland0 (2/10/2012)
Someone mentioned in another article that all you need to do is run a trace and you can get all this information. So, should the hashing be done on the application side to prevent this type of password retrieval with trace on?
In general, you simply have to choose how hard you want which potential adversaries to work using which means.
I would say hashing should be done on the application side at this point, because
A) SQL Server does not provide us modern hash algorithms (i.e. the SHA-2 family; SHA-512, SHA-256, SHA-384, Whirlpool, the SHA-3 finalists, etc.)
B) SQL Server does not provide us with an implementation of PBKDF2; even the SHA-1 only version the .NET framework gives us
C) Conditional: When a single SQL Server instance is serving multiple client machines, loading SQL Server up with hundreds of thousands (or more) of hash iterations in order to allow a reasonable measure of security for users who use passphrases/passwords with a smaller total keyspace may end up putting more load on the CPU of the SQL box than is desirable
C1) Do you really want to try to use GPGPU computation on a SQL Server instance from inside SQL?
Raghavendra Mudugal (2/10/2012)
I thought SALT is fixed static string in the encrypted SP... you dont need to expose the SALT string as open parameter value, that needs to be kept safe. and on the password use the same concept what you are using now... SALT'ing is just added feature to HASH'ing. Let DB things remain on the DB side, if you want use something more on UI level, you can use any encryption concept besides HASH'ing.
Adding a salt in and of itself is merely a device to 1) prevent the trivial identification of the same cleartext (i.e. hash A5BC is present in rows 5, 15, 33, and 114; they have the same cleartext value!) and 2) render more difficult precomputed dictionaries.
Using a fixed static string completely precludes reason 1) as the same cleartext + the same salt = the same hash, and nearly completely undermines reason 2), since once an attacker figures out the fixed single salt, they can proceed with precomputing a massive dictionary, and the result takes up the same space (and takes the same amount of time) as it would have without any salt at all.
Salts need to be significantly long and completely random; feel free to add _more_ content, but the useful core of it is a set of random bytes of significant length.
February 10, 2012 at 8:43 am
Raghavendra Mudugal (2/10/2012)
cengland0 (2/10/2012)
Raghavendra Mudugal (2/10/2012)
cengland0 (2/10/2012)
Steve Jones - SSC Editor (2/9/2012)
SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname)FROM Employees
In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.
Someone mentioned in another article that all you need to do is run a trace and you can get all this information. So, should the hashing be done on the application side to prevent this type of password retrieval with trace on?
just a thought - if that SP is encrypted then finding that in the TRACE is not possible (not sure if secured info are generally passed in the plane SQL statements.
So you're saying to put the query into an encrypted stored procedure to avoid the trace from getting the passwords? How do you pass parameters to that stored procedure? Wouldn't you do something like:
EXEC sp_somestoreprocedure('salt','password')
Couldn't that command be captured by the trace?
I thought SALT is fixed static string in the encrypted SP... you dont need to expose the SALT string as open parameter value, that needs to be kept safe. and on the password use the same concept what you are using now... SALT'ing is just added feature to HASH'ing. Let DB things remain on the DB side, if you want use something more on UI level, you can use any encryption concept besides HASH'ing.
First no need to send the SALT string and the password altogether to the database. Simply get the hashed(and salted) password from DB to the application. This can then be compared by hashing (and salting) the user entered password from UI for further validation in the application end. Hence no need to worry about whether the password/salt will be caught in trace or in transportation layer. This works when the SALT is a static string or user name.
Ofcourse, there are more ways to implement it, but I am saying one of them.
February 10, 2012 at 9:24 am
SathishK (2/10/2012)
Raghavendra Mudugal (2/10/2012)
cengland0 (2/10/2012)
Raghavendra Mudugal (2/10/2012)
cengland0 (2/10/2012)
Steve Jones - SSC Editor (2/9/2012)
SELECT HASHBYTES( 'SHA1', 'R@nd0m' + Pwd + firstname)FROM Employees
In this case, without access to the code, it becomes hard to determine what the input values for the hash function are.
Someone mentioned in another article that all you need to do is run a trace and you can get all this information. So, should the hashing be done on the application side to prevent this type of password retrieval with trace on?
just a thought - if that SP is encrypted then finding that in the TRACE is not possible (not sure if secured info are generally passed in the plane SQL statements.
So you're saying to put the query into an encrypted stored procedure to avoid the trace from getting the passwords? How do you pass parameters to that stored procedure? Wouldn't you do something like:
EXEC sp_somestoreprocedure('salt','password')
Couldn't that command be captured by the trace?
I thought SALT is fixed static string in the encrypted SP... you dont need to expose the SALT string as open parameter value, that needs to be kept safe. and on the password use the same concept what you are using now... SALT'ing is just added feature to HASH'ing. Let DB things remain on the DB side, if you want use something more on UI level, you can use any encryption concept besides HASH'ing.
Simply get the hashed(and salted) password from DB to the application. .
how do you suggest to plan this? first to "get" you need to "set", and how are you going to get by bypassing the trace?
(your comments has created a self loop here)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
February 10, 2012 at 9:43 am
Raghavendra Mudugal (2/10/2012)
how do you suggest to plan this? first to "get" you need to "set", and how are you going to get by bypassing the trace?(your comments has created a self loop here)
Seems the database would have two columns. One for Salt and one for HashedPassword. Then, you have your application do the hashing with the RANDOM salt. You store both in the database.
Reverse engineering a HashedPassword is difficult unless you already have several commonly used password that are in a table and you compare it with the HashedPassword column. This will then give you a list of users that contain the same hash values as your precompiled table. This does not mean the user had the same password but does mean your password will hash to the same value and let you sign in. One hashed value can usually be achieved by many different clear-text passwords.
So, to prevent these "dictionary attacks," use a salt value which changes the final hash value for the stored password. Processing these through a dictionary table is useless because all users have a different salt value. You would have to have a pre-compiled dictionary table for each user in the database which gets much more difficult to process. The chances of you getting a match this way is statistically 0% unless you have several years to brute force it. Generally companies require you to change your passwords every 30, 60, or 90 days so a brute force doesn't work in these cases either.
Sending the clear text from the application to the database is dangerous because the trace command can pick that up and be captured by anyone with full access to the database. Having it hashed in the application makes it so only the salt and hashedpassword values are sent to the database and they are no longer clear text at that point. You can look in the table or do whatever trace command you want and you will not be able to see the user's original password anywhere in the database.
The only method for acquiring these passwords is if you use a network sniffer that captures the packets from the client's computer to the web server. And, this is only easy if you're not using an https server and still on a simple unencrypted http server.
February 10, 2012 at 10:59 am
Seems the database would have two columns. One for Salt and one for HashedPassword. Then, you have your application do the hashing with the RANDOM salt. You store both in the database.
Usually we can use the user ID column so that SALT will be unique for each user though more than one have same password.
Reverse engineering a HashedPassword is difficult unless you already have several commonly used password that are in a table and you compare it with the HashedPassword column.
Hashed password can never be reverse engineered (as in the case of encryption). The only possible way is bruteforce attack with the help of rainbow table. Ofcourse, there are other ways like capturing key stroke.
Again this can further be controlled by limiting incorrect password attempts combined with strong hash algorithm.
February 10, 2012 at 11:51 am
Nadrek (2/10/2012)
I would say hashing should be done on the application side at this point, becauseA) SQL Server does not provide us modern hash algorithms (i.e. the SHA-2 family; SHA-512, SHA-256, SHA-384, Whirlpool, the SHA-3 finalists, etc.)
One note here: SQL Server 2012 will add SHA2
February 11, 2012 at 4:00 pm
cengland0 (2/10/2012)
Generally companies require you to change your passwords every 30, 60, or 90 days so a brute force doesn't work in these cases either.
Two points here: The companies which do this so it to their own staff, not to their customers - so customer passwords have to be safe for a much longer time than the internal passwords to which these change frequencies apply. So something good enough to be safe for rather more than several years will be needed, unless the attitude to customer security changes.
Besides, the pasword I use for my most secure stuff is a couple of hundred characters long and I'm not going to create and learn to remember a new one of those every 90 days. I lost my PGP keys way back when because I was persuaded to change my passphrase regularly - and of course couldn't even revoke them, so they are still on public servers somewhere, since only someone who knows them can revoke them.
Sending the clear text from the application to the database is dangerous because the trace command can pick that up and be captured by anyone with full access to the database. Having it hashed in the application makes it so only the salt and hashedpassword values are sent to the database and they are no longer clear text at that point. You can look in the table or do whatever trace command you want and you will not be able to see the user's original password anywhere in the database.
One can perfectly well use encryption (preferably asymmetric, of course) between the database and the app, and do the hashing in the database - or one could if the database were capable of doing the hashing; for long term high security, SQL Server currently isn't.
The idea that passwords have to be passed to the database in unencrypted form is just plain false, and is not a reason for not doing the hashing in the database. The real reason is the SQL Server doesn't have the required hash functions. SQLS 2012 will improve things a little with the addition of SHA2-256 and SHA2-512 to the hashbytes repertoire, but it's still missing the crucial thing: a seriously slow hash; and that means that it can't do the hashing needed for lasting high security.
Of course banks and investment managers and so on providing customers with a web interface don't have a security model that delivers either high security or lasting security - they impose nonsense like a password must be alphanumeric (no symbols) and/or the alpha part of the password is case insensitive and/or the password must be between 6 and 12 characters, they rubbish the vulnerabilities in their security models that are demonstrated by competent security experts (look at how VISA and MasterCard treat the Cambridge University security research team), some of them even hold passwords in clear.
Tom
February 11, 2012 at 9:19 pm
Wow!
Tom, you have my standing invitation to the whisk[e]y bar in Bellevue, whenever you happen to be in this neck of the woods, but I will have to up that.
I am on systems that are on the Intranet and trusted, so I do not have to worry about these things. So reading your post, I found my blind spot, probably one of many.
Much thanks, again, and I am buying, even if your call is a 50 years old single malt.
January 11, 2013 at 2:18 am
mohammed moinudheen (2/9/2012)
No idea about this really. I guessed it and got it wrong 🙂
+1
--
Dineshbabu
Desire to learn new things..
June 9, 2015 at 2:19 pm
Hi Steve Jones,
I appreciate your question, but the answers are a little confusing if we look at the Books Online and other blogs, like this: http://www.mssqltips.com/sqlservertip/3293/add-a-salt-with-the-sql-server-hashbytes-function/
Regards.
Viewing 15 posts - 46 through 60 (of 64 total)
You must be logged in to reply to this topic. Login to reply