how to encrypt password in MySQL database?

[poolboi]

hi guys,
i would like to ask thing son encryption of data
presently i'm setting up a mysql database to store usernames and password for authentication

is there ways i can encrypt the column passwords even to the database administrator? i know of MD5 but how do i actually go about doing it?

[Atli]

Hi.

There is a simple way to do that by using either the MD5 or SHA1 hashing functions. The essentially do the same thing, but the SHA1 function creates a longer hash, which makes it more secure.

Just do something like this:

INSERT INTO myUserTbl(UserName, Password) 
VALUES('myUserName', SHA1('myPassword'));
 

And then, when it comes to validating the password you can do something like:

SELECT UserID FROM myUserTbl 
WHERE UserName = 'myUserName'
AND Password = SHA1('myPassword');
 

This way, nobody can read any of the passwords, not even with full access to your database.

[poolboi]

Hi.

There is a simple way to do that by using either the MD5 or SHA1 hashing functions. The essentially do the same thing, but the SHA1 function creates a longer hash, which makes it more secure.

Just do something like this:

INSERT INTO myUserTbl(UserName, Password) 
VALUES('myUserName', SHA1('myPassword'));
 

And then, when it comes to validating the password you can do something like:

SELECT UserID FROM myUserTbl 
WHERE UserName = 'myUserName'
AND Password = SHA1('MyPassword');
 

This way, nobody can read any of the passwords, not even with full access to your database.

hi,
hm..with respect to yr validation part
i realise when i printed this SQL statement on mysql client
it returns me an empty set
so i'm not sure if that really works

[Atli]

hi,
hm..with respect to yr validation part
i realise when i printed this SQL statement on mysql client
it returns me an empty set
so i'm not sure if that really works

It does work. I've used this method on several projects myself.

If you are using the exact code I posted tho, the problem may be that I misspelled the second password. It should start with a lowercase 'm'.

I've corrected the typo in my old post.

[poolboi]

It does work. I've used this method on several projects myself.

If you are using the exact code I posted tho, the problem may be that I misspelled the second password. It should start with a lowercase 'm'.

I've corrected the typo in my old post.

opps alright i know the problem
i declare the my length for my password in my password field too short
that's why the verifying ran into problems
thanks anyway atli

[sbmk]

Thanks Atli, it really works.I have tried it.You're just GR8 !!!