Getting mysql error when loading my rest api

Question

I am getting this error when using the function login for checking the auth_key and updating it if it not exists. Below is the error and the code:

{"text":SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@gmail.com' at line 1}

here is my function code:

$app->post('/api/salesman/login',function(Request $request, Response $response)
{
    //echo "CUSTOMERS";

    $auth_key = $request->getParam('auth_key');
    $email = $request->getParam('email');
    $business_id = $request->getParam('business_id');

    $sql = "SELECT email FROM salesman WHERE email = $email";

    $sql1="SELECT auth_key FROM salesman WHERE email =$email";

    $sql2="UPDATE salesman SET
                auth_key = :auth_key

            WHERE email = $email";

    try{
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();

        $stmt =$db->query($sql);

        $email_id = $stmt->fetchAll(PDO::FETCH_OBJ);
        //$db = null;
        //echo ($customer_id);
        if($email_id==null)
            echo 'Please contact administrator';
        else
        {

        $stmt =$db->query($sql1);

        $auth_id = $stmt->fetchAll(PDO::FETCH_OBJ);
        //$db = null;
        if($auth_id=null)
        {
            $stmt = $db->prepare($sql2);
            $stmt->bindParam(':auth_key', $auth_key);
            $stmt->execute();
            echo '{"notice": {"text": "Login Successful"}';

        }

            echo '{"notice": {"text": "Login Successful"}';
        }




    }

    catch(PDOException $e)
    {
        echo '{"error":{"text":'.$e->getMessage().'}';
    }

});

Please help me figure out the error as I am unable to notice it if there is any due to sql syntax

I think you should put variables inside `''` in query. For i.e. `where email = '$email'` — Harshil Doshi, Nov 19 '17 at 23:05
Had you used a prepared statement for everything, you wouldn't have that problem. — Funk Forty Niner, Nov 19 '17 at 23:37

score 0 · Accepted Answer · answered Nov 19 '17 at 23:23

When you use email = $email, SQL thinks that $email is also a column name. So he tries to name email op example@gmail.com. But that column does not exist. Instead of that, you can use quotes

WHERE email = '$email'

Can you try the above example? (I had the same problem today)

Getting mysql error when loading my rest api

1 Answers1