mysqli_query can't read users table

Question

i have problem with my Blog site project. Before go to the topic, just for information my english is really bad, so i'm sorry if you cannot understand what i'm saying xD

This is my Front End login panel Click Here to view, And this is my checklogin.php script :

include('connection.php');

error_reporting(0);
session_start();

$username = $_POST['username'];
$password = md5 ($_POST['password']);

$query = mysqli_query("SELECT * from users where username ='$username' and password ='$password'");
$data = mysqli_fetch_array($query);
$ada = mysqli_num_rows($query);
    if ($password == $data['password']){
        $_SESSION['user'] = $data['username'];
        $_SESSION['level'] = $data['level'];
        $_SESSION['nama'] = $data['nama'];
        $_SESSION['jabatan'] = $data['jabatan'];
        $_SESSION['pic'] = $data['pic'];
        session_register("username");
        session_register("password");

        echo "<meta http-equiv='refresh' content='0; URL=loading.php'>";
    }
    else 
        echo "Wrong Username or Password!!!

<meta http-equiv='refresh' content='0; URL=login.php'>";

Then this is my connection.php script :

mysqli_connect('localhost', 'root', '', 'masjid');
//mysqli_select_db("masjid");

And this is the problem, when i'm trying to login with user and password from database (example : username = admin, password = 12345), it's redirecting to checklogin.php and i got message like this Click Here to view, and the table structure Click Here to view. For some reason i think it's problem with mysqli_connect or mysql_query but i didn't get it, so please help and what i need to do?

Thanks

You should probably pass the database connection to `mysqli_query()` (http://php.net/manual/en/mysqli.query.php), which is the connection you should be getting from the `mysqli_connect()` which you don't store. — Nigel Ren, Jun 07 '18 at 16:42
You should also look into prepared statements and also password hash and verify (https://stackoverflow.com/questions/30279321/how-to-use-password-hash). — Nigel Ren, Jun 07 '18 at 16:43
Please dont __roll your own__ password hashing specially not using MD5(). PHP provides [`password_hash()`](http://php.net/manual/en/function.password-hash.php) and [`password_verify()`](http://php.net/manual/en/function.password-verify.php) please use them. And here are some [good ideas about passwords](https://www.owasp.org/index.php/Password_Storage_Cheat_Sheet) If you are using a PHP version prior to 5.5 [there is a compatibility pack available here](https://github.com/ircmaxell/password_compat) — RiggsFolly, Jun 07 '18 at 16:58
`session_register()` **Warning** This function has been DEPRECATED as of PHP 5.3.0 and REMOVED as of PHP 5.4.0. ___What version of PHP are you running___ — RiggsFolly, Jun 07 '18 at 17:00
`error_reporting(0);` Turns off all error reporting. A little premature may I suggest. — RiggsFolly, Jun 07 '18 at 17:02
Add `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to the top of your script. This will force any `mysqli_` errors to generate an Exception that you can see on the browser and other errors will also be visible on your browser. — RiggsFolly, Jun 07 '18 at 17:03
Echoing an error message at the same time and therefore in the same place in the output HTML sound like a very bad idea — RiggsFolly, Jun 07 '18 at 17:05

score 0 · Answer 1 · answered Jun 07 '18 at 16:34

There is no need to match password again. As you already fetched record from database using $username & $password. And code should be:

include('connection.php');

error_reporting(0);
session_start();

$username = filter_var($_POST['username'], FILTER_SANITIZE_STRING);
$password = md5($_POST['password']);

$query = mysqli_query("SELECT * from users where username ='$username' and password ='$password'");
$data = mysqli_fetch_array($query);
$ada = mysqli_num_rows($query);
if (empty($data['password'])) {
    $_SESSION['user'] = $data['username'];
    $_SESSION['level'] = $data['level'];
    $_SESSION['nama'] = $data['nama'];
    $_SESSION['jabatan'] = $data['jabatan'];
    $_SESSION['pic'] = $data['pic'];
    session_register("username");
    session_register("password");
   echo "<meta http-equiv='refresh' content='0; URL=loading.php'>";
} else {
    echo "Wrong Username or Password!!!
       <meta http-equiv='refresh' content='0; URL=login.php'>";
}

score 0 · Answer 2 · answered Jun 07 '18 at 16:35

I suspect your SQL query is too specific:

"SELECT * from users where username ='$username'"

Should suffice to find the user you're looking for. The following IF statement should be used to verify the password.

As a side note, don't use MD5 hashing for passwords, it's too easily broken. Use PHP's password_hash and password_verify functions to confirm the password is correct.

mysqli_query can't read users table

2 Answers2