使用Doctrine QueryBuilder时变量未定义

Question

I want to make alternative way for making stored procedures by using Doctrine but I am stuck, could any one help me?

Example stored procedure to be formed:

CREATE PROCEDURE catalog_get_department_details(IN DepartmentName)
    BEGIN
    SELECT name, description
    FROM
    department
    WHERE name = name;

Departments Entity:

/**
 * @ORM\Entity(repositoryClass="AppBundle\Repository\departmentsRepository")
 * @ORM\Table(name="departments")
 */
class departments
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     * @ORM\Column(type="integer")
     */
    private $department_id;

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

    /**
     * @ORM\Column(type="string", nullable=true)
     */
    private $description;

    /**
     * @ORM\OneToMany(targetEntity="categories",mappedBy="departments")
     */
    private $categories;

    function __construct()
    {
        $this->categories = new ArrayCollection();
    }

    public function getDepartmentId()
    {
        return $this->department_id;
    }

    public function setDepartmentId($department_id)
    {
        $this->department_id = $department_id;
    }

    /**
     * @return mixed
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * @param mixed $name
     */
    public function setName($name)
    {
        $this->name = $name;
    }

    /**
     * @return mixed
     */
    public function getDescription()
    {
        return $this->description;
    }

    /**
     * @param mixed $description
     */
    public function setDescription($description)
    {
        $this->description = $description;
    }

The scenario is when the route is /index/departmentname/Regional ; my DefaultController will capture Regional as parameter

DefaultController:

class DefaultController extends Controller
{
    /**
     * @Route ("/index/department/{department_name}")
     */
    function departmentAction($department_name)
    {
        // accessing departmentsRepository 
        $categoriesRepository = $this->getDoctrine()->getManager()
            ->getRepository('AppBundle:departments');
        $categoriesRepository->getDepartmentDetails($department_name);
    }

departmentsRepository:

class departmentsRepository extends \Doctrine\ORM\EntityRepository
{
    function getDepartmentDetails($departmentName)
    {
        $em=$this->getEntityManager()->getRepository('AppBundle:departments');
        $qb=$em->createQueryBuilder('dep');
        $qb->select('dep.name','dep.description');
        $qb->where("dep.name=$departmentName");

When I call var_dump($qb->getDQL());die; it shows me exactly what I want:

SELECT dep.name, dep.description FROM AppBundle\Entity\departments dep WHERE dep.name=Regional

I then execute it by calling

$qb->getQuery()->execute();

But I receive the following error:

[Semantical Error] line 0, col 86 near 'Regional': Error: 'Regional' is not defined.

Any idea what I'm doing wrong?

Answer 1

Your dep.name value isn't being escaped. You would expect the query to look like this instead:

WHERE dep.name='Regional'

But what you should be doing, and what is safer, is binding that to a parameter, like so:

$em = $this->getEntityManager()->getRepository('AppBundle:departments');
$qb = $em->createQueryBuilder('dep');
$qb->select('dep.name', 'dep.description');
$qb->where("dep.name = :departmentName");
$qb->setParameter('departmentName', $departmentName);

Doctrine will handle the escaping for you, and safely. This also allows you to avoid SQL injection attacks. Also since you are already in your departments repository you should be able to use the _em value as a shortcut, and also not have to re-specify the departments entity, like so:

$qb = $this->_em->createQueryBuilder('dep');
$qb->select('dep.name', 'dep.description');
$qb->where("dep.name = :departmentName");
$qb->setParameter('departmentName', $departmentName);

Side not, in your controller action you are calling the repository function but not actually saving the results to any variable.