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

Just a tip. Don't store passwords in clear text in the database, it is insecure. Use some kind of hash. — JohnFx, Mar 12 '11 at 20:53
Can you show us what code you've written for this task so far? — Charles, Mar 12 '11 at 21:05

score 10 · Answer 1 · answered Dec 30 '11 at 21:37

10

//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!");

answered Dec 30 '11 at 21:37

dave

101
1
2

2

For anyone who comes across this please do not use md5 when storing passwords. – Mgamerz Oct 15 '14 at 21:41
You wouldn't need to select all columns from the table, since you only need the password. Furthermore, in this query only records with matching usernames are returned, then he compares the usernames again. – LostMikely Feb 05 '16 at 01:34
1

Now 2017, this is old code. Do not use. Go for `mysqli` instead. – CousinCocaine Feb 26 '17 at 11:10

score 9 · Answer 2 · answered Feb 19 '13 at 11:21

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

score 8 · Accepted Answer · answered Mar 12 '11 at 21:07

8

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?

answered Mar 12 '11 at 21:07

Falcon

3,150
2
24
35

3

Dude, just check his recent questions. I'm afraid that only full and complete code will help :) – Your Common Sense Mar 12 '11 at 21:12
Well, I gave him something to dive into. I'll help and further explain stuff to him, if he asks. – Falcon Mar 12 '11 at 21:18
0_0 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 :) – Will Evans Mar 12 '11 at 21:45

MySQL Check if username and password matches in Database

3 Answers3