Continuous query fail in login script

Question

I'm making a login script but it keeps failing and printing out the "connection failed". Why does the query fail and how can i make this better?

session_start();
if(isset($_POST['submit'])){
$email = $_POST['email'];
$pass = $_POST['pass'];

$connection=mysqli_connect("localhost","root","","dbname") or die('Connection failed.');
mysqli_select_db($connection, 'dbname') or die ("Connection failed.");

$query = mysqli_query($connection, "SELECT * FROM users WHERE user_email=`$email` AND user_pass=`$pass`") or die ('Connection failed.');

$check_user = mysqli_num_rows($query);

if($check_user == 1){
$row = mysqli_fetch_row($check_user);
$_SESSION[‘user_email’]=$email;
header('Location: yay.php');
}else{
echo"<script>alert('Email or password is incorrect.')</script>";
}
}

The line that fails:

$query = mysqli_query($connection, "SELECT * FROM users WHERE user_email=`$email` AND user_pass=`$pass`") or die ('Connection failed.');

Answer 1

Firstly, your query failed.

Why? Because, you're using the wrong type of quotes for your variables, being ticks.

$sel_user = "select * from users where user_email = `$email` AND user_pass=`$pass`";

Those should be single quotes.

$sel_user = "select * from users where user_email = '$email' AND user_pass='$pass'";

Checking for errors with http://php.net/manual/en/mysqli.error.php would have told you of the syntax error.

This doesn't help you:

$query = mysqli_query($connection, "SELECT * FROM users 
                                    WHERE user_email=`$email` 
                                    AND user_pass=`$pass`") 
                                    or die ('Connection failed.');
                                            ^^^^^^^^^^^^^^^^^^^^

this does:

$query = mysqli_query($connection, "SELECT * FROM users 
                                    WHERE user_email=`$email` 
                                    AND user_pass=`$pass`")
                                    or die(mysqli_error($connection));

Plus, you're using curly quotes for [‘user_email’] which will also cause your code to fail after you've changed the quotes. (Consult my footnotes on error reporting).

$_SESSION['user_email']=$email;

and adding exit; after header. Your code may want to continue executing.

For password storage, use CRYPT_BLOWFISH or PHP 5.5's password_hash() function. For PHP < 5.5 use the password_hash() compatibility pack.

Your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements, they're much safer.

---

In regards to your other question, now deleted:

• https://stackoverflow.com/q/30695536/ (viewable by OP and 10K+ members)

you had:

$connection=mysqli_connect("localhost","root","","db-name");
if (mysqli_connect_error()){
echo"Connection failed.";
}

so why are you using this now?

$connection=mysqli_connect("localhost","root","","dbname") or die('Connection failed.');
mysqli_select_db($connection, 'dbname') or die ("Connection failed.");

• mysqli_select_db($connection, 'dbname') you've already selected your database.

where the error you stated being (in your other question):

But i'm getting the error above in the title (mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given).

Plus this line:

$row = mysqli_fetch_row($check_user);

You're using the wrong variable for it $check_user, that should be $query.

• Read the manual http://php.net/manual/en/mysqli-result.fetch-row.php

As an example from the manual:

$query = "SELECT Name, CountryCode FROM City ORDER by ID DESC LIMIT 50,5";

    if ($result = mysqli_query($link, $query)) {

        /* fetch associative array */
        while ($row = mysqli_fetch_row($result)) {
            printf ("%s (%s)\n", $row[0], $row[1]);
        }

---

Footnotes:

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// rest of your code

Sidenote: Error reporting should only be done in staging, and never production.