How could i create a trigger that at any insertion on my table [users] will change automatically the content of its [password] field to its MD5 hash?
Ps: I do not want this being done at client side.
A:
I may be reading too much into your question, but I think you WANT to do something client-side. Here's why:
The only place a password should be in clear-text is when the user types it. Encrypt it, send it over the wire, store it encrypted, & compare it encrypted. At no point in this chain is the password sniffable.
If you were to encrypt the password when it was written, how would you ever check it when they try to log in later?
n8wrl
2009-09-04 19:35:05
No because the user will input it directly in the database and i do not control haow it will do it. and it is my responsability to hash it
Gregoire
2009-09-04 19:39:45
Why the downvote?
Philip Kelley
2009-09-04 19:40:32
Users are editing tables in your db?? Wow
n8wrl
2009-09-04 19:40:41
If the password could be sniffed anyways (either through the client being infected, or the wire somehow being insecure), then this is still insecure--either a person could do a replay attack with the encrypted password, or use the cleartext password that they earned from a key-logger or other exploit. Client side doesn't buy any real protection--indeed, it actually gives any would-be attacker complete knowledge of your hashing method.
Brisbe42
2009-09-04 19:41:30
@Brisbe42: Good point. This has always been a concern of mine. Might be good topic for new question
n8wrl
2009-09-04 19:44:36
+3
A:
SQL 2005 has HASHBYTES which will do what you want: http://msdn.microsoft.com/en-us/library/ms174415.aspx
Just fire a trigger on UPDATE and INSERT using that function around your password and you have avoided storing plain text passwords. Better: write a stored procedure that does the hash and is used to update passwords. (This avoids the overhead of a trigger, which I avoid like the plague unless nothing else will do.)
Here is an example I just hacked up:
create table TestTrigger2 (
TestTriggerID int not null identity(1,1),
Hashed binary(50),
PasswordProxy nvarchar(50)
)
--select HashBytes('MD5', N'This string')
create trigger HashPass2 on TestTrigger2
instead of insert
as
begin
insert into TestTrigger2 (Hashed)
select HashBytes('MD5', '@!98ABc'+PasswordProxy) from inserted
end
insert into TestTrigger2
(PasswordProxy)
values
('My password' )
select *
from TestTrigger2
When you look at the result of the final query, you will note that PasswordProxy is NULL (it is just there to make a string usable for input) and the Hashed with have the hashed value. The garbage prepended to the PasswordProxy is a salt to avoid the rainbow attack mentioned (it will make your password hashes different from just hashing the base string). Pick something longer and of your own creation.
Godeke
2009-09-04 19:37:57
Make sure you add a salt to the password before hashing it, to avoid rainbow table attacks.
Rick
2009-09-04 19:40:57
A trigger will catch it no matter what (unless someone disables the trigger). Stored procedures might get skipped or avoided.
Philip Kelley
2009-09-04 19:41:28
As explain to n8wrl i am stick with a trigger. So if I undestand well your first response, it will be something like this:Declare trigger user_insert_trigger on [users] for insertas update set [password] = HashBytes ('MD5',[password]) from inserted ?
Gregoire
2009-09-04 19:47:52
One thing that strikes me: you said you didn't want to do this one the client. While I agree with that in part, make sure that the client->server communication is secure (SSL/TLS or similar) lest you encrypt everything on the server and leave the passwords passed in cleartext.
Godeke
2009-09-04 20:01:54
A:
You can use the HashBytes method. Not sure if this is available in sql server versions before 2008. select HashBytes('MD5', 'THIS IS MY PASSWORD')
rdkleine
2009-09-04 19:40:13
+3
A:
You can use an INSTEAD OF INSERT trigger for this. It replaces the default INSERT with something you define, for example, an MD5 hash of one of the inserted columns.
Ronald Wildenberg
2009-09-04 19:41:40