php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #56669 Prepared select query not working with bound variable
Submitted: 2005-11-21 20:03 UTC Modified: 2006-04-09 01:24 UTC
From: jay at cheakamus dot com Assigned:
Status: Not a bug Package: pdo_mysql (PECL)
PHP Version: 5.0.5 OS: Ubuntu Breezy Badger
Private report: No CVE-ID: None
 [2005-11-21 20:03 UTC] jay at cheakamus dot com
Description:
------------
While trying to implement PDO with MySQL on a new project (first attempt at using PDO) I got stumped with preparing a query statement. I have combed through the examples at http://wiki.cc/php/PDO_Basics and implemented the following code. It returns nothing. I can prepare the statement with no bound variables and it works fine.

For reference: 
  PHP 5.0.5-2ubuntu1 (breezy)
  MySQL 4.1.12-1ubuntu3 (breezy)
  PDO-1.0RC2 (pear install)
  PDO_MYSQL-1.0RC2 (pear install)

Aside from adding the extensions for pdo and pdo_mysql to php.ini this is a stock Breezy PHP and mysql installation.

Reproduce code:
---------------
<?php
print "<pre>\n";
$db = new PDO("mysql:dbname=pdotest;host=localhost", "", "");
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$stmt = $db->prepare( "SELECT * FROM feeds WHERE url = :url" );
$url = "http://www.planet-php.net";
$stmt->bindParam( ":url", $url );
$stmt->execute();
while( $row = $stmt->fetch() )
{
    print_r( $row );
}
print "</pre>\n";
?>


Expected result:
----------------
<pre>
Array
(
    [id] => 1
    [0] => 1
    [name] => Planet-PHP
    [1] => Planet-PHP
    [url] => http://www.planet-php.net
    [2] => http://www.planet-php.net
    [feed] => http://www.planet-php.net/rdf/
    [3] => http://www.planet-php.net/rdf/
)
</pre>

Actual result:
--------------
<pre>
</pre>

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-11-21 20:08 UTC] wez@php.net
Do you actually have any rows in the database?
Does the URL match any of them?
 [2005-11-21 20:17 UTC] jay at cheakamus dot com
Yes

For reference:

mysql> SELECT * FROM feeds;
+------+-------------+---------------------------+--------------------------------+
| id   | name        | url                       | feed                           |
+------+-------------+---------------------------+--------------------------------+
|    1 | Planet-PHP  | http://www.planet-php.net | http://www.planet-php.net/rdf/ |
|    2 | SOA Journal | http://www.soajournal.net | http://www.soajournal.net/rdf/ |
|    3 | PHP Weekly  | http://www.phpweekly.net  | http://www.phpweekly.net/rdf/  |
+------+-------------+---------------------------+--------------------------------+
3 rows in set (0.00 sec)

mysql>

Also the "Expected Result" was generated using a static query with no bound variables:

<?php
print "<pre>\n";
$db = new PDO("mysql:dbname=pdotest;host=localhost", "", "");
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$url = "http://www.planet-php.net";
$stmt = $db->prepare( "SELECT * FROM feeds WHERE url = '$url'" );
//$stmt->bindParam( ":url", $url );
$stmt->execute();
while( $row = $stmt->fetch() )
{
    print_r( $row );
}
print "</pre>\n";
?>
 [2005-11-24 16:42 UTC] wez@php.net
Can you try changing your bindParam call to this:

$stmt->bindParam("url", $url);

Also, add a:
$stmt->debugDumpParams();
call after you prepare the query.

(the : prefix should work, just wondering if it is badly implemented).
 [2005-11-25 00:45 UTC] jay at cheakamus dot com
Just removing the : has no effect.

When I added $stmt->debugDumpParams(); to it (after
$stmt->bindParam( "url", $url );) and hit reload the CPU maxes out
and Firefox eventually comes back with 577105 lines of:

SQL: [36] SELECT * FROM feeds WHERE url = :url
Params:  1
Key: Position #15:
paramno=0
name=[4] :url
is_param=1
param_type=2
Key: Position #15:
paramno=0
name=[4] :url
is_param=1
param_type=2

(those last five lines repeat until eof)

Jay
 [2005-11-29 22:21 UTC] wez@php.net
Can you try upgrading to the latest PDO packages and/or PHP 5.1.1?
 [2005-11-30 11:53 UTC] jay at cheakamus dot com
I upgraded the PDO packages only and get the same result, with a caveat. When running "pear upgrade pdo" the last few lines were:

  Build process completed successfully
  Installing '/var/tmp/pear-build-root/install-PDO-1.0.2//usr/lib/php5/20041030/pdo.so'
  Installing '/var/tmp/pear-build-root/install-PDO-1.0.2//usr/include/php/ext/pdo/php_pdo.h'
  Installing     '/var/tmp/pear-build-root/install-PDO-1.0.2//usr/include/php/ext/pdo/php_pdo_driver.h'
upgrade ok: PDO 1.0.2
  Segmentation fault
  root@cochrane:/home/jay #

Running 'pear list' indicates:

  PDO                  1.0.2   stable
  PDO_MYSQL            1.0     stable

and the scripts run fine with non prepared statements, so it appears to have installed correctly despite the segfault.

I'll look into upgrading php to 5.1.1 later. I'm trying to keep this system as stock Ubuntu as possible, so it would be really nice if it worked with 5.0.5...
 [2006-01-02 17:46 UTC] graybark at bellsouth dot net
FYI from this page: 

http://bugzilla.ubuntu.com/show_bug.cgi?id=18167 

and those referenced from it I get the impression the problem is with breezy's 4.1.12 MySQL server package.  Prepared statements aren't working from Java either.  There's a workaround that works by providing a parameter on the connection URL; it works for my Java code, don't know if it applies to your case.
 [2006-04-09 01:24 UTC] wez@php.net
Unable to reproduce, and user comments finger the ubuntu mysql server package as being broken.
 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Wed Nov 20 12:01:21 2019 UTC