带有消息SQLSTATE [42000]的未捕获异常'PDOException'：语法错误或访问冲突：1064

That is my query:

那是我的疑问:

 public function getAllServices($start, $limit)
{
    $services = array();

    $q = $this->init()->prepare('SELECT id, service_title, time_add FROM services ORDER BY id DESC LIMIT :start, :limit');
    $q->execute(array(":start" => $start, ":limit" => $limit));

    while ($values = $q->fetchAll(PDO::FETCH_ASSOC))
        $services[] = $values;

    return $services;
}

Error message:

Fatal error: Uncaught exception 'PDOException' with message '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 ''0', '10''

致命错误:带有消息'SQLSTATE [42000]的未捕获异常'PDOException':语法错误或访问冲突:1064 SQL语法中有错误;查看与您的MySQL服务器版本对应的手册,以便在“0”,“10”附近使用正确的语法

3 个解决方案

#1

Based on the syntax error message, I would say that it's quoting the integers passed to LIMIT, which is not allowed.

根据语法错误消息,我会说它引用传递给LIMIT的整数,这是不允许的。

#2

Just to add some additional information to this - I'd had a similar problem and had been searching Google for a fix for a while. What I've discovered is that PDO parameterisation has a problem with repeated parameters:

只是为此添加一些额外的信息 - 我遇到了类似的问题,并且一直在谷歌搜索修复程序。我发现PDO参数化有重复参数的问题:

$q = $this->init()->prepare("
    (SELECT * FROM my_table WHERE date > :date LIMIT :limit)
    UNION
    (SELECT * FROM their_table WHERE date > :date LIMIT :limit)
");
$q->bindValue(':date', $somedate, PDO::PARAM_STR);
$q->bindValue(':limit', $limit, PDO::PARAM_INT);
$q->execute();

The resulting query from this code will NOT escape the first :limit but WILL incorrectly escape the second :limit, eg:

来自此代码的结果查询将不会转义第一个:limit但会错误地转义第二个:limit,例如:

(SELECT * FROM my_table WHERE date > '2014-04-14' LIMIT 20)
UNION
(SELECT * FROM their_table WHERE date > '2014-04-14' LIMIT '20')

The reason why you see LIMIT being mentioned in a lot of these similar issues is that providing an escaped integer for field comparisons isn't going to break anything in MySQL, but doing the same thing for LIMIT does.

您在很多这些类似问题中看到LIMIT的原因是为字段比较提供转义整数不会破坏MySQL中的任何内容,但对LIMIT做同样的事情。

So while this doesn't specifically answer the OP's issue I imagine many people like myself will end up at this post with the same problem I had.

因此,虽然这并没有具体回答OP的问题,但我想像我这样的很多人最终都会遇到同样的问题。

I don't currently have a tidy solution for this, so I've taken to using unique parameters such as :limit1 and :limit2. Perhaps someone can suggest a better solution?

我目前没有一个整洁的解决方案,所以我采用了独特的参数,如:limit1和:limit2。也许有人可以提出更好的解决方案?

#3

Solution by OP.

OP解决方案。

That fixed the problem:

这解决了这个问题:

$q = $this->init()->prepare("SELECT id, service_title, time_add FROM services ORDER BY id DESC LIMIT :start, :limit");
    $q->bindParam(':start', $start, PDO::PARAM_INT);
    $q->bindParam(':limit', $limit, PDO::PARAM_INT);
    $q->execute();

#1