[DC-944] Precedence problem in SQL generation allows bypass of pending joins Created: 03/Dec/10  Updated: 17/Apr/14

Status: Open
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.2.3
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Walter Hop Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None

PHP 5.2, 5.3

Attachments: File Query.pendingjoin.diff    


'Pending join conditions' are used by listeners to inject extra SQL conditions into a query. They are often used to add basic constraints on every query. An example is the bundled SoftDelete template. Its listener adds extra constraints such as s.deleted_at IS NULL to a query, to make sure that deleted rows are never retrieved on a query.

However, in the emitted SQL, Doctrine_Query does not use parentheses to group normal SQL conditions together. The pending join condition is simply added to the string without encapsulating existing expressions. This makes it possible to bypass the pending join conditions entirely by using the OR operator.


For instance, the following query exhibits this problem:

$query = Doctrine_Query::create()
->where("name=?", "faulty")
->orWhere("name=?", "faulty");

This query emits the following SQL:

SELECT s.name AS s_name, s.deleted_at AS s_deleted_at FROM soft_delete_test s WHERE (s.name = 'faulty' OR s.name = 'faulty' AND (s.deleted_at IS NULL))

which returns also a deleted row.

Expected behavior

One would expect the pending join conditions always to hold, and to have precedence over regularly added SQL conditions. This could be accomplished in the most simple fashion by:

SELECT s.name AS s_name, s.deleted_at AS s_deleted_at FROM soft_delete_test s WHERE ( ( s.name = 'faulty' OR s.name = 'faulty' ) AND (s.deleted_at IS NULL));

As the existing expressions are now encapsulated by parentheses, it is no longer possible to bypass the pending join conditions injected by the query listener.

Full test case details:


create database softdelete;
grant all privileges on softdelete.* to softdelete@localhost identified by 'uahwqeruwer';

use softdelete;
CREATE TABLE soft_delete_test (name VARCHAR(255), 
    deleted_at DATETIME DEFAULT NULL, 

insert into soft_delete_test values ('fine', null);
insert into soft_delete_test values ('faulty', now());



require "./1.2.3/lib/Doctrine.php";

spl_autoload_register(array('Doctrine', 'autoload'));

require "SoftDeleteTest.php";

$conn = Doctrine_Manager::connection("mysql://softdelete:uahwqeruwer@localhost/softdelete");
$conn->setAttribute(Doctrine::ATTR_USE_DQL_CALLBACKS, true);

$query = Doctrine_Query::create()
    ->where("name=?", "faulty")
    ->orWhere("name=?", "faulty");

$found = $query->execute();
foreach ($found as $f) {
    echo "ERROR! Found a deleted row: $f->name\n";
echo "Done.\n";

SoftDeleteTest.php (copied from Doctrine manual)


class SoftDeleteTest extends Doctrine_Record
    public function setTableDefinition()
        $this->hasColumn('name', 'string', null, array(
                'primary' => true

    public function setUp()

Comment by Walter Hop [ 03/Dec/10 ]

Fixing quote formatting

Comment by Walter Hop [ 03/Dec/10 ]

Final formatting fixes.

Generated at Sun Aug 30 22:52:16 EDT 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.