U4-9592 - Output from Umbraco.Core.Persistence.Database.SplitSqlForPaging contains invalid sql when order by columns are quoted.

Created by Søren Gregersen 02 Mar 2017, 21:55:04 Updated by Søren Gregersen 03 Mar 2017, 02:17:02

Your report will have a greater chance of being addressed if you can give us clear steps to reproduce the issue, please answer the following questions in as much detail as possible:

'''What did you do?''''

I called the SplitSqlForPaging-method with a sql-statement that contained a quoted column-identifier in the order by clause string count, select, orderBy; var sql = "SELECT * FROM [table1] ORDER BY [table1].[Id]"; Umbraco.Core.Persistence.Database.SplitSqlForPaging(sql, out count, out select, out orderBy);

A workaround is to not quote the columns in the ORDER BY, but it seems like a good practice to quote them, when using generated / generic code.

'''What did you expect to happen?'''

That the output would remove the "order by" from the count SQL, and the values of the 3 output strings would be: count = "SELECT COUNT() FROM [table1]": select = " FROM [table1] ORDER BY [table1].[Id] "; orderBy = "ORDER BY [table1].[Id] ";

'''What actually happened?'''

The values of the 3 output strings are: count = "SELECT COUNT() FROM [table1] ORDER BY [table1].[Id]"; select =" FROM [table1] ORDER BY [table1].[Id] "; orderBy = null;

It seems like the regex from the PetaPoco (https://github.com/CollaboratingPlatypus/PetaPoco/blob/master/PetaPoco/Utilities/PagingHelper.cs#L19) works better:

// peta Regex RegexOrderBy = new Regex(@"\bORDER\s+BY\s+(?!.?(?:)|\s+)AS\s)(?:((?>((?)|)(?<-depth>)|.?)(?(depth)(?!)))|[[]""\w\(\)\.])+(?:\s+(?:ASC|DESC))?(?:\s*,\s*(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\[\]""\w().])+(?:\s+(?:ASC|DESC))?)*", RegexOptions.RightToLeft | RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);

// umb Regex rxOrderBy = new Regex(@"\bORDER\s+BY\s+(?:((?>((?)|)(?<-depth>)|.?)(?(depth)(?!)))|[\w().])+(?:\s+(?:ASC|DESC))?(?:\s,\s*(?:((?>((?)|)(?<-depth>)|.?)(?(depth)(?!)))|[\w().])+(?:\s+(?:ASC|DESC))?)", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);

var sql = "SELECT COUNT(*) FROM [table] ORDER BY [table].[Id]"; RegexOrderBy.IsMatch(sql); // true rxOrderBy.IsMatch(sql); // false

Comments

Priority: Normal

Type: Bug

State: Submitted

Assignee:

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted: Inline code

Affected versions:

Due in version:

Sprint:

Story Points:

Cycle: