[#DBAL-267] DBAL\Connection doesn't escape SQL keywords - Doctrine Project

Details

Type: Bug
Status: Resolved
Priority: Major
Resolution: Invalid
Affects Version/s: 2.2
Fix Version/s: None
Component/s: None
Security Level: All
Labels:
None

Description

I'm using DBAL Connection's "insert" and "update" methods to manipulate my database.

Both those methods doesn't escape reserved SQL keywords appropriately. See: http://www.doctrine-project.org/api/dbal/2.2/source-class-Doctrine.DBAL.Connection.html#493

For example the following method call will raise an Exception (SQL error):

$Connection->insert('some_table', array(
'from' => 'foo',
'to' => 'bar'
));

Because the "from"-key is a reserved SQL keyword.

Database abstraction layer such as DBAL MUST consider such situations and act appropriately (i.e. escape field names with regulations specified by syntax of used RDBMS).

In MySQL i have to do this manually before calling "insert" or "update":
$key = '`' . $key . '`';
And this is really bad practice, cause it's platform-depended solution (works only for MySQL).

I hope you will find my words reasonable. it's important for developer to be able to use any key names he seems necessary (even if it's reserved by SQL itself). Sometimes developer doesn't even have a choice (e.g. when he receives database from third-party and can't change it's structure).

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

ross neacoders added a comment - 05/May/12 6:07 PM

Was discussed here:

http://www.doctrine-project.org/jira/browse/DBAL-40

Implicit quoting will not be supported in DBAL.

If you are ok with explicit quoting, this is supported in ORM.
Example - notice the backticks:

/**
 * MMP\GlobalServicesBundle\Entity\Photographers
 *
 * @ORM\Table(name="`Photographers`")
 * @ORM\Entity
 */
class Photographers
{
    /**
     * @var guidextended $photographerguid
     *
     * @ORM\Column(name="`PhotographerGUID`", type="guid", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="NONE")
     */
    private $photographerguid;

    /**
     * @var integer $photographerid
     *
     * @ORM\Column(name="`PhotographerID`", type="integer", nullable=false)
     */
    private $photographerid;

    /**
     * @var string $name
     *
     * @ORM\Column(name="`Name`", type="string", length=50, nullable=false)
     */
    private $name;
...

Example call:

        $id = '050de0bf-20ea-4e20-b94c-aea6a5e9e6ed';

        $repository = $this->getDoctrine()
        ->getRepository('MMPGlobalServicesBundle:Photographers');

        $qb = $repository->createQueryBuilder('p');
        $query = $qb->select('p.name, p.surname')->where("p.photographerguid = '$id'");
         print_r($query->getQuery()->getSQL());

Output:

SELECT p0_."Name" AS name0, p0_."Surname" AS surname1 FROM "Photographers" p0_ WHERE p0_."PhotographerGUID" = '050de0bf-20ea-4e20-b94c-aea6a5e9e6ed'

Show

ross neacoders added a comment - 05/May/12 6:07 PM Was discussed here: http://www.doctrine-project.org/jira/browse/DBAL-40 Implicit quoting will not be supported in DBAL. If you are ok with explicit quoting, this is supported in ORM. Example - notice the backticks: /** * MMP\GlobalServicesBundle\Entity\Photographers * * @ORM\Table(name= "`Photographers`" ) * @ORM\Entity */ class Photographers { /** * @ var guidextended $photographerguid * * @ORM\Column(name= "`PhotographerGUID`" , type= "guid" , nullable= false ) * @ORM\Id * @ORM\GeneratedValue(strategy= "NONE" ) */ private $photographerguid; /** * @ var integer $photographerid * * @ORM\Column(name= "`PhotographerID`" , type= "integer" , nullable= false ) */ private $photographerid; /** * @ var string $name * * @ORM\Column(name= "`Name`" , type= "string" , length=50, nullable= false ) */ private $name; ... Example call: $id = '050de0bf-20ea-4e20-b94c-aea6a5e9e6ed'; $repository = $ this ->getDoctrine() ->getRepository('MMPGlobalServicesBundle:Photographers'); $qb = $repository->createQueryBuilder('p'); $query = $qb->select('p.name, p.surname')->where( "p.photographerguid = '$id'" ); print_r($query->getQuery()->getSQL()); Output: SELECT p0_. "Name" AS name0, p0_. "Surname" AS surname1 FROM "Photographers" p0_ WHERE p0_. "PhotographerGUID" = '050de0bf-20ea-4e20-b94c-aea6a5e9e6ed'

Hide

Permalink

Benjamin Eberlei added a comment - 22/May/12 5:52 PM

See http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html for reasoning why we dont automatic identifier quoting.

You can do $conn->update(..., array('`quoted`' => $value));

Show

Benjamin Eberlei added a comment - 22/May/12 5:52 PM See http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html for reasoning why we dont automatic identifier quoting. You can do $conn->update(..., array('`quoted`' => $value));

DBAL\Connection doesn't escape SQL keywords

Details

Description

Activity

People

Dates