Fatal Error due to PDO Exception because of a SQL query with dynamic PHP input

Question

After almost a day of researching I cannot really see what the error with my code is. I am trying to create a small prototype function that returns the number of rows of query results.

The error I get is: Uncaught PDOException: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The multi-part identifier "my@email.com" could not be bound. in C:\inetpub\wwwroot\index.php:59

Row 59 being $stmt->execute();.

I believe the issue comes from my SQL query but I am not 100% sure.

The way the code works is by getting values from a POST input and it checks in a row and column if it contains said value in a schema.

Additionally, I am using MSSQL(not MySQLi) and PHP for the task.

This is the main bit where I want the php code to send an SQL query and the check if the result exists.

<?php  
    include "connect.php";

    if(isset($_POST['login']))
    {
        $email = $_POST['EMAIL'];
        $password = $_POST['PASSWORD']; 

        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = $conn->prepare("SELECT * FROM users WHERE EMAIL=$email;"); 
        $stmt->execute();

        $count= $stmt->rowCount();
        echo "Row count: $count \n";
    }
?>

Just to mention I have tried all kinds of changes to the SQL query like:

$stmt = $conn->prepare("SELECT * FROM users WHERE EMAIL='".$email."';");

and

$stmt = $conn->prepare("SELECT * FROM users WHERE EMAIL=?;");

with an array input that had $email in it.

All returned the same identical error.

Here I have the connect.php, just in case the issue might come from here.

<?php 
        $servername = "localhost";
        $username = "sa";
        $password = ""; 

        try 
        {
            $conn = new PDO("sqlsrv:Server=$servername; Database=db", $username, $password);
            // set the PDO error mode to exception
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            echo "Connected!";
            return $conn;
        }

        catch(PDOException $e)
        {
            echo "Connection failed: " . $e->getMessage();
        }
?>

Sorry @u_mulder, I accidentally pasted the wrong error log. Now its fixed in the lines above. — Jeanie Miflin, Apr 02 '19 at 09:13
At the time I was experimenting to see if the issue was with the type of input but I do not think it was. — Jeanie Miflin, Apr 02 '19 at 09:14
`$email` is a __string__ value and should be quoted. Or use prepared statements. — u_mulder, Apr 02 '19 at 09:15
Yes, I said above I tried to add it in as `EMAIL=' " .$email. " '` but it gave me back the same error. — Jeanie Miflin, Apr 02 '19 at 09:18
Possible duplicate of [The multi-part identifier could not be bound](https://stackoverflow.com/questions/7314134/the-multi-part-identifier-could-not-be-bound) — Daniel W., Apr 02 '19 at 09:19
@u_mulder Wow, i was apparently so wrong! I believe that error buffer got bugged or something like that as it kept returning the error I mentioned above. This is the error I get now: `PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The data types text and varchar are incompatible in the equal to operator. in C:\inetpub\wwwroot\index.php:59` — Jeanie Miflin, Apr 02 '19 at 09:39
`SELECT * FROM users WHERE CONVERT(NVARCHAR, EMAIL)= '".$email."'; ` is the query I am using now. — Jeanie Miflin, Apr 02 '19 at 09:41
All right, now it returns a row value of `-1`. That's not how it supposed to work, is it? — Jeanie Miflin, Apr 02 '19 at 09:42
Is this really how `rowCount();` is supposed to work? Decrement a value instead of incrementing ? — Jeanie Miflin, Apr 02 '19 at 09:50
Probably. See [PHP doc](https://www.php.net/manual/en/pdostatement.rowcount.php) for full explanation. — DinoCoderSaurus, Apr 02 '19 at 12:01

score 0 · Accepted Answer · answered Apr 03 '19 at 06:20

0

Basically the way I solved the problem was to have the EMAIL and PASSWORD converted to VARCHAR's:

$stmt = $conn->prepare("SELECT ID, EMAIL, LAST_NAME FROM users WHERE CONVERT(VARCHAR, EMAIL)='my@email.com' AND CONVERT(VARCHAR, PASSWORD)='1234'");

answered Apr 03 '19 at 06:20

Jeanie Miflin

69
9

Fatal Error due to PDO Exception because of a SQL query with dynamic PHP input

1 Answers1