Why do these prepared statements not work?

Question

I use NetBeans 6.8 and have MAMP with this config on my mac:

Apache 2.0.63
MySQL 5.1.37
PHP 4.4.9 & 5.2.10
APC 3.0.19 & APC 3.1.2
eAccelerator 0.9.5.3
XCache 1.2.2
phpMyAdmin 2.11.9.5 & phpMyAdmin 3.2.0.1
Zend Optimizer 3.3.3
SQLiteManager 1.2.0
Freetype 2.3.9
t1lib 5.1.2
curl 7.19.5
jpeg 7
libpng-1.2.38
gd 2.0.34
libxml 2.7.3
libxslt 1.1.24
gettext 0.17
libidn 1.15
iconv 1.13
mcrypt 2.5.8
YAZ 3.0.47 & PHP/YAZ 1.0.14

My PDO Driver for MySQL is the client library version 5.1.37

I try to use prepared statements here.

This works perfectly WITHOUT prepared statements:

try {
    $dbh = new PDO('mysql:host=localhost;dbname=test', 'root', 'root');
    $prepared = $dbh->prepare('SELECT * from sys_navigation_point WHERE name="root"');
    //$prepared->bindParam('foo', 'root');

    $prepared->execute();

    foreach($prepared as $row) {
        print_r($row);
    }
    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

But this does not work at all with a prepared statement. Getting a totally blank page when doing this:

try {
    $dbh = new PDO('mysql:host=localhost;dbname=test', 'root', 'root');
    $prepared = $dbh->prepare('SELECT * from sys_navigation_point WHERE name=:foo');
    $prepared->bindParam('foo', 'root');

    $prepared->execute();

    foreach($prepared as $row) {
        print_r($row);
    }
    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

foo should be replaced with root. However, it doesn't. Ok, so lets try this, which also does not work at all:

try {
    $dbh = new PDO('mysql:host=localhost;dbname=test', 'root', 'root');
    $prepared = $dbh->prepare('SELECT * from sys_navigation_point WHERE name=?');
    $prepared->bindParam(1, 'root');

    $prepared->execute();

    foreach($prepared as $row) {
        print_r($row);
    }
    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

Absolutely nothing. I don't even get an error message. Just an blank page. After this code, there is some echo with standard HTML output. It doesn't go out, so the script stops somewhere near the bindParam method call.

Again, this one works perfectly fine without any prepared statement:

try {
    $dbh = new PDO('mysql:host=localhost;dbname=test', 'root', 'root');
    $prepared = $dbh->prepare('SELECT * from sys_navigation_point WHERE name="root"');
    //$prepared->bindParam('foo', 'root');

    $prepared->execute();

    foreach($prepared as $row) {
        print_r($row);
    }
    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

Like you can see, obviously all versions are exactly the same query. With PS doesn't work. Without PS it does. Now I have found a brutal bug in PHP itself?

Is it possible that prepared statements are disabled somewhere?

Answer 1

The bind name is :foo - not foo. And you shouldn't use bindParam, but bindValue , since you aren't giving a variable as argument. Eg. :

$prepared->bindValue(':foo', 'root');

In general, I would recommend that you never use bindParam, since it has reference semantics and thus can create some really hard-to-spot errors.

Answer 2

You are using this kind of code :

$prepared->bindParam('foo', 'root');

But bindParam is expecting a variable as second parameter :

bool PDOStatement::bindParam  ( mixed $parameter  , 
    mixed &$variable  [, int $data_type = PDO::PARAM_STR  [, int $length  
    [, mixed $driver_options  ]]] )

Here, you should probably use bindValue, as you only want to bind a... value... and not a variable passed by reference to the SQL query :

bool PDOStatement::bindValue  ( mixed $parameter  , mixed $value  
    [, int $data_type = PDO::PARAM_STR  ] )

So, your code would look like :

$prepared->bindValue(':foo', 'root');

(Don't forget the ':' before the param name, btw ;-) )