CODEWITHSUNDEEP
phppdoprepared-statement
Martin Thoma
Martin Thoma

Reputation: 136287

Why can't I write PDO prepared statements in quotes?

I had the following piece of code with PDO prepared statements:

$stmt = $conn->prepare('SELECT `myColumn1` FROM my_table '.
                       'WHERE `myColumn2`=:val LIMIT 1');
$stmt->bindValue(":val", $value);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);

This works fine. It sends the following query:

113 Query   SELECT `myColumn1` FROM my_table WHERE `myColumn2`=":val" LIMIT 1

and it returns the correct value.

But it doesn't work if I change the first line to

$stmt = $conn->prepare('SELECT `myColumn1` FROM my_table '.
                       'WHERE `myColumn2`=":val" LIMIT 1');

or

$stmt = $conn->prepare('SELECT `myColumn1` FROM my_table '.
                       'WHERE `myColumn2`=':val' LIMIT 1');

The same query is sent, but PDO returns false.

Can anybody explain why?

Upvotes: 0

Views: 499

Answers (2)

lonesomeday
lonesomeday

Reputation: 237845

From the page you quote:

The parameters to prepared statements don't need to be quoted; the driver automatically handles this.

The purpose of the quotation marks is to delimit string data from the rest of the query, since it cannot easily be separated (unlike numbers, which have an obvious format). Since using prepared statements means that query and data are passed separately, the quotes are unnecessary.

Upvotes: 1

Corbin
Corbin

Reputation: 33437

One of the advantages of prepared statements are that types are handled for you (sort of...). In other words, prepared statements allow MySQL (or whatever RDBMS) to decide how to handle data. When putting quotes, that would force it to be a string which doesn't make sense. If it's supposed to be a string, then the server will handle that.

Upvotes: 0

Related Questions