Skip to main content

Building dynamic SQL statements the right way

The following examples of implementing common use cases of dynamic SQL statements in OutSystems can help you prevent SQL injection vulnerabilities.

Also, check the SQL Injection Warning page for more information on the warnings you might get when the OutSystems platform detects a known bad practice that might lead to vulnerabilities.

Switching a filter condition on or off

Use an extra variable that switches a condition on/off without using parameters with Expand Inline enabled.

For example, if you wanted to provide an optional filter by surname, you could define your SQL query in the following way:

SELECT {Users}.[Username], {Users}.[Surname], {Users}.[Firstname]
FROM {Users}
WHERE {Users}.[IsActive] = 1
AND (@filterBySurname = 0 OR {Users}.[Surname] LIKE '%' + @surnameFilter +'%')

The Query Parameters filterBySurname and surnameFilter — configured with Boolean and Text data type, respectively — would have the following values:

filterBySurname = surnameFilter <> ""
surnameFilter = surnameFilter 

This way you could use this SQL statement to list the username of all users or of users having a specific surname while avoiding having the Expand Inline property enabled and using the EncodeSql function.

Implementing custom sort orders in SQL queries

Query parameters in prepared statements can only be used for data replacements; they cannot be used as parameters for specifying table names, table fields, operators, or SQL syntax like "ORDER BY" clauses. Therefore, to customize the sort order of the results returned by a SQL query you will need to enable the Expand Inline property for the parameter that will define the custom sort order.

As said before, you must not use values provided by end-users as part of SQL statements — this is also valid for the sort order of an SQL statement. The EncodeSql built-in will not protect you in this case since it's designed to encode string literals and not parts of a SQL statement.

Use the List_SortColumn widget available in RichWidgets together with the List_SortColumn_GetOrderBy server action to implement custom sort orders in your SQL statements. Check How to use the List Sort Column Widget with a SQL query in Development FAQs for more information.

If you must provide complex sorting abilities in your application provided by end-users, you should provide them with a UI where they can select their desired sorting options without having to enter any column/attribute names. All column names and sort order should be determined by your application from the options selected by end-users.

  • Was this article helpful?