Executing PDO Query changes bound parameter from integer to string

Question

Code Sample

$query = $this->db->prepare( $sql );                  // prepare sql
$query->bindParam( 'start', $start, PDO::PARAM_INT ); // bind start
$query->bindParam( 'end', $end, PDO::PARAM_INT );     // bind end
$query->bindParam( 'language', $this->language );     // bind language
$query->bindValue( 'keyword', "%$keyword%" );         // bind keyword

var_dump( $end );
$query->execute();
var_dump( $end );

Output

int 2
string '2' (length=1)

But... if I switch the order of binds...

$query = $this->db->prepare( $sql );                  // prepare sql
$query->bindParam( 'language', $this->language );     // bind language
$query->bindValue( 'keyword', "%$keyword%" );         // bind keyword
$query->bindParam( 'start', $start, PDO::PARAM_INT ); // bind start
$query->bindParam( 'end', $end, PDO::PARAM_INT );     // bind end

var_dump( $end );
$query->execute();
var_dump( $end );

Output

int 2
int 2

PHP Version: 5.3.8 on Windows

Can anyone explain why this is happening?

5.3.8 was released almost a year ago - Can anybody reproduce this on PHP > 5.4? — nickb
– nickb, Commented Jul 17, 2012 at 12:44
I think most people still use 5.3 and 5.2 - it should at least be fixed in the latest 5.3.x. So can anyone test this in 5.3.14? — HyderA
– HyderA, Commented Jul 17, 2012 at 12:49

wmarbut · Accepted Answer · 2012-07-31 03:39:52Z

1

Try turning of emulation for preparing statements

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

answered Jul 31, 2012 at 3:39

wmarbut

4,7257 gold badges45 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alexander R. · Accepted Answer · 2015-03-30 17:11:23Z

1

Checked this with PHP 5.3.13 - two versions of your code gives me:

int 2
string '2' (length=1)

Additionally, with using bindValue() instead of bindParam() two versions of code gives me:

int 2
int 2

p.s. I prefer work with bindValue() and not mix it with bindParam(). Using bindParam() doesn't gives any performance improvements. Some people thinks that passing by values and passing by pointer in PHP works as in C/C++, but this is wrong thinking. Using bindParam() may lead to bugs that hard to find them when they occurs.

answered Mar 30, 2015 at 17:11

Alexander R.

1,75612 silver badges19 bronze badges

Comments

bcesars · Accepted Answer · 2015-03-30 13:40:57Z

I know this has been said before but I'll write a note on it too because I think it's important to keep in mind:

If you use PDO bindParam to do a search with a LIKE condition you cannot put the percentages and quotes to the param placeholder %:keyword%.

This is WRONG:

"SELECT * FROM `users` WHERE `firstname` LIKE '%:keyword%'";

The CORRECT solution is to leave clean the placeholder like this:

"SELECT * FROM `users` WHERE `firstname` LIKE :keyword";

And then add the percentages to the php variable where you store the keyword:
$keyword = "%".$keyword."%";

And finally the quotes will be automatically added by PDO when executing the query so you don't have to worry about them.

So the full example would be:

<?php
    // Get the keyword from query string
    $keyword = $_GET['keyword'];
    // Prepare the command
    $sth = $dbh->prepare('SELECT * FROM `users` WHERE `firstname` LIKE :keyword');
    // Put the percentage sing on the keyword
    $keyword = "%".$keyword."%";
    // Bind the parameter
    $sth->bindParam(':keyword', $keyword, PDO::PARAM_STR);
?>

Collectives™ on Stack Overflow

Executing PDO Query changes bound parameter from integer to string

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related