PDO bindParam behaving strangely

I am having a problem getting PDO bindParam to work correctly. Using this code:

$foo = "bar";

$stmt_1 = $db->prepare("SELECT * FROM table WHERE foo = $foo");
$stmt_1->execute();
$results_1 = $stmt_1->fetchAll();

$stmt_2 = $db->prepare("SELECT * FROM table WHERE foo = ?");
$stmt_2->bindParam(1, $foo, PDO::PARAM_STR);
$stmt_2->execute();
$results_2 = $stmt_2->fetchAll(开发者_高级运维);

$stmt_3 = $db->prepare("SELECT * FROM table WHERE foo = :foo");
$stmt_3->bindParam(":foo", $foo, PDO::PARAM_STR);
$stmt_3->execute();
$results_3 = $stmt_3->fetchAll();

$results_1 contains only the row(s) where foo = bar, $results_2 is empty, and $results_3 contains every entry in "table"

bindValue has the exact same problem, too. Anyone know what's going on here or what I'm doing wrong?

I solved my problem. For some reason, the variables I was setting from $_POST weren't available to the internal functions of a class I had defined, so I added a parameter array to the class to hold the bound variables, and now everything works.

(I'll do a new answer because I misunderstood the question in the other one.)

By default PDO silently ignores errors and returns empty results. This could explain #2. For example, if your table is really called "table" and you didn't quote it with backticks (table is a reserved keyword). Turn on error reporting to see if this is the case (see below).

In #3, all rows might be returned because the condition is tautological: foo = foo or :foo = :foo or 'foo' = :foo.

Here's a complete program that works correctly for me. If it doesn't work for you, it's likely a bug in a particular version of PHP, PDO or MySQL.

<?php

$db = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$foo = 'bar';

$stmt_1 = $db->prepare("SELECT * FROM `table` WHERE foo = '$foo'");
$stmt_1->execute();
$results_1 = $stmt_1->fetchAll();

$stmt_2 = $db->prepare("SELECT * FROM `table` WHERE foo = ?");
$stmt_2->bindParam(1, $foo, PDO::PARAM_STR);
$stmt_2->execute();
$results_2 = $stmt_2->fetchAll();

$stmt_3 = $db->prepare("SELECT * FROM `table` WHERE foo = :foo");
$stmt_3->bindParam("foo", $foo, PDO::PARAM_STR);
$stmt_3->execute();
$results_3 = $stmt_3->fetchAll();

print_r($results_1);
print_r($results_2);
print_r($results_3);

foo = ? with parameter "bar" is equivalent to foo = 'bar', not foo = bar.
I.e., you're selecting where the column equals a string, not another column.

There's no need to use a parameter here because $foo is not user input. (If it is, it should be validated against the finite set of columns in the table.)

Basically, there are two stages to executing an SQL query: parsing and evaluation. SQL injection can only happen when you expose the parser to user input. Evaluation is safe regardless of the values in the columns.

So when you run WHERE foo = bar;, it is parsed as "compare columns foo and bar". That's the only thing the database will do, regardless of what the values in those columns are.