Page 1 of 1

Creating a password field in mysql

Posted: Wed Nov 16, 2005 3:55 pm
by flann
How do you create a password field in mysql that is encrypted like the one on the user table in the mysql database that controls access to the DBMS?

Posted: Wed Nov 16, 2005 4:04 pm
by Buddha443556
PASSWORD() or OLD_PASSWORD() depend on the version of MySQL you're using or were using ... whatever the case maybe. However, it's not recommended that you use these functions as they are for MySQL internal use ... that's in the manual somewhere.

Posted: Wed Nov 16, 2005 4:10 pm
by flann
so is it safe to create a user table for my database and not encrypt the password field? It really doesn't matter because the information doesn't need to be secure, but I'm trying to figure out the best way to create this. If I did it that way would I do it like this.

Code: Select all

alter table users add OLD_PASSWORD(password) varchar(15);

Posted: Wed Nov 16, 2005 5:00 pm
by Burrito
create a varchar field and use a hash for the password you should.

Posted: Wed Nov 16, 2005 5:12 pm
by flann
thanks yoda, but I'm new at this could you please explain how I would do that? thanks

Posted: Wed Nov 16, 2005 5:17 pm
by Burrito
three options you have.

md5(), sha1(), or feyd's sha256 (search code snippets for that you must).

ex usage:

Code: Select all

$query = "insert into myTable (username,password) values ('burrito','".md5("taco")."')";

Posted: Wed Nov 16, 2005 5:27 pm
by flann
thanks

Posted: Wed Nov 16, 2005 5:29 pm
by twigletmac
Note that you could also use MySQL's MD5() function:

Code: Select all

$query = "insert into myTable (username,password) values ('burrito', md5('taco'))";
but that this is *not* the same as PHP's md5() function.

Mac

Posted: Wed Nov 16, 2005 5:43 pm
by hawleyjr
You really should add salt to your encrypted password...

Code: Select all

$username = 'hawleyjr';
$password = 'abc123';
CREATE PASSWORD

Code: Select all

define('HASH_LEN',20);

//CREATE SALT
$salt = substr(sha1(time()),HASH_LEN);

//create password
$password = $salt . sha1( $salt . $password);

//QUERY:
	"INSERT INTO myTable set username = '$username',pass = '$password'"

VALIDATE PASSWORD:

Code: Select all

//QUERY:
	"SELECT pass FROM myTable where username = '$username'"

	//FROM QUERY:
	$passFromDB = 'ccb8e9d800e210ea45da40c25e653e9c4c08d504997bf3d05f14d0fddcbb';

//GET SALT
$salt = substr($passFromDB ,HASH_LEN);

//VALIDATE PASSWORD
if($salt . sha1( $salt . $password) == $passFromDB){
	//VALID USERNAME/PASS
}else{
	//INVALID USERNAME/PASS
}