[DBAL-164] Quoting allows SQL injections Created: 10/Sep/11  Updated: 17/Apr/14  Resolved: 13/Sep/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.1.2
Fix Version/s: 2.1.3
Security Level: All

Type: Bug Priority: Major
Reporter: Oliver Mueller Assignee: Guilherme Blanco
Resolution: Fixed Votes: 0
Labels: None
Environment:

OCI8 Driver
IBMDB" Driver



 Description   

$test = "foo ' bar";
$quoted = $conn->quote( $test );
echo $quoted;

RESULT: 'foo ' bar'
EXPECTED: 'foo \' bar'



 Comments   
Comment by Guilherme Blanco [ 13/Sep/11 ]

Fixed in https://github.com/doctrine/dbal/commit/82cc921447fde697bf3d9f5285d0f0b8587303d8

Comment by Benjamin Eberlei [ 25/Sep/11 ]

Backported to 2.0.9

Comment by Benjamin Eberlei [ 25/Sep/11 ]

Fix was modified to use the Zend Framework code for quoting OCI input: https://github.com/doctrine/dbal/commit/97638edc0fef0e08ce7db631eb130fde950844d7

This code is now in DBAL 2.1.4 and 2.0.9 and i have added some tests to very some simple SQL Injection vectors don't work on any supported platform.





[DBAL-102] SQLSRV PDO doesn't support DateTime as a PHP Object Created: 19/Mar/11  Updated: 15/Mar/13  Resolved: 14/Sep/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.1
Fix Version/s: 2.1.3

Type: Bug Priority: Major
Reporter: Aaron DM Assignee: Guilherme Blanco
Resolution: Fixed Votes: 0
Labels: None
Environment:

Windows 7, Microsoft SQL Server 2008, PHP SqlSrv Driver 2.0, PHP 5.3.6



 Description   

I have created an entity with has a datetime column

Entity.php
    /**
     * @orm:Column(name="deleteAt", type="datetime")
     */
    protected $deletedAt;

    /**
     * Get DeletedAt
     * @return	\DateTime	
     */
    public function getDeletedAt() {
    	return $this->deletedAt;
    }
    
    /**
     * Set DeletedAt
     * @param	\DateTime		$deletedAt
     */
    public function setDeletedAt(\DateTime $deletedAt) {
        $this->deletedAt = $deletedAt;
    }

This should work, however I get this error when trying to set a "deleteAt" date

$this->setDeletedAt(new \DateTime());
SQLSTATE[22007]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string.

This should work afaik.



 Comments   
Comment by Benjamin Eberlei [ 21/Mar/11 ]

Are yo using columns of type "datetime" or "datetime2" in MSSQL? The former is not supported.

Comment by Aaron DM [ 21/Mar/11 ]

I did not know datetime was not supported. I am using the column type "datetime"

Comment by Benjamin Eberlei [ 21/Mar/11 ]

In this case you have to add your own datetime type and change the timestamp format, or use the VarDateTime instead by calling "overrideType". Have a look at the Doctrine\DBAL\Types folder, escpecially the Type.php, DateTimeType.php and VarDateTimeType.php

Comment by Aaron DM [ 29/Mar/11 ]

I have now tried this with the column type "datetime2", which creates the following date format in the database
"2011-03-27 06:07:00.000000"

And I am still getting the same error
"SQLSTATE[22007]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string."

P.S. this time I tried doing a select
WHERE deletedAt > (datetime)

Comment by Benjamin Eberlei [ 29/Mar/11 ]

Assigned to juokaz

Comment by Aaron DM [ 29/Mar/11 ]

When i ran profile, this is what I see

" AND (a0_.deleted_at <= @P3) ORDER BY a0_.deleted_at DESC','810678','Object','Object'"

Expliclitly converting the DateTiem to a string works

$query->setParameter("deletedAt", $deletedAt->format("Y-m-d H:i:s.u"));

Comment by Juozas Kaziukenas [ 29/Mar/11 ]

Can you give a full code which you use to query this (or the smallest allowing to replicate). I can't see how DateTime object gets passed as a 'Object' to the query, this is handled by DBAL type system, not by mssql platform, which only specifies the date and/or time format.

Comment by Benjamin Eberlei [ 30/Mar/11 ]

Does the error happen during a DQL or during flush (UPDATE/INSERT) ?

Comment by Aaron DM [ 30/Mar/11 ]

https://gist.github.com/6b2a8b53ece6e75abf20

The code there works (its not fully complete, but I tested the statements with the same Entity and etc and I am unable to successfully query the database).

Looks like it doesn't work even for a simple select. However if I do retrieve an entry, the getCreatedAt() does return a DateTime object correctly.

Comment by Guilherme Blanco [ 13/Sep/11 ]

Is this issue still valid with new implementation of ParameterTypeInferer in ORM?

It seems it solved this issue.

Comment by Guilherme Blanco [ 14/Sep/11 ]

Closing since no response was given.

Comment by Michał Banaś [ 15/Mar/13 ]

Why is it close ? After one day of waiting for response?
Problem is not solved. It exist on ether datetime and datetime2 TSQL types.
And it is easy to check if you have sqlserver databes.
PLease repoen this issue.

Comment by Marco Pivetta [ 15/Mar/13 ]

Michał Banaś this actually works with the current implementation of the schema tools. Consider implementing additional formats a custom DBAL type instead.





[DBAL-160] Github-PR-51 by jheth: OCI8Statement doesn't support first query parameter being null Created: 30/Aug/11  Updated: 30/Aug/11  Resolved: 30/Aug/11

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: 2.1.3
Security Level: All

Type: Improvement Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of jheth:

Url: https://github.com/doctrine/dbal/pull/51

Message:

Patch for OCI8Statement to support param[0] being null. Switched isset to array_key_exists. Added test that demonstrates the bug and confirms the fix.






Generated at Mon Apr 21 05:44:48 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.