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? :P

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..