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('$test Pass')"

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('$newP ass') 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]

Re: Checking password with mysql & PASSWORD()

On Sat, 14 Aug 2004 22:12:24 GMT, "John Victor" <wiegeabo@pacbe ll.net> wrote:
[color=blue]
>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('$test Pass')"
>
>and ran mysql_query() using the string. But nothing was returned.[/color]

OK - what's in the table? What's in $testPass? Something must not match up...
[color=blue]
> 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('$newP ass') 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?[/color]

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.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

[John Victor]

Re: Checking password with mysql &amp; PASSWORD()

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:8i7th057kd ovoguqqfbmt4sul jtrssdvaq@4ax.c om...[color=blue]
> On Sat, 14 Aug 2004 22:12:24 GMT, "John Victor" <wiegeabo@pacbe ll.net>[/color]
wrote:[color=blue]
>[color=green]
> >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[/color][/color]
php[color=blue][color=green]
> >document to that saved in the database. I used the string
> >
> >"Select name From users Where password = PASSWORD('$test Pass')"
> >
> >and ran mysql_query() using the string. But nothing was returned.[/color]
>
> OK - what's in the table? What's in $testPass? Something must not match[/color]
up...[color=blue]
>[color=green]
> > 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('$newP ass') Where name =[/color][/color]
'userName1'"[color=blue][color=green]
> >
> >and it works fine. My database is updated properly. So my question is,[/color][/color]
why[color=blue][color=green]
> >can't I find a match using the PASSWORD function, but can still update my
> >table?[/color]
>
> There's no connection between the two queries, so why shouldn't you be[/color]
able to[color=blue]
> update the table by the name field if the password field doesn't match[/color]
your[color=blue]
> expectations?
>
> --
> Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
> <http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool[/color]

[Koncept]

Re: Checking password with mysql &amp; PASSWORD()

In article <c7wTc.4473$4m5 .1455@newssvr27 .news.prodigy.c om>, John
Victor <wiegeabo@pacbe ll.net> wrote:
[color=blue]
> "Select name From users Where password = PASSWORD('$test Pass')"
>
> 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[/color]

Just a shot in the dark here and not the greatest solution, but I was
wondering what would happen if you broke things up into 2 SQL calls or
backticked your column names?

$str2Check = 'foobar';
$s_pass = "SELECT password('${str 2Check}')";
$s_sql = "SELECT `name` FROM `users` WHERE `password` = '${s_pass}' ";

--
Koncept <<
"The snake that cannot shed its skin perishes. So do the spirits who are
prevented from changing their opinions; they cease to be a spirit."
-Nietzsche

[Andy Hassall]

Re: Checking password with mysql &amp; PASSWORD()

On Sun, 15 Aug 2004 04:34:20 GMT, "John Victor" <wiegeabo@pacbe ll.net> wrote:
[color=blue][color=green][color=darkred]
>>>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('$test Pass')"
>>>
>>>and ran mysql_query() using the string. But nothing was returned.[/color]
>>
>> OK - what's in the table? What's in $testPass? Something must not match
>> up...
>>[color=darkred]
>>> 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('$newP ass') Where name =[/color]
>>'userName1' "[color=darkred]
>>>
>>>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?[/color]
>>
>> 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?[/color]
>
>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,[/color]

Which is why I asked for examples of the contents of the variables and the
table...
[color=blue]
>yet I can somehow use the encrypted password to make
>changes to a table.[/color]

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 | 652f9c175d1914f 9 |
+------+------------------+
1 row in set (0.01 sec)

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

[John Victor]

Re: Checking password with mysql &amp; PASSWORD()

> Somewhere in your code your password variables must be holding the wrong[color=blue]
> 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 | 652f9c175d1914f 9 |
> +------+------------------+
> 1 row in set (0.01 sec)
>
> --
> Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
> <http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool[/color]


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('userP ass')

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

Now, I've just noticed an error I made in the update query. It should say
Where password = PASSWORD('userP ass') 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]

Re: Checking password with mysql &amp; PASSWORD()

On Sun, 15 Aug 2004 18:19:04 GMT, "John Victor" <wiegeabo@pacbe ll.net> wrote:
[color=blue][color=green]
>> 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 | 652f9c175d1914f 9 |
>> +------+------------------+
>> 1 row in set (0.01 sec)[/color]
>
>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('userP ass')[/color]

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

PASSWORD('userP ass') 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.
[color=blue]
>But trying to select that field using the password function failed:
>Select name From users Where password = PASSWORD('userP ass')[/color]

You didn't set it to PASSWORD('userP ass'), so again there's no demonstrated
reason why it should return anything.
[color=blue]
>Now, I've just noticed an error I made in the update query. It should say
>Where password = PASSWORD('userP ass') but I left out password =.[/color]

Ah ha.
[color=blue]
> 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.[/color]

"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.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

[Jari Lehtinen]

Re: Checking password with mysql &amp; PASSWORD()

On Sun, 15 Aug 2004 18:19:04 GMT, John Victor <wiegeabo@pacbe ll.net> wrote:
[color=blue][color=green]
>> 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 | 652f9c175d1914f 9 |
>> +------+------------------+
>> 1 row in set (0.01 sec)
>>
>> --
>> Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
>> <http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool[/color]
>
>
> 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('userP ass')
>
> But trying to select that field using the password function failed:
> Select name From users Where password = PASSWORD('userP ass')
>
> Now, I've just noticed an error I made in the update query. It should
> say
> Where password = PASSWORD('userP ass') 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.[/color]

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

http://www.jarilehtinen.net

[Jari Lehtinen]

Re: Checking password with mysql &amp; PASSWORD()

On Sun, 15 Aug 2004 21:42:42 +0300, Jari Lehtinen
<jari@--no-spam--jarilehtinen.ne t> wrote:
[color=blue]
> On Sun, 15 Aug 2004 18:19:04 GMT, John Victor <wiegeabo@pacbe ll.net>
> wrote:
>[color=green][color=darkred]
>>> 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 | 652f9c175d1914f 9 |
>>> +------+------------------+
>>> 1 row in set (0.01 sec)
>>>
>>> --
>>> Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
>>> <http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool[/color]
>>
>>
>> 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('userP ass')
>>
>> But trying to select that field using the password function failed:
>> Select name From users Where password = PASSWORD('userP ass')
>>
>> Now, I've just noticed an error I made in the update query. It should
>> say
>> Where password = PASSWORD('userP ass') 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.[/color]
>
> 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) . "'";[/color]

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

--
Jari Lehtinen

http://www.jarilehtinen.net

[John Victor]

Re: Checking password with mysql &amp; PASSWORD()

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('pword 101')).

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('pword 101')"

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




__________
John

[Andy Hassall]

Re: Checking password with mysql &amp; PASSWORD()

On Mon, 16 Aug 2004 22:26:41 GMT, "John Victor" <wiegeabo@pacbe ll.net> wrote:
[color=blue]
>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('pword 101')).[/color]

At last some example data!

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

mysql> select password('pword 101');
+----------------------+
| password('pword 101') |
+----------------------+
| 266a88fc2c3cb94 9 |
+----------------------+
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?
[color=blue]
>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('pword 101')"
>
>"Update users Set password = PASSWORD('pword 202') Where name = 'John'"[/color]

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.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

[John Victor]

Re: Checking password with mysql &amp; PASSWORD()

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:5be2i0t0sc kuo1a1uirq79e2t n5gt17ogc@4ax.c om...[color=blue]
> On Mon, 16 Aug 2004 22:26:41 GMT, "John Victor" <wiegeabo@pacbe ll.net>[/color]
wrote:[color=blue]
>[color=green]
> >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[/color][/color]
'266a88fc2c'[color=blue][color=green]
> >(which is the result of PASSWORD('pword 101')).[/color]
>
> At last some example data!
>
> '266a88fc2c' is not the result of PASSWORD('pword 101').
>
> mysql> select password('pword 101');
> +----------------------+
> | password('pword 101') |
> +----------------------+
> | 266a88fc2c3cb94 9 |
> +----------------------+
> 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[/color]
value?[color=blue]
>[color=green]
> >The two tasks I now want to accomplish in the test is to simply find a[/color][/color]
name[color=blue][color=green]
> >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[/color][/color]
real[color=blue][color=green]
> >world.)
> >
> >But neither of my queries are working:
> >
> >"Select name From users Where password = PASSWORD('pword 101')"
> >
> >"Update users Set password = PASSWORD('pword 202') Where name = 'John'"[/color]
>
> In an earlier post I demonstrated that this approach does work, provided[/color]
you[color=blue]
> 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[/color]
MySQL[color=blue]
> Server, you should not use it in your own applications. For that purpose,[/color]
use[color=blue]
> MD5() or SHA1() instead. Also see RFC 2195 for more information about[/color]
handling[color=blue]
> 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.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool
>[/color]