Skip to main content
OutSystems

Don’t abuse of expand inline parameters

It is possible to inject SQL content inside a SQL query through an expand inline parameter. This type of parameter it is not a SQL parameter, in the sense that it will not be created in the database as a parameter. Instead, it is meant to be calculated in runtime to be textually expanded inside the SQL.

Here are some examples of common use for Expand inline parameters:

Example 1:

Dynamic conditions - e.g., if the user fills a search keyword in the UI, then pass
“and {User}.[Name] like ‘%”+SearchKeyword+”%’” in a expand inline parameter called SearchClause, otherwise simply pass an empty string in that parameter.

Inside the SQL query definition you can use the inline parameter to textually expand that content

SELECT {User}.[Name], {User}.[Phone]
FROM    {User}
WHERE {User}.[IsActive]
@SearchClause
ORDER BY {User}.[Name]

Example 2:

Dynamic set of values - e.g., a record list resulting from a previous query or action is transformed into a comma separated string of values, and then passed into the query in a expanded inline parameter called UserIds

SELECT {User}.[Name], {User}.[Phone]
FROM    {User}
WHERE {User}.[IsActive]
AND {User}.[Id] in { @UserIds }
ORDER BY {User}.[Name]

Impact

In these two examples, there is a good chance that every time you call the query a completely different text is passed to the Inline parameter, generating a completely different query in the perspective of the DB Engine.

Inline parameters that change too often don't allow the database to optimize execution plans, since it keeps generating different queries, with an huge impact in performance. With complex queries and after some volume of data, a query that takes a couple of seconds when executed for the first time, can take only a couple of milliseconds after statistical optimization of the execution plan.

Best practices

Here are some recommendations to avoid expand inline parameters

  • Avoid dynamic SQL to cope with optional filters - use fixed conditions covering the optionality instead.

To fix example 1., instead of the inline parameter SearchClause, send the SearchKeyword as a normal parameter and transform the query as follows:

SELECT {User}.[Name], {User}.[Phone]
FROM    {User}
WHERE {User}.[IsActive]
AND @SearchKeyword = ‘’ OR {User}.[Name] like ‘%’+@SearchKeyword+’%’
ORDER BY {User}.[Name]

With this fix the query is always the same for the DB engine. It is important to place the optionality condition first (@SearchKeyword = ‘’) to make sure that the real condition is never executed when there is no Search keyword. DB engines do a very good job optimizing this type of “dummy” conditions, so preparing the query for this optional filters is practically neglectable

  • Use subqueries instead of injecting the result of a query as comma separated values to the next query.

In example 2., if UserIds come from a complex query to select users with a certain logic, that query can be included in the same SQL query, and can even be used to avoid the IN condition that is normally hearts performance. For example:

WITH UserIdTable (Id) as (SELECT ...)
SELECT {User}.[Name], {User}.[Phone]
FROM    {User}
INNER JOIN UserIdTable on UserIdTable.ID = {User}.[ID]
WHERE {User}.[IsActive]
ORDER BY {User}.[Name]

Again we achieved a fixed query that will be optimized by the DB Engine

  • Use temporary tables when the comma separated list of values don’t come from a single query. In some situations, the list of values is the result of some complex logic (non SQL processing) that keeps adding elements to the list. Instead of adding the elements to a comma separated list of values, simply add those values into a temporary table (just google “Oracle temporary tables” or “SQL temporary tables” to find out how you can create a temporary table in a SQL query). Once you populate the temporary table, example 2 can be fixed to:

SELECT {User}.[Name], {User}.[Phone]
FROM    {User}
INNER JOIN TemporaryTable on TemporaryTable.ID = {User}.[ID]
WHERE {User}.[IsActive]
ORDER BY {User}.[Name]

 

  • Was this article helpful?