Checking password with mysql & PASSWORD()

[John Victor]

In my mysql database, I've stored all the passwords using the PASSWORD()
function. Now I'm running a test and need to compare the password in my php
document to that saved in the database. I used the string

"Select name From users Where password = PASSWORD('$testPass')"

and ran mysql_query() using the string. But nothing was returned. So I
decided to run a test and try to change a password from my php page using
the string

Update users Set password = PASSWORD('$newPass') Where name = 'userName1'"

and it works fine. My database is updated properly. So my question is, why
can't I find a match using the PASSWORD function, but can still update my
table?

[Andy Hassall]

On Sat, 14 Aug 2004 22:12:24 GMT, "John Victor" <wiegeabo@pacbell.net> wrote:

>In my mysql database, I've stored all the passwords using the PASSWORD()
>function. Now I'm running a test and need to compare the password in my php
>document to that saved in the database. I used the string
>
>"Select name From users Where password = PASSWORD('$testPass')"
>
>and ran mysql_query() using the string. But nothing was returned.

OK - what's in the table? What's in $testPass? Something must not match up...

> So I
>decided to run a test and try to change a password from my php page using
>the string
>
>Update users Set password = PASSWORD('$newPass') Where name = 'userName1'"
>
>and it works fine. My database is updated properly. So my question is, why
>can't I find a match using the PASSWORD function, but can still update my
>table?

There's no connection between the two queries, so why shouldn't you be able to
update the table by the name field if the password field doesn't match your
expectations?

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[John Victor]

The fields, and variables, all just hold strings. The problem is that I'm
unable to check the password someone has typed against the encrypted
password in the table, yet I can somehow use the encrypted password to make
changes to a table.




--
John


"Andy Hassall" <andy@andyh.co.uk> wrote in message
news:8i7th057kdovoguqqfbmt4suljtrssdvaq@4ax.com...

> On Sat, 14 Aug 2004 22:12:24 GMT, "John Victor" <wiegeabo@pacbell.net>

wrote:

>

> >In my mysql database, I've stored all the passwords using the PASSWORD()
> >function. Now I'm running a test and need to compare the password in my

php

> >document to that saved in the database. I used the string
> >
> >"Select name From users Where password = PASSWORD('$testPass')"
> >
> >and ran mysql_query() using the string. But nothing was returned.

>
> OK - what's in the table? What's in $testPass? Something must not match

up...

>

> > So I
> >decided to run a test and try to change a password from my php page using
> >the string
> >
> >Update users Set password = PASSWORD('$newPass') Where name =

'userName1'"

> >
> >and it works fine. My database is updated properly. So my question is,

why

> >can't I find a match using the PASSWORD function, but can still update my
> >table?

>
> There's no connection between the two queries, so why shouldn't you be

able to

> update the table by the name field if the password field doesn't match

your

> expectations?
>
> --
> Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
> <http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Andy Hassall]

On Sun, 15 Aug 2004 04:34:20 GMT, "John Victor" <wiegeabo@pacbell.net> wrote:

>>>In my mysql database, I've stored all the passwords using the PASSWORD()
>>>function. Now I'm running a test and need to compare the password in my
>>>php document to that saved in the database. I used the string
>>>
>>>"Select name From users Where password = PASSWORD('$testPass')"
>>>
>>>and ran mysql_query() using the string. But nothing was returned.

>>
>> OK - what's in the table? What's in $testPass? Something must not match
>> up...
>>

>>> So I
>>>decided to run a test and try to change a password from my php page using
>>>the string
>>>
>>>Update users Set password = PASSWORD('$newPass') Where name =

>>'userName1'"

>>>
>>>and it works fine. My database is updated properly. So my question is,
>>>why
>>>can't I find a match using the PASSWORD function, but can still update my
>>>table?

>>
>> There's no connection between the two queries, so why shouldn't you be
>> able to update the table by the name field if the password field doesn't match
>> your expectations?

>
>The fields, and variables, all just hold strings. The problem is that I'm
>unable to check the password someone has typed against the encrypted
>password in the table,

Which is why I asked for examples of the contents of the variables and the
table...

>yet I can somehow use the encrypted password to make
>changes to a table.

Where did you demonstrate this? Your second query simply matched on the name
field, it didn't use the encrypted password anywhere in the WHERE clause.


Somewhere in your code your password variables must be holding the wrong
values, since the approach you are taking is certainly valid, e.g.:

mysql> insert into users values ('andy', password('blah'));
Query OK, 1 row affected (0.01 sec)

mysql> select * from users where password = password('blah');
+------+------------------+
| name | password |
+------+------------------+
| andy | 652f9c175d1914f9 |
+------+------------------+
1 row in set (0.01 sec)

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[John Victor]

> Somewhere in your code your password variables must be holding the wrong

> values, since the approach you are taking is certainly valid, e.g.:
>
> mysql> insert into users values ('andy', password('blah'));
> Query OK, 1 row affected (0.01 sec)
>
> mysql> select * from users where password = password('blah');
> +------+------------------+
> | name | password |
> +------+------------------+
> | andy | 652f9c175d1914f9 |
> +------+------------------+
> 1 row in set (0.01 sec)
>
> --
> Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
> <http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

I've tried something a little different. For clarity I removed the
variables. I was able to change a field using this statment:
Update users Set name = 'John1' Where PASSWORD('userPass')

But trying to select that field using the password function failed:
Select name From users Where password = PASSWORD('userPass')

Now, I've just noticed an error I made in the update query. It should say
Where password = PASSWORD('userPass') but I left out password =. Funny
thing is, when I fix the statement, it no longer works in my php page or as
a query when running mysql from the shell.

This is frying my brain.


--
John

[Andy Hassall]

On Sun, 15 Aug 2004 18:19:04 GMT, "John Victor" <wiegeabo@pacbell.net> wrote:

>> Somewhere in your code your password variables must be holding the wrong
>> values, since the approach you are taking is certainly valid, e.g.:
>>
>> mysql> insert into users values ('andy', password('blah'));
>> Query OK, 1 row affected (0.01 sec)
>>
>> mysql> select * from users where password = password('blah');
>> +------+------------------+
>> | name | password |
>> +------+------------------+
>> | andy | 652f9c175d1914f9 |
>> +------+------------------+
>> 1 row in set (0.01 sec)

>
>I've tried something a little different. For clarity I removed the
>variables. I was able to change a field using this statment:
>Update users Set name = 'John1' Where PASSWORD('userPass')

Why are you changing the name for a user now, I thought you were trying to set
the password?

PASSWORD('userPass') is alway true, so you've now either:

(a) Trashed your table - everyone will have name John1
(b) Ignored an error - because that should have caused a key violation

If (a) then your table's design is wrong, since you seem to be using name as a
key value, so it should be set as a key in the table.

If (b) you're making your life far more difficult than it need be if you're
ignoring errors.

>But trying to select that field using the password function failed:
>Select name From users Where password = PASSWORD('userPass')

You didn't set it to PASSWORD('userPass'), so again there's no demonstrated
reason why it should return anything.

>Now, I've just noticed an error I made in the update query. It should say
>Where password = PASSWORD('userPass') but I left out password =.

Ah ha.

> Funny
>thing is, when I fix the statement, it no longer works in my php page or as
>a query when running mysql from the shell.

"No longer works" in what way?
Again, what's in your table? Show some examples of real data. Show some sample
code. etc.

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Jari Lehtinen]

On Sun, 15 Aug 2004 18:19:04 GMT, John Victor <wiegeabo@pacbell.net> wrote:

>> Somewhere in your code your password variables must be holding the
>> wrong
>> values, since the approach you are taking is certainly valid, e.g.:
>>
>> mysql> insert into users values ('andy', password('blah'));
>> Query OK, 1 row affected (0.01 sec)
>>
>> mysql> select * from users where password = password('blah');
>> +------+------------------+
>> | name | password |
>> +------+------------------+
>> | andy | 652f9c175d1914f9 |
>> +------+------------------+
>> 1 row in set (0.01 sec)
>>
>> --
>> Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
>> <http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

>
>
> I've tried something a little different. For clarity I removed the
> variables. I was able to change a field using this statment:
> Update users Set name = 'John1' Where PASSWORD('userPass')
>
> But trying to select that field using the password function failed:
> Select name From users Where password = PASSWORD('userPass')
>
> Now, I've just noticed an error I made in the update query. It should
> say
> Where password = PASSWORD('userPass') but I left out password =. Funny
> thing is, when I fix the statement, it no longer works in my php page or
> as
> a query when running mysql from the shell.
>
> This is frying my brain.

I suggest that you convert password into a md5 hash and save the hash into
database. When selecting, use md5($password) in query. Such as $query =
"SELECT * FROM users WHERE password = '" . md5($password) . "'";

--
Jari Lehtinen
[url]http://www.jarilehtinen.net[/url]

[Jari Lehtinen]

On Sun, 15 Aug 2004 21:42:42 +0300, Jari Lehtinen
<jari@--no-spam--jarilehtinen.net> wrote:

> On Sun, 15 Aug 2004 18:19:04 GMT, John Victor <wiegeabo@pacbell.net>
> wrote:
>

>>> Somewhere in your code your password variables must be holding the
>>> wrong
>>> values, since the approach you are taking is certainly valid, e.g.:
>>>
>>> mysql> insert into users values ('andy', password('blah'));
>>> Query OK, 1 row affected (0.01 sec)
>>>
>>> mysql> select * from users where password = password('blah');
>>> +------+------------------+
>>> | name | password |
>>> +------+------------------+
>>> | andy | 652f9c175d1914f9 |
>>> +------+------------------+
>>> 1 row in set (0.01 sec)
>>>
>>> --
>>> Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
>>> <http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

>>
>>
>> I've tried something a little different. For clarity I removed the
>> variables. I was able to change a field using this statment:
>> Update users Set name = 'John1' Where PASSWORD('userPass')
>>
>> But trying to select that field using the password function failed:
>> Select name From users Where password = PASSWORD('userPass')
>>
>> Now, I've just noticed an error I made in the update query. It should
>> say
>> Where password = PASSWORD('userPass') but I left out password =. Funny
>> thing is, when I fix the statement, it no longer works in my php page
>> or as
>> a query when running mysql from the shell.
>>
>> This is frying my brain.

>
> I suggest that you convert password into a md5 hash and save the hash
> into database. When selecting, use md5($password) in query. Such as
> $query = "SELECT * FROM users WHERE password = '" . md5($password) . "'";

Forgot to mention. For improved security, add a extra string when hashing,
such as:
md5($password . '398th34ghf73fHG') so it'll be harder to crack.

--
Jari Lehtinen
[url]http://www.jarilehtinen.net[/url]

[John Victor]

The user table I'm testing right now is very simple. It only has two
fields: name and password. Example values would be 'John' and '266a88fc2c'
(which is the result of PASSWORD('pword101')).

The two tasks I now want to accomplish in the test is to simply find a name
using the PASSWORD() function, as well as find a name and change the
password. (This is just testing, it doesn't have to make sense in the real
world.)

But neither of my queries are working:

"Select name From users Where password = PASSWORD('pword101')"

"Update users Set password = PASSWORD('pword202') Where name = 'John'"




__________
John

[Andy Hassall]

On Mon, 16 Aug 2004 22:26:41 GMT, "John Victor" <wiegeabo@pacbell.net> wrote:

>The user table I'm testing right now is very simple. It only has two
>fields: name and password. Example values would be 'John' and '266a88fc2c'
>(which is the result of PASSWORD('pword101')).

At last some example data!

'266a88fc2c' is not the result of PASSWORD('pword101').

mysql> select password('pword101');
+----------------------+
| password('pword101') |
+----------------------+
| 266a88fc2c3cb949 |
+----------------------+
1 row in set (0.00 sec)

This is on MySQL 4.0.18.

Have you chosen a column type that it too short to contain the password value?

>The two tasks I now want to accomplish in the test is to simply find a name
>using the PASSWORD() function, as well as find a name and change the
>password. (This is just testing, it doesn't have to make sense in the real
>world.)
>
>But neither of my queries are working:
>
>"Select name From users Where password = PASSWORD('pword101')"
>
>"Update users Set password = PASSWORD('pword202') Where name = 'John'"

In an earlier post I demonstrated that this approach does work, provided you
use a long enough column.

In any case, looking through the manual to find the length of string that
PASSWORD produces finds the following warning:

"Note: The PASSWORD() function is used by the authentication system in MySQL
Server, you should not use it in your own applications. For that purpose, use
MD5() or SHA1() instead. Also see RFC 2195 for more information about handling
passwords and authentication securely in your application."

So, see the manual for appropriate column lengths for either MD5 or SHA1.

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[John Victor]

Thank you. That was the problem. My password field was too short. I
thought I was doing the query correctly. I'm so glad it was just a simple
database problem, and I wasn't losing my mind. And thanks for the PASSWORD
note. I'm going to use either md5 or sha1 instead.


John


"Andy Hassall" <andy@andyh.co.uk> wrote in message
news:5be2i0t0sckuo1a1uirq79e2tn5gt17ogc@4ax.com...

> On Mon, 16 Aug 2004 22:26:41 GMT, "John Victor" <wiegeabo@pacbell.net>

wrote:

>

> >The user table I'm testing right now is very simple. It only has two
> >fields: name and password. Example values would be 'John' and

'266a88fc2c'

> >(which is the result of PASSWORD('pword101')).

>
> At last some example data!
>
> '266a88fc2c' is not the result of PASSWORD('pword101').
>
> mysql> select password('pword101');
> +----------------------+
> | password('pword101') |
> +----------------------+
> | 266a88fc2c3cb949 |
> +----------------------+
> 1 row in set (0.00 sec)
>
> This is on MySQL 4.0.18.
>
> Have you chosen a column type that it too short to contain the password

value?

>

> >The two tasks I now want to accomplish in the test is to simply find a

name

> >using the PASSWORD() function, as well as find a name and change the
> >password. (This is just testing, it doesn't have to make sense in the

real

> >world.)
> >
> >But neither of my queries are working:
> >
> >"Select name From users Where password = PASSWORD('pword101')"
> >
> >"Update users Set password = PASSWORD('pword202') Where name = 'John'"

>
> In an earlier post I demonstrated that this approach does work, provided

you

> use a long enough column.
>
> In any case, looking through the manual to find the length of string that
> PASSWORD produces finds the following warning:
>
> "Note: The PASSWORD() function is used by the authentication system in

MySQL

> Server, you should not use it in your own applications. For that purpose,

use

> MD5() or SHA1() instead. Also see RFC 2195 for more information about

handling

> passwords and authentication securely in your application."
>
> So, see the manual for appropriate column lengths for either MD5 or SHA1.
>
> --
> Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
> <http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
>