Uncaught exception 'PDOException' with message SQLSTATE[42000]: Syntax error or access violation: 1064

Question

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''

It appears you haven't made your connection variable global. — Enijar, Jan 22 '14 at 23:20
I try to change :start and :limit with other name but don't have result — Вълко Калъчев, Jan 22 '14 at 23:23
Well, I for one have learned something today @ВълкоКалъчев --- Glad to see that it was resolved. You know that you can post your own answer. — Funk Forty Niner, Jan 22 '14 at 23:33

score 0 · Answer 1 · answered Apr 16 '14 at 03:20

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:

$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:

(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.

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.

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?

score 0 · Answer 2 · answered Apr 22 '18 at 18:18

Solution by 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();

jeremycole · Answer 3 · 2014-01-23T00:12:53.603

-1

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

edited Jan 23 '14 at 00:12

answered Jan 22 '14 at 23:25

jeremycole

2,741
12
15

That's the problem but I don't think this solution helps. The default bind type is `PDO::PARAM_STR` – Phil Jan 22 '14 at 23:44
@Phil Cool, if you propose an answer with the right syntax for that I'll gladly vote it up. :) – jeremycole Jan 23 '14 at 00:04

Uncaught exception 'PDOException' with message SQLSTATE[42000]: Syntax error or access violation: 1064

3 Answers3