CODEWITHSUNDEEP
symfonydql
user8238012
user8238012

Reputation:

DQL to ORM - Query Builder Symfony

I have in fact 2 related issues. I made a DQL request (she works fine) but my boss told me it was a security risk and I had to use ORM instead. I found this strange and I wanted to know why but I wasn't able to find anything telling me why it's a security risk.

So my first question would be do you know why ?

Here is the request:

    $sql="SELECT v.id,v.codeVague, v.date_fin_ultime,c.distance,p.adresse1,p.code_postal,p.ville,m
          FROM McInvestigatorBundle:Vague v
                    INNER JOIN McInvestigatorBundle:Enquete e WITH e.vague_id = v.id
                    INNER JOIN McInvestigatorBundle:Mission m WITH m.id = e.mission_id
                    INNER JOIN McInvestigatorBundle:Contrat c WITH c.id = m.contrat
                    INNER JOIN McInvestigatorBundle:User u WITH u.enqueteur_id = e.enqueteur_id
                    INNER JOIN McInvestigatorBundle:PointDeVente p WITH p.id = e.pdv_id
          WHERE v.codeVague ='".$wave_code."'
                    AND e.type_id =".$type_id."
                    AND m.enqueteur_id=".$enq_id."
                    ORDER BY m.date_rea_prev ASC";
    $results= $em->createQuery($sql)->getResult();

My second issue is the "most important", I need to translate my request to use ORM as my boss said. I thought about using query builder, but I'm not even sure query builder is ORM. Is it ? If it's not, what's the way I need to use to get full ORM ?

Upvotes: 0

Views: 184

Answers (2)

Veve
Veve

Reputation: 6748

1) Your SQL query can be vulnerable to SQL injection if the parameters passed to your SQL query aren't correctly sanitized.

2) Look here for a complete description of DoctrineQueryLanguage, and here is your SQL query in DQL:

$sql = "SELECT v.id,v.codeVague, v.date_fin_ultime,c.distance,p.adresse1,p.code_postal,p.ville,m
          FROM McInvestigatorBundle:Vague v
                    INNER JOIN McInvestigatorBundle:Enquete e WITH e.vague_id = v.id
                    INNER JOIN McInvestigatorBundle:Mission m WITH m.id = e.mission_id
                    INNER JOIN McInvestigatorBundle:Contrat c WITH c.id = m.contrat
                    INNER JOIN McInvestigatorBundle:User u WITH u.enqueteur_id = e.enqueteur_id
                    INNER JOIN McInvestigatorBundle:PointDeVente p WITH p.id = e.pdv_id
          WHERE v.codeVague = :wave_code
                    AND e.type_id = :type_id
                    AND m.enqueteur_id = :enq_id
                    ORDER BY m.date_rea_prev ASC";
$results = $em->createQuery($sql)
               ->setParameter('wave_code', $wave_code)
               ->setParameter('type_id', $type_id)
               ->setParameter('enq_id', $enq_id)
               ->getResult()
;

I also suggest you study the QueryBuilder, it will help you to construct queries.

Upvotes: 0

Finwe
Finwe

Reputation: 6725

There are indeed security issues in your DQL - you should parametrize your query, otherwise it might be prone to SQL injection attacks.

'WHERE v.codeVague = :wave_code
    AND e.type_id = :type_id
    AND m.enqueteur_id = :enq_id
ORDER BY m.date_rea_prev ASC';

$results = $em->createQuery($sql)
    ->setParameters([
        'wave_code' => $wave_code,
        'type_id' => $type_id,
        'enq_id' => $enq_id,
    ])->getResult();

Are the WITH e.vague_id = v.id statements in the DQL valid and really needed? If your associations are defined correctly, Doctrine should determine foreign keys by itself.

Other than that, I do not see any security issues in using DQL. The QueryBuilder won't help in your case as it just provides chainable methods to build the same DQL query as you wrote by hand.

I do not understand your boss' request to "use ORM instead" either. You can ask for clarification, I guess.

Upvotes: 1

Related Questions