Prepared statement returns no rows

Question

Brief version (without try's and catches):

$dbConnection = new PDO("mysql:host=$serverName;dbname=$dbName", $userName, $password, $dbOptions);
$dbStatement = $dbConnection->prepare('SELECT * FROM bin_content WHERE LotId=":lot";');
dbStatement->bindParam(':' . $key, $filter->$key);
// $filter->lot contains "A32" and $key="lot",
// so I'm assuming "A32" will be filled into the statement
$dbStatement->execute(); // this is actually in a try/catch, and no exceptions are caught here
$row = $dbStatement->fetch(); // this returns false

As mentioned in the above code, the fetch() returns false, but if I manually query my database with the following SQL statement, a row is returned:

SELECT * FROM bin_content WHERE LotId="A32";

I'm binding a property of the $filter object because the queries are dynamic depending on the properties of $filter. Is it possible that somehow I'm not binding what I think I'm binding?

Answer 1

When using prepared statements, don't quote what you would like to later 'inject' safely into the statement.

If you query this

"SELECT * FROM table WHERE user = 'some-user'" 

It will literally look for users with that name, which is why it literally looks for users ":lot" when you query the database.

Instead use

"SELECT * FROM table WHERE user = :user_name"

bindParam->(':user_name', $var);

->execute ();

Now it will safely inject $var into the prepared statement, then you can execute it