undoing mistakes

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

undoing mistakes

Post 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]
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

that would work, but you have to fix your scripts to support encrypted passwords as well
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

UPDATE table SET password = MD5(password) WHERE NOT REGEXP '[a-z0-9]{32}'

or something similar, maybe?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

ok, well I know there's no MD5, but thanks, I'll prolly leave that on there anyways just to be safe!
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

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