Affects Version/s: 1.2.3
Fix Version/s: None
Environment:PHP 5.2, 5.3
'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()
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.
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:
SoftDeleteTest.php (copied from Doctrine manual)