MySQL Check if username and password matches in Database

Question

I have a form which has a textbox with the name attribute username and another one with the name attribute password. I also have a database with columns called user and pass. When my users signed up it added the username to the user column and password to the pass column.

How would I make a MySQL query to check if the form submitted the right username and password and then if it did have a branch to let me input the code for if it succeeded?

I really need some code, this bit isn't going well I know it should be something like SELECT * FROM table WHERE username == $username AND... but then I'm stuck because I have an MD5 password in the database and that first bit is probably wrong. Please help. :)

Thanks

Solution 1

1.) Storage of database passwords Use some kind of hash with a salt and then alter the hash, obfuscate it, for example add a distinct value for each byte. That way your passwords a super secured against dictionary attacks and rainbow tables.

2.) To check if the password matches, create your hash for the password the user put in. Then perform a query against the database for the username and just check if the two password hashes are identical. If they are, give the user an authentication token.

The query should then look like this:

select hashedPassword from users where username=?

Then compare the password to the input.

Further questions?

Solution 2

//set vars
$user = $_POST['user'];
$pass = md5($_POST['pass']);

if ($user&&$pass) 
{
//connect to db
$connect = mysql_connect("$server","$username","$password") or die("not connecting");
mysql_select_db("users") or die("no db :'(");
$query = mysql_query("SELECT * FROM $tablename WHERE username='$user'");

$numrows = mysql_num_rows($query);


if ($numrows!=0)
{
//while loop
  while ($row = mysql_fetch_assoc($query))
  {
    $dbusername = $row['username'];
    $dbpassword = $row['password'];
  }
  else
      die("incorrect username/password!");
}
else
  echo "user does not exist!";
} 
else
    die("please enter a username and password!");

Solution 3

Instead of selecting all the columns in count count(*) you can limit count for one column count(UserName).

You can limit the whole search to one row by using Limit 0,1

SELECT COUNT(UserName)
  FROM TableName
 WHERE UserName = 'User' AND
       Password = 'Pass'
 LIMIT 0, 1