Limit is treated as a string

[jerome2710]

As the statement is executed as $stmt->execute($this->getValues()), any integers will be added to the parameters as strings. This results in an SQL-error for LIMIT, as this needs to be an integer.

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 ''999'' at line 1

[jerome2710]

Any updates regarding this matter?

[kwhat]

I'll take a look this week. Please bug me if I don' get back to you by Friday.

[kwhat]

Can you provide code to duplicate the issue.

[jerome2710]

Can you provide code to duplicate the issue.

Sure, but it is nothing special:

$result = $this->database
    ->select()
    ->from('table-name')
    ->limit(5)
    ->execute();

Results in:

SERVER_ERROR 500 - 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 ''5'' at line 1

Probably because executing the statement while passing parameters will treat them as:

@param array $params [optional]
* 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.

While the limit should be an integer.

[kwhat]

I am not sure how you did this, I cannot duplicate the issue with MariaDB 10.2. Do you have a specific database configuration that is causing this error? I am going to need a lot more info about how this is happening.

$db = new Database(
    'mysql:host=127.0.0.1;dbname=testing;charset=utf8mb4',
    'root',
    null
);

var_dump(
    $db->select(['id'])
        ->from('test')
        ->limit(new Limit(10))
        ->__toString()
); 

// string(30) "SELECT id FROM tbluser LIMIT ?"

$result = $db->select(['id'])
    ->from('test')
    ->limit(new Limit(10))
    ->execute();

var_dump($result->fetchAll());

/*
array(10) {
  [0]=>
  array(1) {
    ["id"]=>
    int(31)
  }
  [1]=>
  array(1) {
    ["id"]=>
    int(90090)
  }
  [2]=>
  array(1) {
    ["id"]=>
    int(1)
  }
  [3]=>
  array(1) {
    ["id"]=>
    int(11)
  }
  [4]=>
  array(1) {
    ["id"]=>
    int(21)
  }
  [5]=>
  array(1) {
    ["id"]=>
    int(41)
  }
  [6]=>
  array(1) {
    ["id"]=>
    int(51)
  }
  [7]=>
  array(1) {
    ["id"]=>
    int(61)
  }
  [8]=>
  array(1) {
    ["id"]=>
    int(71)
  }
  [9]=>
  array(1) {
    ["id"]=>
    int(81)
  }
}
*/

[kwhat]

I figured it out... somehow you've managed to enable PDO::ATTR_EMULATE_PREPARES.

[kwhat]

You can try this patch, I am not sure this wont cause other interesting side effects with decimal or enum types.

diff --git a/src/AbstractStatement.php b/src/AbstractStatement.php
index e644774..e4b5d64 100644
--- a/src/AbstractStatement.php
+++ b/src/AbstractStatement.php
@@ -33,7 +33,16 @@ abstract class AbstractStatement implements StatementInterface
     {
         $stmt = $this->dbh->prepare($this->__toString());
         if ($stmt !== false) {
-            $stmt->execute($this->getValues());
+            foreach ($this->getValues() as $i => $value) {
+                $type = PDO::PARAM_STR;
+                if (is_int($value)) {
+                    $type = PDO::PARAM_INT;
+                }
+
+                $stmt->bindParam($i + 1, $value, $type);
+            }
+
+            $stmt->execute();
         }
 
         return $stmt;