PHP - Validating Password with MD5 and MySQL

Question

I have a registration page which hashes passwords and stores it in MySQL. I am having an issue upon login where PHP is not recognizing the username and password. I have searched for answers, and tried to implement the many examples but none have worked. Below is the code:

?php
session_start();
if(isset($_POST['insert'])){
    // username and password sent from form

    $hostname = "localhost";
    $username = "root";
    $password = "Passw0rk1";
    $databaseName = "change_management";

     $connect = mysqli_connect($hostname, $username, $password, $databaseName);

     $user = $_POST['user'];
     $passcode = $_POST['passcode'];


     $sql = "SELECT * FROM admin WHERE username = '$user' and passcode ='".md5($passcode)."'";
     $result = mysqli_query($connect,$sql);
     $row = mysqli_fetch_array($result,MYSQLI_ASSOC);

     $count = mysqli_num_rows($result);

     // If result match $myusername and $mypassword, table row must be 1 row

     if($count ==0) {
        echo "Invalid Credentials";
        }else {

I know the credentials I am inputting are present in the SQL but I am getting 'Invalid Credentials' echoed on the screen.

Here is the registration page incase there is an issue here:

?php 
if(isset($_POST['insert']))
{


    $hostname = "localhost";
    $username = "root";
    $password = "Passw0rk1";
    $databaseName = "change_management";


    $user = $_POST['user'];
    $passcode = $_POST['passcode'];

     $connect = mysqli_connect($hostname, $username, $password, $databaseName);

     $sql = "INSERT INTO `admin` (`username`, `passcode`) VALUES('$user', '".md5('$passcode')."')";

     $result = mysqli_query($connect,$sql);

    if($result)
    {
     echo "Added successfully"; 
    }

    else{
        echo $connect->error;
    }
}
?>

***You really shouldn't use [MD5 password hashes](http://security.stackexchange.com/questions/19906/is-md5-considered-insecure)*** and you really should use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. Make sure you [don't escape passwords](http://stackoverflow.com/q/36628418/1011527) or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, Feb 23 '17 at 12:46
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Feb 23 '17 at 12:47
If you used PHP's methods and prepared statements (both mentioned above) you would never have to worry about these simple typos. — Jay Blanchard, Feb 23 '17 at 12:47
Great moment to learn debugging with var_dump.. `$sql = "SELECT * FROM admin WHERE username = '$user' and passcode ='".md5($passcode)."'"; var_dump($sql);`... en copy paste SQL in your database administration program like PHPMyAdmin/SQLyog and see if you get any records or a error — Raymond Nijland, Feb 23 '17 at 12:54
Add error reporting to the top of your file(s) right after your opening ` — Jay Blanchard, Feb 23 '17 at 13:15
Please post the markup for the form you're submitting to run this script. — Jay Blanchard, Feb 23 '17 at 13:15

DB93 · Accepted Answer · 2017-02-23T13:27:27.880

6

Your code is not secure consider using php's password_hash() and password_verify() function and prepared statements!

Maybe change this line.

 $sql = "SELECT * FROM admin WHERE username = '$user' and passcode ='".md5('passcode')."'";

To

$sql = "SELECT * FROM admin WHERE username = '" . $user . "' and passcode ='".md5($passcode)."'";

In your registration page the line

 $sql = "INSERT INTO `admin` (`username`, `passcode`) VALUES('$user', '".md5('$passcode')."')";

Should be

 $sql = "INSERT INTO `admin` (`username`, `passcode`) VALUES('" . $user . "', '".md5($passcode)."')";

But the md5 is not secure and you should consider using password_hash as said in a comment.

edited Feb 23 '17 at 13:27

answered Feb 23 '17 at 12:46

DB93

610
2
5
16

still the same result. 'Invalid Credentials'. @DB93 – Stephen King Feb 23 '17 at 12:48
Then the username and password combination is invalid and no row is returned from the database. – DB93 Feb 23 '17 at 12:50
I changed the to query so it is now adding the user variable but be careful the user variable is not escaped and could make sql injections happen. – DB93 Feb 23 '17 at 12:55
again, still the same result. cant help but feel like its something so simple we are all overlooking it – Stephen King Feb 23 '17 at 12:58
@S.King can check your credentials!! – sandip kakade Feb 23 '17 at 13:00
ive spent 3 hours checking them, I am pretty sure they are correct – Stephen King Feb 23 '17 at 13:01
@S.King Update your insert query I the post answer – sandip kakade Feb 23 '17 at 13:03
1

@DB93 FIXED! Problem was with the register page. Needed a gifted pair of fresh eyes! Thank you for your help mate, big thumbs up! – Stephen King Feb 23 '17 at 13:19

score 2 · Answer 2 · answered Feb 23 '17 at 12:46

2

change

md5('passcode')

to

md5($passcode)

answered Feb 23 '17 at 12:46

Danyal Sandeelo

12,196
10
47
78

still the same result, 'Invalid Credentials' @Danyal Sandeelo – Stephen King Feb 23 '17 at 12:48
how do you save the password? you are using md5 of mysql right? – Danyal Sandeelo Feb 23 '17 at 13:32

score 1 · Answer 3 · edited Mar 17 '17 at 13:14

Here is the proper method to deal with passwords using MySQLi, prepared statements and PHP's password methods (You must store the password in the database with password_hash() - read more here):

<?php
session_start();
if(isset($_POST['insert'])){
    // username and password sent from form

    $hostname = "localhost";
    $username = "root";
    $password = "Passw0rk1";
    $databaseName = "change_management";

    $connect = mysqli_connect($hostname, $username, $password, $databaseName);

    $user = $_POST['user'];
    $passcode = $_POST['passcode'];

    $stmt = mysqli_prepare($connect, "SELECT * FROM admin WHERE username = ?");
    mysqli_stmt_bind_param($stmt, "s", $user); // bind parameters
    mysqli_stmt_execute($stmt); //execute query
    $result = $stmt->get_result(); // get result
    $row = $result->fetch_assoc(); // put results into array

   if (password_verify($passcode, $row['passcode'])) {
       echo 'Password is valid!';
   } else {
       echo 'Invalid password.';
   }
    mysqli_stmt_close($stmt); // close statement
}

Some things to note here:

you only have to select the row using the user name, no need to select the passcode
you do not have to count the rows, there should only be one row with that username
you may want to test if the query was successful (I skipped that here)
you will want to make sure you're properly checking for database errors like connection and query success, I've omitted that here.
Please read password_hash(), password_verify(), and MD5 is not secure

For your insert you should do this:

$user = $_POST['user'];
$passcode = password_hash($_POST['passcode'], PASSWORD_DEFAULT);

$connect = mysqli_connect($hostname, $username, $password, $databaseName);

$stmt = mysqli_prepare($connect,"INSERT INTO `admin` (`username`, `passcode`) VALUES(?, ?)");
mysqli_stmt_bind_param($stmt, "ss", $user, $passcode);

Then you can execute and check the insertion.

PHP - Validating Password with MD5 and MySQL

3 Answers3

Your code is not secure consider using php's password_hash() and password_verify() function and prepared statements!