mysql: how to update field to "password"

newbie needs to figure out how to update a field to "password"

[makimoto]

hello,

i went and created a mysql table with usernames and passwords (all pre-defined), but forgot to use the password() function with the INSERT command when creating the "customers_password" mysql field.

is there an UPDATE mysql command that would allow me to change the values of the "customers_password" field to a hash of the current value? i have tried this:

UPDATE `customers` SET `customers_password` = PASSWORD( "*") WHERE `customers_num` ="*";

but this did not work, obviously, for security reasons. i thought a wild card would just change anything, but it did not work. the above works when i poppulate the * values properly of course, but this would be super time consuming for the amount of users i have in this table (300+).

thanks in advance for any advice,

[Nutter]

Before I start, back up first. I'm not positive this is correct.

I think it should be:

UPDATE table SET password_field = PASSWORD(password_field)

You can leave off the WHERE clause to have it do everything.

[jamie]

hi makimoto,

if you have stored the clear text versions of the passwords in the database already, you could make a simple php page which selects the clear text passwords

SELECT customers_num, password
FROM table

and then loops through the results to spit out 300 update commands

while ($row = mysql_fetch_array($result)){
echo 'UPDATE table set password=password(' . $row['password'] . ') WHERE customers_num = ' . $row['customers_num'] . ';';
}

when you view the page, it generates a long list of update commands which you can then paste into phpMyAdmin to update the entire table at once.

that's how i'd do it anyway. there's code missing, so the above won't work verbatim, but should give you an idea?

[makimoto]

thanks Nutter!
that worked like a charm!

[jamie]

there's always a simpler way to do it ;-)

lol

[makimoto]

thanks for your reply jamie, but removing the WHERE clause accomplished what i required.
cheers,

[Nutter]

Here's a problem I had to solve. To query for username you can use

WHERE (user_name='".$username_entered."' AND user_password='".md5($password_entered)."')