Why “SQLSTATE[42000]: Syntax error or access violation”?

1
votes

I would like to run this Doctrine Query Builder query:

$repository = $this->getDoctrine()->getRepository('MyBundle:Usage');
$usage_q = $repository->createQueryBuilder('u')->where('u.var = \'warranty\'');
$usage_result = $usage_q->getQuery()->getSingleResult();

But I always get:

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 'Usage u0_ WHERE u0_.var = 'warranty'' at line 1

This is my Usage entity:

<?php

namespace Herbanist\AdminBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Usage
 *
 * @ORM\Table()
 * @ORM\Entity
 */
class Usage
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="var", type="string", length=20)
     */
    private $var;

    /**
     * @var string
     *
     * @ORM\Column(name="value", type="text")
     */
    private $value;


    public function getId()
    {
        return $this->id;
    }

    public function setVar($var)
    {
        $this->var = $var;

        return $this;
    }

    public function getVar()
    {
        return $this->var;
    }

    public function setValue($value)
    {
        $this->value = $value;

        return $this;
    }

    public function getValue()
    {
        return $this->value;
    }
}

Usage table:

mysql> select * from `Usage`;
+----+----------+-------+
| id | var      | value |
+----+----------+-------+
|  1 | warranty | 0     |
+----+----------+-------+
2
phpmysqlsymfonydoctrine-orm

2 Answers

3
votes

It happens because 'USAGE' is an MySQL reserved word. So I had to rename Usage table and Usage entity class to something else.

1
votes

Escaping single quotes with a back slash only works if the string is inside of double quotes.

usage_q = $repository->createQueryBuilder('u')->where("u.var = 'warranty'");

You might be better off taking advantage of parameters. The quote problem goes away completely.

usage_q = $repository->createQueryBuilder('u')->where('u.var = :something');
usage_q->setParameter('something','warranty');