PHP: PDOStatement simple MySQL Select doesn't work.

Question

Hi

I have the following PHP code doing a very simple select into a table.

$statement = $db->prepare("SELECT * FROM account WHERE fbid = :fbid");
$statement->bindParam(":fbid",$uid, PDO::PARAM_STR,45);
$out = $statement->execute();
print_r($out) // 1;
//$out = $statement->execute(array(':fbid' => $uid)); // also doesn't work
$row = $statement->fetch();

$out is true (success) yet $row is null.

EDIT:

$statement->debugDumpParams();

Outputs

SQL: [40] SELECT * FROM account WHERE fbid = :fbid Params: 1 Key: Name: [5] :fbid paramno=-1 name=[5] ":fbid" is_param=1 param_type=2

If I modify the code as follows:

$statement = $db->prepare("SELECT * FROM account WHERE fbid = $uid");
$out = $statement->execute();
$row = $statement->fetch();

$row contains the record I'm expecting.

I'm at a loss. I'm using the PDO::prepare(), bindParams() etc to protect against SQL Injection (maybe I'm mistaken on that).

EDIT: In my example, $uid is a numerical string (ie a string containing only numbers). In the database, the column type is VARCHAR(45)

EDIT:

If I change the database type from VARCHAR(45) to BIGINT, both queries work. If I change the type in the database type back to VARCHAR(45) again, it works. So what gives?

Please halp.

Answer 1

It's been a while... try passing a hash to execute instead:

$statement->execute(array( 'fbid' => $uid ));

Answer 2

Try dropping the extra parameter,

$statement->bindParam (":fbid", $uid, PDO::PARAM_STR);

(edit) Are you 100% positive there is no extra whitespace surrounding the UID? Test with trim() and pass by value:

$statement->bindValue (":fbid", trim($uid), PDO::PARAM_STR);

Answer 3

Maybe try PDO::PARAM_INT

Aside from that, keep in mind bindParam() takes the variable as a reference. Maybe your demo code doesn't show you changing the value of that variable before execute() is called. See bindValue() if needed.

Answer 4

I think there may be an issue with your PDO installation.

$uid = 552192373; // my facebook uid for testing
$statement = $db->prepare("SELECT * FROM users WHERE facebook_uid = :fbid");
$statement->bindParam(":fbid",$uid, PDO::PARAM_STR,45);
$out = $statement->execute();
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo '<pre>';
print_r($row);
echo '</pre>';

returns:

Array
(
    [id] => 1
    [facebook_name] => Jason Boehm
    [facebook_uid] => 552192373
)

Answer 5

Hi Alan

You need to check your fbid value. it should be always string if its integer value is greater than 2^32 (unsigned), simply cast by (string)$uid is not work, and sprintf("%.0f",...) will only works when integer value less than 2^52, because on 32-bit OS when a number is greater than 2^31(32 unsigned) PHP will assume it is double type and default precise is only 14 decimal but fbid is 20.

You have to keep fbid in string contains only [0-9] in PHP, doesn't matter it is stored as BIGINT or VARCHAR in MySQL, MySQL accepts only string sql statement and always returns result in string format.

$mi = new mysqli("localhost", "root", "xxx", "test");
$uid = "12379739851403943597";   // Works
//$uid = 12379739851403943597;   // never Works
//$uid = (string) 12379739851403943597;   // get "1.2379739851404E+19" wrong string !
//$suid = sprintf("%.0f", $uid);          // get "12379739851403943936" lost precise

$stmt = $mi->prepare("select * from bitest where id = ?");
$stmt->bind_param('s', $uid);

$stmt->execute();
$stmt->bind_result($id, $name);

$stmt->store_result();
print "numrow: " . $stmt->num_rows . " - \n";
$stmt->fetch();
print "$id - $name \n";
$stmt->free_result();

$stmt->close();


$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', 'xxx');
$sql = "select * from bitest where id = ?";

$sth = $pdo->prepare($sql);
$sth->bindParam(1, $uid, PDO::PARAM_STR);

$sth->execute();
var_dump($sth->fetchAll(PDO::FETCH_ASSOC));