How I will make every password in my user table encrypted(md5()) except one particular row using a single query?
+6
A:
UPDATE table SET Password = MD5(Password)
I will say though that MD5 isn't a very good level of encryption and you should consider something stronger such as ENCRYPT with a custom salt. Read about it here
EDIT: Looks like the original question changed. Here's the altered query to accomodate
UPDATE table SET Password = MD5(Password) WHERE ID!=[specified index]
EDIT: Worth noting
jerebear
2009-04-01 05:41:45
It is fine except that it's been hacked http://www.gearfuse.com/md5-algorithm-hacked-by-playstation-3-cluster/
jerebear
2009-04-01 06:14:03
What if the password is encrypted with md5() with salt? How about double salting? Still hack-able?
bLee
2009-04-01 08:13:17
This doesn't seem to answer the OP's question, as it hashes *every* row, and the OP wants to hash every row *except one*.
Ben
2009-04-01 08:17:46
Ben: the OP edited his question later to add this information.
unbeknown
2009-04-01 08:19:08
The "hacked" link has absolutely nothing to do with password verification. I wish people would quit calling MD5 "broken" when they don't understand anything about the actual vulnerability.
Chad Birch
2009-04-01 15:50:27
MD5 in MySQL doesn't allow a salt (or double salt for that matter)
jerebear
2009-04-01 19:54:52
Sure it does, you just concatenate the salt string(s) onto the front/end of the password. That's what salting md5 is.
Chad Birch
2009-04-02 18:37:32
Well...that's just clever...never thought about that one.
jerebear
2009-04-02 19:03:00
+1
A:
Edited in response to edit in OP.
UPDATE userTable
SET password = MD5(password)
WHERE NOT (<criteria to identify row to exclude>)
lc
2009-04-01 05:42:52
+2
A:
Concerning you edit: do you have an ID or username that identifies this row?
UPDATE mytable
SET password = MD5(password)
WHERE id <> 123
unbeknown
2009-04-01 08:13:44
A:
Hash Functions in MySQL
There are a lot more hash functions than MD5 to use for storing passwords in you MySQL database.
You can find a list of them on MySQL :: 11.10.2. Encryption and Compression Functions.
Save Password (hash):
UPDATE users SET password = SHA('secret_password') WHERE ....;
Check Password:
SELECT COUNT(*) FROM users WHERE name = 'username' && password = SHA('typed_password');
If the result is > 0, the user provided the correct password.
furtelwart
2009-04-01 08:23:17
A:
When hashing passwords, do not forget to salt them, so that same passwords do not yield same hashes:
SET @salt := CONV(FLOOR(RAND() * 0x100000000), 10, 16)
UPDATE passwords
SET password = CONCAT(@salt, SHA(CONCAT(@salt, @typed_password)))
SELECT 1
FROM passwords
WHERE SHA(CONCAT(SUBSTRING(password, 1, 8), @typed_password)) = SUBSTRING(password, 9, 40)
Quassnoi
2009-04-01 15:58:34