@ (or "at sign") is ending my MySQL query early, resulting in error.

Question

I am working on my first login script. I have been following examples from a book, but this is where I am stuck. I want the user login to be an e-mail address, but when I would hit submit, I would get this error.

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\wamp\www\mysite\index.php on line 19

The script works from the book, there's no typos, and from Google, I found that error is given if the original query fails, so I decided to insert a "mysqli_error" to check what is wrong and I got this:

Nah. [email protected] have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@mysite.com AND password = SHA('password')' at line 1

So, I am almost positive that the @ sign is ending my query early. I have trim and mysql_real_escape_string as a way to clean up the strings, but besides that I have nothing. I have been Googling for a while now and I can't find or figure out how to make an exception for the @ ("at sign") or some kind of work around.

I didn't want this question to be too long or complicated, but I can provide more code if needed.

Thanks!

Edit: Here is the complete code to narrow down the solution.

if (isset($_POST['submit'])) {

    $loginEmail = mysqli_real_escape_string($dbc, trim($_POST['loginEmail']));
    $loginPassword = mysqli_real_escape_string($dbc, trim($_POST['loginPassword']));        

    $query = "SELECT user_id, username FROM user_db WHERE email = $loginEmail AND password = SHA('$loginPassword')";
    $loginData = mysqli_query($dbc, $query);

    if (mysqli_num_rows($loginData) == 1) {
        echo 'You win!';
    }

    else {
        $error = mysqli_error($dbc);
        echo 'Nah. ' . $loginEmail . $error;
    }
}

Answer 1

String literals need to be in single-quotes:

...WHERE email = '[email protected]' AND password = SHA('password')

The mysql_real_escape_string() function adds backslashes to special characters within your variable's content, but it does not delimite the string with single-quotes.

For example:

$loginEmail = "O'Reilly";
$loginEmail = mysql_real_escape_string($loginEmail);

Now the content of $loginEmail is:

O\'Reilly

Not:

'O\'Reilly'

So you need to add the quotes in your SQL expression yourself:

$query = "SELECT user_id, username FROM user_db 
          WHERE email = '$loginEmail' AND password = SHA('$loginPassword')";

I really recommend using PDO and prepared queries instead of all the meticulous interpolation of variables into SQL. That way, you don't have to worry about mysql_real_escape_string() or quotes or special characters.

$pdo = new PDO(...connection arguments...);
$sql = "SELECT * FROM mytable WHERE email = ? AND password = SHA(?)";
$stmt = $pdo->prepare($sql);
$stmt->execute(array($loginEmail, $loginPassword));

See? You don't need to use single-quotes around the ? parameter placeholders, you don't need to escape the variables you pass as parameters, and yet you don't need to worry about SQL Injection.

Also see my presentation SQL Injection Myths and Fallacies.

Also please read You're Probably Storing Passwords Incorrectly by fearless leader.

Answer 2

An E-mail address will be a string: are you quoting it in your SQL query? Use quotes in the same way as you're using them for $loginPassword

$query = "SELECT user_id, username FROM user_db WHERE email = '$loginEmail' AND password = SHA('$loginPassword')"; 

or better still, use bind variables, as Bil Karwin's example shows