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)

Most upvoted comments

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.

setFrom and addFrom make no effort to validate their inputs. If you’re interacting with SQLConditionalExpression or 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.

You say this is a regression - what is the latest version you saw this working in?

We’re on version 4.13.42 of the framework I believe. Upgrading straight to 5.2