silverstripe-framework: SQLSelect regression with subqueries
Module version(s) affected
5
Description
Regression within the SQLSelect class causes sub queries to be duplicated. This can be very easily reproduced in a blank silverstripe project.
How to reproduce
The regression itself:
Creating the select statement
$sql = SQLSelect::create('*', '( SELECT DISTINCT "SiteTree"."ClassName" FROM "SiteTree" ) as FINAL');
Output
SELECT *
FROM ( SELECT DISTINCT "SiteTree"."ClassName" FROM "SiteTree" ) as FINAL AS "( SELECT DISTINCT
SiteTree.ClassName FROM SiteTree ) as FINAL"
Note that the subquery is being duplicated.
Possible Solution
no response
Additional Context
I noticed that the issue cannot be reproduced when double quotations (") are left out of the query. Following the same reproduction steps WITHOUT the double quotations results in the correct output:
$sql = SQLSelect::create('*', '( SELECT DISTINCT SiteTree.ClassName FROM SiteTree ) as FINAL');
Output
SELECT *
FROM ( SELECT DISTINCT SiteTree.ClassName FROM SiteTree ) as FINAL
The reproduction code is an MVP, our actual use case is a little bit more complex with additional where statements, and froms. This worked just fine in silverstripe v4. A closer reproduction would be something like
$siteTree = SiteTree::get();
$query = $siteTree->dataQuery()->sql();
$finalQuery = SQLSelect::create('*', '(' . $query . ') as FINAL');
Debug::dump($finalQuery->sql());
Output (Note the duplication after the aliasing "AS FINAL AS “…”):
SELECT *
FROM (SELECT DISTINCT "SiteTree_Live"."ClassName", "SiteTree_Live"."LastEdited",
"SiteTree_Live"."Created", "SiteTree_Live"."CanViewType", "SiteTree_Live"."CanEditType",
"SiteTree_Live"."Version", "SiteTree_Live"."URLSegment", "SiteTree_Live"."Title",
"SiteTree_Live"."MenuTitle", "SiteTree_Live"."Content", "SiteTree_Live"."MetaDescription",
"SiteTree_Live"."ExtraMeta", "SiteTree_Live"."ShowInMenus", "SiteTree_Live"."ShowInSearch",
"SiteTree_Live"."Sort", "SiteTree_Live"."HasBrokenFile", "SiteTree_Live"."HasBrokenLink",
"SiteTree_Live"."ReportClass", "SiteTree_Live"."ParentID", "SiteTree_Live"."ID",
CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName"
ELSE 'SilverStripe\\CMS\\Model\\SiteTree' END AS "RecordClassName"
FROM "SiteTree_Live"
ORDER BY "SiteTree_Live"."Sort" ASC) as FINAL AS "(SELECT DISTINCT SiteTree_Live.ClassName,
SiteTree_Live.LastEdited, SiteTree_Live.Created, SiteTree_Live.CanViewType,
SiteTree_Live.CanEditType, SiteTree_Live.Version, SiteTree_Live.URLSegment, SiteTree_Live.Title,
SiteTree_Live.MenuTitle, SiteTree_Live.Content, SiteTree_Live.MetaDescription,
SiteTree_Live.ExtraMeta, SiteTree_Live.ShowInMenus, SiteTree_Live.ShowInSearch, SiteTree_Live.Sort,
SiteTree_Live.HasBrokenFile, SiteTree_Live.HasBrokenLink, SiteTree_Live.ReportClass,
SiteTree_Live.ParentID, SiteTree_Live.ID,
CASE WHEN SiteTree_Live.ClassName IS NOT NULL THEN SiteTree_Live.ClassName
ELSE 'SilverStripe\\CMS\\Model\\SiteTree' END AS RecordClassName
FROM SiteTree_Live
ORDER BY SiteTree_Live.Sort ASC) as FINAL"
Changing the alias to be resolved as the array index, like
SQLSelect::create('*', ['Final' => '(' . $siteTreeSql . ')']);
resolves the same way.
Validations
- Check that there isn’t already an issue that reports the same bug
- Double check that your reproduction steps work in a fresh installation of
silverstripe/installer(with any code examples you’ve provided)
Pull requests
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Comments: 16 (14 by maintainers)
Great investigation.
Yup sounds like a bug to me. IMO reverting isn’t really viable, so some variation of option 2 sounds good. Haven’t fully wrapped my head around it so I’m not sure if there are edge cases where that’ll cause a new problem, but it still sounds like the better way forward even with that possibility.
PR merged. Will be automatically tagged by GitHub Actions.
@michalkleiner I don’t think so … or more accurately, it’s not introducing any SQL Injection vector that wouldn’t be there already.
SQLConditionalExpression::addFrom()is a low level API. Very few developers will be interacting with this method directly.setFromandaddFrommake no effort to validate their inputs. If you’re interacting withSQLConditionalExpressionor its descendants, you are more or less writing raw SQL. I think it’s fair to put it back on the developer to sanitised their inputs in this case.I’ve been asked to look into fixing this at my new job. So I’ll have glance to see how difficult - or not difficult it is.
I’m going to move that into the “additional context” section then - since that’s what you’re saying it is.
@michalkleiner
It’s mentioned just above the example,… my mistake I’ve updated it. An important note right below is that it’s not exactly a “possible solution” but rather something I discovered which may help debug the issue.
We’re on version 4.13.42 of the framework I believe. Upgrading straight to 5.2