If you want to implement a dynamic sort based on an input parameter of type Text, you need to sanitize this string to prevent SQL-injections. This library has two functions to get you covered:
- TextToSortAttribute
Converts a string with SQL sort attributes (e.g. “CreatedOn DESC” or “{Contact}.[LastName], {Contact}.[FirstName]”) to SortAttributes for the BuildSafe_SortClause function. - BuildSafe_SortClause
Returns a comma-delimited text value containing all the sort attributes provided as input. The returned value can be safely used in a SQL “ORDER BY” clause.
The former versions (< 1.2.0) of this forge component were using another approach derived from the OutSystems documentaion article “How to enable dynamic sorting in a table fed by a SQL query“. This older function EncodingSortForSQL is still contained in the library for backward compatibility, but is marked as deprecated and internally uses the new functions now:
- DEPRECATED_EncodingSortForSQL
Encodes a string with SQL sort attributes to prevent SQL-Injection.
The new version offers the following improvements:
- TextToSortAttributes still allows to receive the ordering as a string in SQL syntax, but parses it to a list of SortAttributes.
- This SortAttributes list can then be manipulated as needed, e.g. to force certain sort orders.
- BuildSafe_SortClause takes this list, sanitizes the individual attributes and builds a minified string for use in an advanced SQL element.