SQL query for salted passwords

veritas · 07-30-2007, 11:17 PM

Hello,

I am making a login script (php) that checks a given username/pass against a database, which has passwords stored with a salt. I have it working, but I would like to know if I am doing the query and password checking the most efficient and secure way. It seems kinda long to me

Code:

$query = mysql_query('SELECT uname, upass, data FROM users WHERE uname = "'.mysql_real_escape_string($_POST['username']).'"');
if(mysql_num_rows($query) == 1) // if username exists, move on to verify the pass
{
  $row = mysql_fetch_assoc($query);
  if(saltShaker(mysql_real_escape_string($_POST['pass']),$row['upass']) == $row['upass'])
  {

and my salt function

Code:

function saltShaker($plainText, $salt = null)
{
  if ($salt === null)
  {
    $salt = substr(md5(uniqid(rand(), true)),0,28); //28 character salt
  }
  else
  {
    $salt = substr($salt, 0, 28);
  }
  
  return $salt . sha1(md5($salt . $plainText)); 
}

graemef · 08-01-2007, 06:00 PM

I see how you are encrypting the passwords and storing the salt on the database but in my view that doesn't cover all the security bases because the password is still passed into the system in plain text. The way I do it is as follows:

onsumbit I use javascript to encrypt the password
The encryption is a plain md5 encrypt followed by a md5 encrypt with a salt of the timestamp
on the system the password is stored as a plain md5 encrypt, so the comparison needs to be a second md5 encrypt with the same timestamp.
The timestamp can be passed around using a hidden field or session variable, I actually use a combination of both, checking that the hidden field hasn't changed value.

My point would be don't worry too much about doing it efficiently, focus on secure.

jlinkels · 08-01-2007, 06:12 PM

What I did is first calculating the stored password, and put that password in the query, hence:

Code:

passwd = md5(upasswd);
query = "SELECT userid, passwd FROM users WHERE userid=\"uname\" AND passwd = \"passwd\"

(Not syntactically correct but you'll get the idea. You would use SaltShaker instead of MD5)

When I perform the query I count the number of rows. If none is returned, no user/pwd combination exist.

I am not sure what your system load is, but it might not be relevant at all to improve the efficiency of this function. I am surprised what I can do in PHP (and I mean loops inside loops which perform queries!) without any speed penalty yet.

jlinkels

veritas · 08-01-2007, 09:59 PM

jlinkels, thanks. I knew there was something long and winding about my code, thought I cant decide if I want to keep the if statements to identify specific errors in the query

Graemef, thanks for bringing up the point about transmitting the password in plaintext from the user to the server. This is something I never really considered. I use the salt to protect the password hashes from dictionary brute forcing if the DB was somehow compromised (also prevents a hash appearing twice in the DB), and I'm sure the added length/complexity increases the time required to brute force the hash

If i understand your method completely (i'm still kinda new to cryptography and php), you store an md5 hash of the plaintext on the server for comparison, and then when a user logs in, their computer hashes the plaintext, then hashes it again with the salt added, then sends it for comparing. If all that is correct, I think your method has room for improvement as well, since you are storing the md5s in a database without a salt. But thank you for the tip. I will rework my method to encrypt what is POSTed

Also, since your salt is stored by a hidden field, wouldnt someone be able to get the salt and use it when brute forcing whatever was encrypted before POST (assuming they correctly guess where the salt was applied to the plaintext).

Just thinking out loud here, cryptography is my new fascination right now

graemef · 08-02-2007, 01:52 AM

yes they would but that is an inherent problem with passwords. At some point in the process a user enters a password that could be cracked by a third party. What the developer can do is to reduce the opportunity available to the third party to guess the password. A three strikes and you're out is one approach where the user is locked out if they fail to log in on three attempts, and remember in a PHP application the user shouldn't be identified by the user name that they offered but by the IP address being used.

The point of the salt is to make it difficult for anyone sniffing the network to crack the encrypted password. A salt that changes each time that the user attempts to login reduces the effectiveness of encrypted dictionaries. However, given sufficient time it can be cracked, so it is important for the user to change their passwords on a regular basis.

Security is a balancing act between the value of the data being protected and the cost of the security being implemented. Eye retina scans are all well and good but can you justify the expense?

chrism01 · 08-02-2007, 05:09 AM

graemef: I wouldn't normally rely on IP address. In most companies & at home, most people are on non-static IP addresses.
Apologies if I've misunderstood something.

graemef · 08-02-2007, 05:31 PM

If you're looking to avoid a simple brute force attack on your system then using the IP address would be sufficient since the attack would be the with the same IP address, however that could block valid users, which is why the block should be time limited. Additionally, keeping track of the IP address is important to avoid session hijacking where a third party sends the PHP session details of a logged in user but to a different IP address.

veritas · 08-02-2007, 08:43 PM

Graemef, would you agree that salting hashes in a database is a good first step for database security? I originally got the idea from the whole piratebay incident, where someone stole their database but couldn't do anything with the hashes since they were "very encrypted" which I assume means at least salting

graemef · 08-02-2007, 09:49 PM

Yes I would. Looking at the situation where someone has access to the encrypted data, be it a database or a flat file then the approach to get the unencrypted passwords would be to compare the encrypted password with a list of encrypted words. It's easy to encrypt a dictionary of words and common alterations and then do a comparison.

Adding a salt to the encryption would make it much harder, because the cracker would need to know the salt and where it was placed in relation to the password. One thought would be do you have a single system-wide salt used by all the passwords or a salt unique for each individual password?

Having a system-wide salt makes it easier to implement but if the cracker has access to the salt then it is not really any more secure. Having a unique salt is harder to implement because the salt needs to be stored somewhere and if the cracker has access to the password list they probably have access to the salt details as well. The big advantage with the unique salt, as you have in your original post is that for each password an encrypted dictionary would need to be generated, which is going to be quite a bit of processing power. One thing to keep in mind is that if a third party has managed to get access to the password data, then they have most likely also managed to access the program's source code and so they know how the security functions work.

As an aside a simple way of salting the stored password would be to use the username.