PDO adding quotes and causing syntax error in MySQL

PHP
1

I have played with many permutations trying to get rid of the error, and finally turned on MySQL logging.

I my PHP I am getting this error:

Error selecting photographs: SQLSTATE[42000]: Syntax error or access violation: 1064 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 ‘‘4’’ at line 1

The message is correct. The statement being executed by MySQL, according to the log, is:

SELECT * FROM photographs LIMIT ‘4’

The quotes around the 4 is the syntax error.

Now, in PHP, I am processing a POST with a function and the simplest code to replicate the error is:



function updatephotos($dbh, $x) {

  $number = $x['number'] ;

  $sql = 'SELECT * FROM photographs LIMIT :number';
              
  $stmt = $dbh->prepare($sql);

  try {
        $stmt->bindParam( ':number', $number, PDO::PARAM_INT );   
        $stmt->execute();   
      } catch (PDOException $e) {
            return 'Error selecting photographs: ' . $e->getMessage();
    }
  return "test worked" ;
}

$dbh is the handle to the DB created in the caller
$x is $_POST

If I echo $number I get: 4. No quotes.

I have tried using bindValue instead of bindParam.

I have tried not using PDO::PARAM_INT, and also using PDO::PARAM_STR

No matter what I do I ‘4’, instead of 4

I have a LAMP box. Ubuntu 11.04

Latest version of Apache, PHP and MySQL

Any and all help appreciated!

2

This seems to be a known issue. Hopefully this bug report will be of some help.
Bug #40740 PDO::execute() errors when parameters are used in LIMIT clause

The conversation continues in:
[url=“https://bugs.php.net/bug.php?id=44639”]
Bug #44639 PDO quotes integers in prepared statement

This may be a work-around, but have you tried casting the number instead?

$stmt->bindParam(':number', (int)$number);

It’s not ideal, but until PHP’s PDO resolves the issue, that may have to be the way to go.

3

Well, I have never come across that problem before because I did not consider the LIMIT clause to be a parameter.

That could well be because of the hours I initially lost with PDO trying to figure out why this did not work: :wink:


"Select name from dogs where ? = ?";
$stmt->bindParam(1, 'breed');
$stmt->bindParam(2, 'husky');

This will work just as well, although I admit it is wandering away from the purity of prepared statements.


// critical:
// typecast to integer here to protect your db
$number = (int)$x['number'] ;

// concat to the sql statement string
$sql = 'SELECT * FROM photographs LIMIT ' . $number ;
4

Thank you for the replies.

The type casting works, and it seems a harmless work around.

Waking up to a solution makes for a happy day. :slight_smile:

Thank you!