Page 1 of 1
undoing mistakes
Posted: Thu Jun 10, 2004 9:42 am
by magicrobotmonkey
Let's say someone designed a database and left users passwords plaintext in there. Someone else comes along and wants to at least md5 them. Is there a way this person could do it with a SQL statement as opposed to running a script to do it? I'm using MySQL. I was thinking something like
Code: Select all
UPDATE table
SET password = MD5(password)
[/quote]
Posted: Thu Jun 10, 2004 9:46 am
by Weirdan
that would work, but you have to fix your scripts to support encrypted passwords as well
Posted: Thu Jun 10, 2004 9:56 am
by feyd
UPDATE table SET password = MD5(password) WHERE NOT REGEXP '[a-z0-9]{32}'
or something similar, maybe?
Posted: Thu Jun 10, 2004 10:07 am
by Weirdan
ehh? Is there any reason to restrict nothing to match your regexp?

(I mean it has syntax error).
magicrobot wants to update the whole table, so there's no need for WHERE clause.
Posted: Thu Jun 10, 2004 10:18 am
by magicrobotmonkey
yea, ok, thanks guys! I really just want to know if MYSQL could self refer like that. Another case I could use help on is I'd like to convert from a TIMESTAMP to a DATE. The Timestamp is like YYYYMMDDHHMMSS anbd I want just date. Is there a way to do it without creating another column? That is, if I just change the column type to DATE will it be alright?
Posted: Thu Jun 10, 2004 10:18 am
by feyd
oops
UPDATE table SET password = MD5(password) WHERE password NOT REGEXP '^[a-z0-9]{32}$'
more like that, then.
that should run through a table and encrypt every password that isn't already an MD5, or at least looks like one.
Posted: Thu Jun 10, 2004 10:19 am
by magicrobotmonkey
ok, well I know there's no MD5, but thanks, I'll prolly leave that on there anyways just to be safe!
Posted: Thu Jun 10, 2004 5:15 pm
by d3ad1ysp0rk
feyd wrote:oops
UPDATE table SET password = MD5(password) WHERE password NOT REGEXP '^[a-z0-9]{32}$'
more like that, then.
that should run through a table and encrypt every password that isn't already an MD5, or at least looks like one.
and what if my password is 32 characters long and alphanumeric?

Posted: Thu Jun 10, 2004 8:40 pm
by feyd
on the extreme chance someone selects a all hex 32 character lowercase string as a password, put in a note that if their password suddenly does not work, to contact you.. or go to x.php to be given a password reset..