Luke Sheppard's blog about information security, web development, and hacking 

Home Blog SQL Queries for Plaintext Passwords

SQL Queries for Plaintext Passwords

 

A cleartext password shown on a mailing label. From the US Census Business help website.

If you find yourself in a database that stores passwords in plaintext, this handful of SQL queries might help you get a picture of how weak the passwords are in general, as well as how many duplicates there are.

 

SELECT count(*) FROM UsersTable WHERE Password LIKE Username
SELECT count(*) FROM UsersTable WHERE Password = 'password'
SELECT count(*) FROM UsersTable WHERE Password = '*******'

SELECT count(*) FROM UsersTable WHERE len(Password) = 0

SELECT count(DISTINCT U1.Username) FROM UsersTable U1,
UsersTable U2 WHERE U1.Password = U2.Password AND U1.Username != U2.Username

 

That last query will tell you how many users have the same password as one or more other users.

This is all an accident waiting to happen. What you should be doing is storing all passwords as at least a SHA-256 hash with a random salt.

 
 Share on Facebook Share on Twitter Share on Reddit Share on LinkedIn