PHP MySQLi authentication using if_num_rows not matching database information

Question

I am new to PHP / MySql programming. I have purchased a book to help learn the language and I have done well so far except when I tried to create an authentication system.

I want to be able to match the record to the database using MD5 encryption and if found send to the website. If the username and password are incorrect then send them to the login page again.

At one point it would only match the first record. Now it won't match any. I can type exactly what is in the database and the result still goes to 0 or back to the login page.

Also I am wanting to set a session variable for the username and auth_level so that I can call on it throughout my website/application.

I am using XAMPP on Mac if that helps.

Auth Script:

if ((!isset($_POST['username'])) || (!isset($_POST['password']))) {
    header('Location: login.html');
    exit;
}

$mysqli = mysqli_connect('localhost', 'username', 'password', 'testDB')
      or die(mysql_error($mysqli));

$username = mysqli_real_escape_string($mysqli, $_POST['username']);
$password = mysqli_real_escape_string($mysqli, $_POST['password']);
$auth_sql ="SELECT username , auth_level FROM auth_users WHERE

username ='".$username."' AND password =MD5('".$password."')";

$auth_sql_res = mysqli_query($mysqli, $auth_sql) or die(mysqli_error($mysqli));

if (mysqli_num_rows($auth_sql_res) == 1) {
    $_SESSION['username'] = $username;
    header('Location: homebeta.php');
} else {
    header("Location:index.php");
    exit;
}

PHP v5.3.1

Thank you everyone that takes the time to look, analyze, and/or help. I really appreciate your time.

You're trying to check for 1 or "any" rows. make sure you don't have any duplicate user records, which'd make the row count 2 or more. — Marc B, Apr 15 '13 at 16:06
I have made the username field unique to prevent duplicates. — user2283239, Apr 15 '13 at 17:22

score 1 · Answer 1 · edited May 23 '17 at 12:28

1

You forgot an exit after the first call to header:

header('Location: homebeta.php');
exit;

Are you checking PHP errors? Read How to get useful error messages in PHP? to know more.

I think your script may output something at the beginning, that prevents headers or session information to be sent.

edited May 23 '17 at 12:28

Community

1
1

answered Apr 15 '13 at 16:10

Jocelyn

11,209
10
43
60

I have Error Reporting E_ALL set in my php.ini config file. This is a dev/testing so I don't mind having it on. I am also using dreamweaver to code. I did add in the exit that I left out (thanks by the way.) – user2283239 Apr 15 '13 at 16:54
Also I took a look at homebeta.php. If $_SESSION['username'] is not set it also takes you to login.html. I changed this to send to a different page and I was sent to the different page. Maybe the session variable is not being set? I have session_start(); located on the homebeta.php page. Should I call that function within my auth script before setting the username session variable? -- just a thought -- I also do want to use MD5 once we get this all figured out. – user2283239 Apr 15 '13 at 17:35

score 1 · Answer 2 · 2013-04-15T17:01:27.330

1

Try this:

if ((!isset($_POST['username'])) || (!isset($_POST['password']))) {
    header('Location: http://www.replacethis.com/login.html');
} else {
    $mysqli = mysqli_connect('localhost', 'username', 'password', 'testDB')
        or die(mysql_error($mysqli));

    $username = mysqli_real_escape_string($mysqli, $_POST['username']);
    $password = mysqli_real_escape_string($mysqli, $_POST['password']);

    $auth_sql = "SELECT `username`, `auth_level`
        FROM `auth_users`
        WHERE `username` = '$username' AND `password` = MD5('$password')";

    $auth_sql_res = mysqli_query($mysqli, $auth_sql)
        or die(mysqli_error($mysqli));

    if (mysqli_num_rows($auth_sql_res) > 0) {
        $_SESSION['username'] = $username;
        header('Location: http://www.replacethis.com/homebeta.php');
    } else {
        header("Location: http://www.replacethis.com/index.php");
        exit;
    }
}

Else statement added.
Backticks in your SQL query (Just to be on the safe side)
Absolute URL in the header location.

And try removing the MD5 hashing from your query and copy n paste both username AND password in your HTML-form and then login.

edited Apr 15 '13 at 17:01

answered Apr 15 '13 at 16:49

You should post only the modified parts, or add comments to help us identify quickly what you modified. – Jocelyn Apr 15 '13 at 16:56
@Yassine: I tried it but it's doing the same thing, sending me back to index.php – user2283239 Apr 15 '13 at 16:59
The first `else` statement you added is useless since there is an `exit` in the `if` block. And it makes the code harder to read. – Jocelyn Apr 15 '13 at 17:00
Okay I tried it with your edits and I removed the md5 from the password query. I changed an account password on phpmyAdmin to be regular without md5 hashing and it sent me to login.html upon entering the credentials... – user2283239 Apr 15 '13 at 17:09
I got it to work without MD5 but I want it to work with MD5. The issue was with the session not being started yet. – user2283239 Apr 15 '13 at 17:51

score 0 · Answer 3 · answered May 01 '13 at 17:19

Well, you may not getting your error messages, since you are using mysql_error instead of mysqli like everything else, and specifically on connect, there is mysqli_connect_error().

Also, according to the manual, inside the parentheses should be void for mysqli_connect_error:

$mysqli = mysqli_connect('localhost', 'username', 'password', 'testDB')
  or die(mysqli_connect_error());

PHP MySQLi authentication using if_num_rows not matching database information

3 Answers3