18
$sql = "SELECT * FROM table WHERE id LIKE CONCAT('%', :id, '%')
LIMIT :limit1, :limit2";

I want to still use the array input like this:

$stmt->execute($array);

Otherwise I cannot reuse the same method for executing my queries.

At the same time, the :limit1 and :limit2 doesn't work unless it is put in like this:

$stmt->bindParam(':limit1', $limit1, PDO::PARAM_INT);

I tried to do both but it doesn't execute with the bindParams:

$stmt->bindParam(':limit2', $limit2, PDO::PARAM_INT);
$stmt->execute($array);

What is the way around it?

I thought I could extend PDOStatement and add a new method "bindLimit" or something but I can't figure out what internal method PDO uses to bind parameters to a variable.

webbiedave
  • 48,414
  • 8
  • 88
  • 101
user1275136
  • 301
  • 1
  • 3
  • 10
  • 1
    +1 for the idea of extending PDOStatement (however I'm not sure if that is the solution). – hakre May 03 '12 at 18:46
  • Can't you check for the limit params and bind them explicitly if present, but otherwise just use execute? –  May 03 '12 at 18:47
  • "Otherwise I cannot reuse the same method for executing my queries." Can you explain more about this method? – webbiedave May 03 '12 at 18:54
  • @webbiedave its just a convenience method to prepare, bind and execute with optional arguments for limit variables. I could just append the string 'LIMIT n,n' to the end of $sql, but then I'd have to use regex to clean the input string. – user1275136 May 03 '12 at 19:24

3 Answers3

18

If you turn off the default setting of PDO::ATTR_EMULATE_PREPARES, then it will work. I just found out that that setting is on by default for mysql, which means you never actually use prepared statements, php internally creates dynamic sql for you, quoting the values for you and replacing the placeholders. Ya, a major wtf.

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt = $pdo->prepare($sql);
$stmt->execute(array(5)); //works!

The prepares are emulated by default because of performance reasons.

See as well PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?

Community
  • 1
  • 1
goat
  • 31,486
  • 7
  • 73
  • 96
  • yup lol. check the mysql query log before and after disabling that setting and you'll see you never really were using prepared statements after all these years lol. – goat May 03 '12 at 19:27
  • I have to agree with eggyal: WTF? That's the wierdest thing I've ever heard of. – RonLugge Mar 04 '13 at 04:44
8

As stated in the documentation for PDOStatement::execute:

input_parameters

An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

For the most part, this goes entirely unnoticed as MySQL's implicit type conversion handles the rest (but it can cause some undesirable behaviour if MySQL is using a particularly weird connection character set, as converting strings of numbers back to their numeric value might not give the expected result).

In your case, MySQL is attempting to execute a LIMIT clause that has string arguments. Whilst it could attempt to resolve that using its implicit type conversion, as it does everywhere else a string appears where an integer should be, it simply doesn't bother and runs off crying instead.

So, you need to tell PDO that these particular parameters are integers. Since even PHP doesn't know what type its variables are, I believe the only way to do that is to directly bind them with either PDOStatement::bindParam or PDOStatement::bindValue as you are doing (although it isn't strictly necessary to specify the $data_type argument as a simple (int) cast of the $variable gives PHP's otherwise clueless type system enough to work with).

As far as the internal method PDO uses to bind parameters to a variable goes, take a look at really_register_bound_param() (unsurprisingly not exposed to PHP, so you'll be having lots of fun in C).

Good luck!

eggyal
  • 122,705
  • 18
  • 212
  • 237
0

Why bind limit values when they're not user input?

$start = 0;
$limit = 20;
$sql = "SELECT * FROM table WHERE id LIKE CONCAT('%', :id, '%')
    LIMIT $start, $limit";

Even if $start and $limit are determined from user input, say from a $_GET, you can test the value with is_int().

inorganik
  • 24,255
  • 17
  • 90
  • 114