CODEWITHSUNDEEP
mysql
ahhchuu
ahhchuu

Reputation: 471

MySQL throwing error when using @ symbol in query

I am trying to check if an email address already exists in a user database. I am using AJAX. When the SELECT statement executes, I always get an error returned. The statement seems to break at the @ symbol.

AJAX call:

var emailok = false;
var email = $('input[name="email"]');
$(function(){
        $.ajax({
                type: "POST",
                data: "email="+email.attr('value'),
                url: "check_email.php",
                success: function(data){
                    alert(data);
                    if(data != 0) {
                        emailok = false;
                        $('#exists_message').slideDown('slow').delay(3500).slideUp('slow');
                        for(i=0;i<4;i++) {
                            email.animate({
                                backgroundColor: '#AC0A0A'
                            },300).animate({
                                backgroundColor: '#FFFFFF'
                            },300);
                        }
                    } else {
                        emailok = true;
                    }
                }
           });

And here is my check_email PHP script:

<?php

include('connect.php');

$email = $_POST['email'];

try {
    $STH = $DBH->prepare("SELECT * FROM users WHERE email=$email");
    $STH->execute();
} catch(PDOException $e) {
    echo $e->getMessage();
}

$DBH = NULL;
$users =  $STH->fetchAll(PDO::FETCH_OBJ);

echo ' Matches - ' . sizeof($users);

?>

Just in case there is an error in my connect script, here it is:

<?php

$host   = 'localhost';
$user   = '********';
$pass   = '********';
$dbname = 'tm';
try {
    $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
    $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
} catch(PDOException $e) {
    echo $e->getMessage();
}

?>

And here is the error that comes up in that alert:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@asdf.asd' at line 1 Matches - 0

That 0 at the end of the error is the number of rows found with that email address, so the statement executes.

What am I doing wrong? I have always used PDO prepare for email addresses and have never gotten this error before.

EDIT: Just so I don't get more of these comments, I know I need to use a prepared statement to prevent SQL injection. I merely did it this way to test. It was the missing single quotes around the email variable. Thanks everyone. :)

Upvotes: 2

Views: 3474

Answers (2)

feeela
feeela

Reputation: 29932

As you are using prepared statements, you would bet better off to also use `bindValue()``to properly escape the input:

$STH = $DBH->prepare("SELECT * FROM users WHERE email=:email");
$STH->bindValue(':email', $email);
$STH->execute();

You don't need to add the single quotes to the query – PDO will do that for you.

Upvotes: 3

Kao Vang
Kao Vang

Reputation: 412

Strings need to be wrapped in single quotes, so your SQL should be:

"SELECT * FROM users WHERE email='$email'"

Upvotes: 5

Related Questions