0

How can I display all the rows in a table where password is not in MD5 format?

I have a table in which some passwords are entered in MD5 format and some are not !!! I want to display all the rows where password is not in MD5 format how can i do that?

I know that the data was inserted with:

 MD5('".$password."')

How can I retrieve the rows where password is not in MD5 format?

tadman
  • 208,517
  • 23
  • 234
  • 262
slash
  • 69
  • 1
  • 8
  • MD5 is as secure as plaintext these days... Use something stronger (`password_hash`, `password_verify`) – tkausl Jun 30 '16 at 04:53
  • do you have any limitation in password? like max 15. – Dave Jun 30 '16 at 04:53
  • 1
    Have you tried with the length? MD5 hashed fields will have a fixed length. – Shubhamoy Jun 30 '16 at 04:53
  • Don't use md5 for password hashing, it's not secure. See these Q/A, [http://stackoverflow.com/q/6774345/5517143](http://stackoverflow.com/q/6774345/5517143) and [http://security.stackexchange.com/q/19906](http://security.stackexchange.com/q/19906). Always perform [salted password hashing](https://crackstation.net/hashing-security.htm) on raw password before inserting it into the table. – Rajdeep Paul Jun 30 '16 at 04:55
  • I hope you're updating to something better like [`password_hash`](http://php.net/manual/en/function.password-hash.php) format. Pro tip: You can reset a user's password when they login to use the updated algorithm if you can verify their existing hash matches. – tadman Jun 30 '16 at 06:36

1 Answers1

2

Try this query:

SELECT * FROM `table` WHERE `password` NOT REGEXP '^[a-f0-9]{32}$'

Where table is your table name.

Sergey Khalitov
  • 987
  • 7
  • 17