127

Here is a snapshot of my code:

$fetchPictures = $PDO->prepare("SELECT * 
    FROM pictures 
    WHERE album = :albumId 
    ORDER BY id ASC 
    LIMIT :skip, :max");

$fetchPictures->bindValue(':albumId', $_GET['albumid'], PDO::PARAM_INT);

if(isset($_GET['skip'])) {
    $fetchPictures->bindValue(':skip', trim($_GET['skip']), PDO::PARAM_INT);    
} else {
    $fetchPictures->bindValue(':skip', 0, PDO::PARAM_INT);  
}

$fetchPictures->bindValue(':max', $max, PDO::PARAM_INT);
$fetchPictures->execute() or die(print_r($fetchPictures->errorInfo()));
$pictures = $fetchPictures->fetchAll(PDO::FETCH_ASSOC);

I get

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''15', 15' at line 1

It seems that PDO is adding single quotes to my variables in the LIMIT part of the SQL code. I looked it up I found this bug which I think is related: http://bugs.php.net/bug.php?id=44639

Is that what I'm looking at? This bug has been opened since April 2008! What are we supposed to do in the meantime?

I need to build some pagination, and need to make sure the data is clean, sql injection-safe, before sending the sql statement.

Geoffrey
  • 5,407
  • 10
  • 43
  • 78
Nathan H
  • 48,033
  • 60
  • 165
  • 247
  • [Here is a related quetion but with bindParam instead](http://stackoverflow.com/questions/15853266/pdo-bindparam-not-allowing-statement-to-return-results) – Timo Huovinen Oct 24 '13 at 18:49
  • Noteworthy Answer in a duplicate Question: [Parametrized PDO query and \`LIMIT\` clause - not working \[duplicate\] (Aug 2013; by Bill Karwin)](http://stackoverflow.com/a/18006026/367456) – hakre Mar 29 '15 at 11:37

11 Answers11

175

I remember having this problem before. Cast the value to an integer before passing it to the bind function. I think this solves it.

$fetchPictures->bindValue(':skip', (int) trim($_GET['skip']), PDO::PARAM_INT);
Stephen Curran
  • 7,433
  • 2
  • 31
  • 22
  • 37
    Thanks! But in PHP 5.3, the above code threw an error saying "Fatal error: Cannot pass parameter 2 by reference". It doesn't like casting an int there. Instead of `(int) trim($_GET['skip'])`, try `intval(trim($_GET['skip']))`. – Will Martin Apr 08 '11 at 17:01
  • 6
    would be cool if someone provided the explanation why this is so...from a design/security (or other) standpoint. – Ross Sep 25 '12 at 00:23
  • 7
    This will only work if **emulated prepared statements are enabled**. It will fail if it is disabled (and it should be disabled!) – Madara's Ghost Nov 15 '12 at 19:32
  • 1
    @WillMartin Are you sure you're not using `bindParam`? – Chris Feb 23 '13 at 06:01
  • 1
    Two+ years later, this bug still exists in PHP 5.4! I was only able to get this to work by setting $integer=intval($limit) , then using $integer as the second parameter of the bind function, in order to prevent the 'cannot pass parameter 2 by reference error'. Also- same problem with OFFSET too. – FredTheWebGuy Oct 13 '13 at 00:46
  • 4
    @Ross I cannot specifically answer this- but I can point out that LIMIT and OFFSET are features that were glued on AFTER all this PHP/MYSQL/PDO madness hit the dev circuit... In fact, I believe it was Lerdorf himself who oversaw LIMIT implementation a few years back. No, it doesn't answer the question, but it does indicate that it's an aftermarket add-on, and you know how well they can work out sometimes.... – FredTheWebGuy Oct 13 '13 at 00:55
  • 1
    @Dreadful_Code [LIMIT and OFFSET are not part of the SQL standard](http://en.wikipedia.org/wiki/Select_(SQL)#Non-standard_syntax), that might explain why it was "glued on" – Timo Huovinen Oct 24 '13 at 19:21
  • I was not able to execute the prepared statement again when I had a bound param in LIMIT and OFFSET, it would give empty results – Timo Huovinen Oct 24 '13 at 20:59
  • 2
    @Ross PDO doesn't allow binding towards values - rather variables. If you try bindParam(':something', 2) you will have an error as PDO uses a pointer to the variable which a number can't have (if $i is 2 you can have a pointer towards $i but not towards the number 2). – Kristijan Jun 20 '14 at 08:34
  • @Kristijan - I understand this, but the parameterizing is supposed to allow variable substitution. if I set $v=2 and bindparam on $v to :limit and include :limit in the sql statement, it causes an error - this should not happen and is probable a result of what Dreadful mentioned. – Ross Jun 23 '14 at 00:29
  • @Ross just wanted to explain the PDO "issue" with binding values instead of variables :) – Kristijan Jun 23 '14 at 01:13
53

The simplest solution would be to switch the emulation mode off. You can do it by simply adding the following line

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

Also, this mode can be set as a constructor parameter when creating a PDO connection. It could be a better solution as some report their driver doesn't support the setAttribute() function.

It will not only solve your problem with binding, but also let you send values directly into execute(), which will make your code dramatically shorter. Assuming the emulation mode has been already set, the whole affair will take as much as half a dozen lines of code

$skip = isset($_GET['skip']) ? (int)trim($_GET['skip']) : 0;
$sql  = "SELECT * FROM pictures WHERE album = ? ORDER BY id LIMIT ?, ?";
$stmt  = $PDO->prepare($sql);
$stmt->execute([$_GET['albumid'], $skip, $max]);
$pictures = $stmt->fetchAll(PDO::FETCH_ASSOC);
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • `SQLSTATE[IM001]: Driver does not support this function: This driver doesn't support setting attributes`...Why is it never so simple for *me* :) While I'm sure this will get most people there, in my case I ended up having to use something similar to the accepted answer. Just a heads up to future readers! – Matthew Johnson May 08 '14 at 21:36
  • @MatthewJohnson what driver it is? – Your Common Sense May 09 '14 at 05:35
  • I'm not sure, but in [the manual](http://www.php.net/manual/en/pdo.setattribute.php) it says `PDO::ATTR_EMULATE_PREPARES Enables or disables emulation of prepared statements. Some drivers do not support native prepared statements or have limited support for them`. It's a new one to me, but then again I'm just getting started with PDO. Usually use mysqli, but figured I'd try to broaden my horizons. – Matthew Johnson May 09 '14 at 13:49
  • @MatthewJohnson if you are using PDO for mysql, driver does support this function all right. So, you are getting this message due to some mistake – Your Common Sense May 09 '14 at 14:07
  • Thank you so much. I was going to start punching myself in the forehead if this didn't start to work by the time I found your answer. – nf071590 Sep 23 '14 at 01:42
  • 1
    If you got driver support problem message, check again if you call `setAttribute` for the statement($stm, $stmt) not for the pdo object. – Jehong Ahn Dec 21 '16 at 06:44
18

Looking at the bug report, the following might work:

$fetchPictures->bindValue(':albumId', (int)$_GET['albumid'], PDO::PARAM_INT);

$fetchPictures->bindValue(':skip', (int)trim($_GET['skip']), PDO::PARAM_INT);  

but are you sure your incoming data is correct? Because in the error message, there seems to be only one quote after the number (as opposed to the whole number being enclosed in quotes). This could also be an error with your incoming data. Can you do a print_r($_GET); to find out?

Pekka
  • 442,112
  • 142
  • 972
  • 1,088
  • 1
    ''15', 15'. The first number is fully enclosed in quotes. The second number has no quotes at all. So yes, the data is good. – Nathan H Feb 16 '10 at 00:37
12

This just as summary.
There are four options to parameterize LIMIT/OFFSET values:

  1. Disable PDO::ATTR_EMULATE_PREPARES as mentioned above.

    Which prevents values passed per ->execute([...]) to always show up as strings.

  2. Switch to manual ->bindValue(..., ..., PDO::PARAM_INT) parameter population.

    Which however is less convenient than an ->execute list[].

  3. Simply make an exception here and just interpolate plain integers when preparing the SQL query.

     $limit = intval($limit);
     $s = $pdo->prepare("SELECT * FROM tbl LIMIT {$limit}");
    

    The casting is important. More commonly you see ->prepare(sprintf("SELECT ... LIMIT %d", $num)) used for such purposes.

  4. If you're not using MySQL, but for example SQLite, or Postgres; you can also cast bound parameters directly in SQL.

     SELECT * FROM tbl LIMIT (1 * :limit)
    

    Again, MySQL/MariaDB don't support expressions in the LIMIT clause. Not yet.

Community
  • 1
  • 1
mario
  • 144,265
  • 20
  • 237
  • 291
  • 1
    I would have used sprintf() with %d for 3, I'd say it's a bit more stable then with the variable. – hakre Mar 29 '15 at 11:18
  • Yeah, the varfunc cast+interpolation isn't the most practical example. I'd often use my lazy `{$_GET->int["limit"]}` for such cases. – mario Mar 30 '15 at 23:31
  • Hello Mario, how you going? I got one similar question about PDO Limit Placeholders, is it possible to use the named placeholder to Limit inside the execute? https://stackoverflow.com/questions/72081221/error-to-selecet-when-use-pdo-prepared-limit?noredirect=1#comment127363151_72081221 – Sophie May 02 '22 at 05:41
8

for LIMIT :init, :end

You need to bind that way. if you had something like $req->execute(Array()); it wont work as it will cast PDO::PARAM_STR to all vars in the array and for the LIMIT you absolutely need an Integer. bindValue or BindParam as you want.

$fetchPictures->bindValue(':albumId', (int)$_GET['albumid'], PDO::PARAM_INT);
joran
  • 169,992
  • 32
  • 429
  • 468
Nicolas Manzini
  • 8,379
  • 6
  • 63
  • 81
2

Since nobody has explained why this is happening, I'm adding an answer. The reason it is behaving this was is because you are using trim(). If you look at the PHP manual for trim, the return type is string. You are then trying to pass this as PDO::PARAM_INT. A few ways to get around this are:

  1. Use filter_var($integer, FILTER_VALIDATE_NUMBER_INT) to make sure you are passing an integer.
  2. As others said, using intval()
  3. Casting with (int)
  4. Checking if it is an integer with is_int()

There are plenty more ways, but this is basically the root cause.

Allan Pereira
  • 2,572
  • 4
  • 21
  • 28
1

bindValue offset and limit using PDO::PARAM_INT and it will work

Karel
  • 89
  • 1
  • 2
-1

//BEFORE (Present error) $query = " .... LIMIT :p1, 30;"; ... $stmt->bindParam(':p1', $limiteInferior);

//AFTER (Error corrected) $query = " .... LIMIT :p1, 30;"; ... $limiteInferior = (int)$limiteInferior; $stmt->bindParam(':p1', $limiteInferior, PDO::PARAM_INT);

-1

PDO::ATTR_EMULATE_PREPARES gave me the

Driver does not support this function: This driver doesn't support setting attributes' error.

My workaround was to set a $limit variable as a string, then combine it in the prepare statement as in the following example:

$limit = ' LIMIT ' . $from . ', ' . $max_results;
$stmt = $pdo->prepare( 'SELECT * FROM users WHERE company_id = :cid ORDER BY name ASC' . $limit . ';' );
try {
    $stmt->execute( array( ':cid' => $company_id ) );
    ...
}
catch ( Exception $e ) {
    ...
}
pringi
  • 3,987
  • 5
  • 35
  • 45
Fins
  • 15
  • 6
-1

There is alot going on between different versions of PHP and the oddities of PDO. I tried 3 or 4 methods here but could not get LIMIT working.
My suggestion is to use string formatting / concatination WITH an intval() filter:

$sql = 'SELECT * FROM `table` LIMIT ' . intval($limitstart) . ' , ' . intval($num).';';

It is very important to use intval() to prevent SQL injection, particularly if you are getting your limit from $_GET or the like. If you do that this is the easiest way to get LIMIT working.

There is alot of talk about 'The problem with LIMIT in PDO' but my thought here is that PDO params were never ment to be used for LIMIT since they will alway be integers a quick filter works. Still, it is a bit misleading since the philosophy has always been to not do any SQL injection filtering yourself but rather 'Have PDO handle it'.

Tycon
  • 123
  • 2
  • 12
-1

Try the following:

$sql2 = "SELECT  * FROM tab ORDER BY id DESC limit 2
ahmed
  • 9,071
  • 3
  • 9
  • 22
bricas30
  • 5
  • 5