Creating a password field in mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
flann
Forum Commoner
Posts: 38
Joined: Tue Aug 23, 2005 10:48 pm

Creating a password field in mysql

Post 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?
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post 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.
flann
Forum Commoner
Posts: 38
Joined: Tue Aug 23, 2005 10:48 pm

Post 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);
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

create a varchar field and use a hash for the password you should.
flann
Forum Commoner
Posts: 38
Joined: Tue Aug 23, 2005 10:48 pm

Post by flann »

thanks yoda, but I'm new at this could you please explain how I would do that? thanks
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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")."')";
flann
Forum Commoner
Posts: 38
Joined: Tue Aug 23, 2005 10:48 pm

Post by flann »

thanks
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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
}
Post Reply