PHP and MySQL PASSWORD() function

[Anon]

Hi there,

I am trying to make a PHP script to compare an entered password with one stores in a database which is also encrypted using the PASSWORD() function. When using the command "SELECT PASSWORD('a_password')" it works fine from the mysql command interface but how do I do this from a PHP script?

I tried everything I could think off but nothing works so far.

Any sugestions?

Thanx in advance, Eric.

[Anon]

SELECT * FROM your_table where user='$user' and password=password('$password');

$user and $password are the plain text versions. This SELECT will return a result if it finds a matching user and password.

---John Holmes...

[linko82]

I'm doing pretty much the same thing. I have a small table, 2 columns one with the passwords, encrypted, and the other with user names. For some reason when I run: select id from members where password = password(testing); it returns an empty set.

[linko82]

I didn't really give enough detail before, here is exactly what I'm doing, I really need help getting this to work.

mysql> INSERT INTO Unamepassword VALUES (0,password('testing'), 'dmuhsin');
Query OK, 1 row affected (0.00 sec)

mysql> select * from unamepassword;
+----+--------------+----------+
| id | password | username |
+----+--------------+----------+
| 1 | 0b89d2383b82 | dmuhsin |
+----+--------------+----------+
1 row in set (0.01 sec)

mysql> select id from unamepassword where password = password('testing');
Empty set (0.00 sec)

mysql>

[hand]

<?php
mysql_connect("localhost", "", "") or die("could not connect"); 
mysql_select_db("yourDb");

$QueryString = "SELECT * FROM your_table where user='$user' and password=password('$password')";
$Query = mysql_query($QueryString);

$RecsFound = mysql_num_rows();

if ($RecsFound > 0) {
	print "Authentication o.k.<br>\n";
} else {
	print "Sorry, ACCESS DENIED<br>\n";
}
?>

[linko82]

that's pretty much what I have line for line, but I tried it anyway, it still returns an empty set. Maybe I'll just redo the entire thing from scratch.

[hand]

compare passwords and display them:

<?php
mysql_connect("localhost", "", "") or die("could not connect"); 
mysql_select_db("yourDb");

$QueryString = "SELECT *,password('$password') as hand FROM your_table where user='$user'";
$Query = mysql_query($QueryString);

print "$QueryString<br>\n";

while ($row = mysql_fetch_array($Query)) {
   print "user: $row[user] db-pwd: $row[password] / entered pwd $row[hand]<br>\n";
   if ($row[password] != $row[hand]) {
      "You entered an invalid password<br>\n"
   }
}
?>

Please post the output from this script

[linko82]

I got an error, it printed the first part but the loop doesn't work correctly.

SELECT *,password('testing') as hand FROM your_table where user='dmuhsin'

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in s:\inetpub\wwwroot\authentication testing\TMPv18zd11p0r.php on line 11

[hand]

Are you sure that in your Database there exists a table called "your_table" ?
You have to customize the script to your configuration!

<?php
mysql_connect("localhost", "", "") or die("could not connect");
mysql_select_db("yourDb");  // Please customize databasename

$QueryString = "SELECT *,password('$password') as hand FROM your_table where user='$user'";
// Please customize tablename "your_table"
// Please customize fieldname "user"

$Query = mysql_query($QueryString)
	or print "&gt;&gt;&gt; MySQL-Error: ".mysql_errno()." -&gt; ".mysql_error()."<br>\n";

print "$QueryString<br>\n";

while ($row = mysql_fetch_array($Query)) {
	// Please customize fieldname "user"
	// Please customize fieldname "password"
   print "user: $row[user] db-pwd: $row[password] / entered pwd $row[hand]<br>\n";
   if ($row[password] != $row[hand]) {
      "You entered an invalid password<br>\n"
   }
}
?>

At first please take this script. Let it run. I added an or-statement. This statement will help you find the errors.

[linko82]

here's the mysql command line data if that helps any.

+----+------------ -----+---------------+---------------------------------+
| id | password | username | hand |
+----+---------------- --+------------+---------------------------------+
| 1 | 0b89d2383b82 | dmuhsin | 0b89d2383b82c0b7 |
+----+--------------+-----------------+---------------------------------+
1 row in set (0.00 sec)

the passwords are different

[linko82]

I feel like I'm getting somehwhere now, thank you for all of your help. Here's the output from the php page.

SELECT *,password('testing') as hand FROM users where username='dmuhsin'
user: dmuhsin db-pwd: 0b89d2383b82 / entered pwd 0b89d2383b82c0b7
You entered an invalid password

[hand]

I think your password character field definition is too short [char(12) oer varchar(12)? instead of (16)]. Please post the result of describe table_name:

mysql command line>DESCRIBE users;

[linko82]

mysql> describe users;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| password | varchar(12) | | MUL | | |
| username | varchar(12) | | MUL | | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.14 sec)

[hand]

You have
either to change the password field from varchar(12) to varchar(16), because of the length of the generated password hash
or
compare pwds with following querie:

$QueryString = "SELECT * FROM users where username='$user' and password=left(password('$password'),12)";

Good luck

[linko82]

thanks a lot. that fixed it, I would have never figured that out on my own.