OutSystems动态查询条件实现in查询怎么玩的？

OutSystems动态查询条件实现in查询怎么玩的？本文是续OutSystems如何实现动态列表格展示统计数据的后续故事，Outsystems开发却实是很不错的低代码开发平台，对于一个熟悉新一代.net的开发人来说，这段时间在实现一个功能就在思考OutSystems里面是怎样实现类似SQL的动态查询功能，用于处理分页查询时条件不固定的需求，如果单单用outsystems自带的做一个分页功能，用不了5分钟就完事了，但一旦带上条件，这事情就有点麻烦了。

OutSystems动态查询条件实现in查询

先来说一下剧情前提，先前我们用outsystems实现了动态列表格展示统计数据，当时在GetData里面直接使用了下面的SQL来统计数据，系统里面有这么一个功能，就是复杂一点的统计，分组统计各个价格的交易数量，以下是混淆后的查询SQL（基本上反映了真实的情况）：

declare @param1 nvarchar(32), @param2 int = 9527
select CONVERT(varchar, Col7) + '%' as Col7, count(1) as Col8
from (
    select t2.Col2, ISNULL(t3.Col3, 0) as Col4, ISNULL(t4.Col4, 0) as Col5, t1.Col1, 
           t2.Col2 + (case when t1.Col6 < CONVERT(datetime, '2024-06-03') then 0 else ISNULL(t3.Col3, 0) end) + ISNULL(t4.Col4, 0) as Col7,
           t1.Col6
    from [DB1].dbo.[TableA] t1
    inner join [DB1].dbo.[TableB] u on t1.Col9 = u.Col10
    inner join [DB1].dbo.[TableC] ue on u.Col10 = ue.Col11
    inner join [DB1].dbo.[TableD] t2 on t1.Col1 = t2.Col12 and t2.Col12 = @param2
    inner join [DB1].dbo.[TableE] t on t1.Col13 = t.[Col14]
    left join [DB1].dbo.[TableF] t3 on t1.Col15 = t3.Col16
    left join [DB1].dbo.[TableG] t4 on t1.Col1 = t4.Col17 and t4.Col18 > 0 and t4.Col19 = 0
    where t1.Col19 = 0
    and t.Col20 <> 4
    and t.Col21 in (6, 8, 12)  --只取这几种交易状态的数据. https://jhrs.com 首发此文.
    and ((CASE WHEN ((len((LTrim(RTrim(@param1))))) > 0) THEN (CASE WHEN (ue.[Col22] LIKE ((N'%' + @param1) + N'%') or u.Col23 LIKE ((N'%' + @param1) + N'%')) THEN 1 ELSE 0 END) ELSE 1 END) = 1)
) t1 group by Col7

OutSystems动态查询条件实现in查询怎么玩的？

将以上的功能开发完比后，在开发环境上的 6,8,12 这三个数字，代表着不同的交易状态，验证结果也是正确的。不过事情的发展总是超出了普通人的预料，本来像6，8，12，这种代表着各种状态的数据，在C# 里面定义一个枚举可以将值给固定下来，但因为用的是outsystems，这玩意里面没有枚举的概念，只有Static Entity可以实现类似编程里面的枚举功能，但Static Entity也是存储在数据库同名表里面的，因此意味着它的Value(值)有可能与开发环境是不一样，因此我们遇到的正是这个问题。

这也就意味着在统计的SQL代码里面不能将 6,8,12写死了，需要用参数的形式传入，或者使用之前查询出来也成，我选择了后者，通过传参的方式处理。

那么问题来了，像上面的条件 t.Col21 in (6, 8, 12) 中的3个数字，我们需要改为参数传入该如何做呢？答案就是这里我们需要运用到Expand Inline功能了，如下图所示那样，在右侧参数栏的Expand Inline这里，改为Yes，默认是No

然后再将参数传入即可，修改后SQL的如下所示：

declare @param1 nvarchar(32), @param2 int = 9527
select CONVERT(varchar, Col7) + '%' as Col7, count(1) as Col8
from (
    select t2.Col2, ISNULL(t3.Col3, 0) as Col4, ISNULL(t4.Col4, 0) as Col5, t1.Col1, 
           t2.Col2 + (case when t1.Col6 < CONVERT(datetime, '2024-06-03') then 0 else ISNULL(t3.Col3, 0) end) + ISNULL(t4.Col4, 0) as Col7,
           t1.Col6
    from [DB1].dbo.[TableA] t1
    inner join [DB1].dbo.[TableB] u on t1.Col9 = u.Col10
    inner join [DB1].dbo.[TableC] ue on u.Col10 = ue.Col11
    inner join [DB1].dbo.[TableD] t2 on t1.Col1 = t2.Col12 and t2.Col12 = @param2
    inner join [DB1].dbo.[TableE] t on t1.Col13 = t.[Col14]
    left join [DB1].dbo.[TableF] t3 on t1.Col15 = t3.Col16
    left join [DB1].dbo.[TableG] t4 on t1.Col1 = t4.Col17 and t4.Col18 > 0 and t4.Col19 = 0
    where t1.Col19 = 0
    and t.Col20 <> 4
    and t.TRANSACTION_TYPE_ID in (@TransactionTypes)  --只取这几种交易状态的数据. https://jhrs.com 首发此文.
    and ((CASE WHEN ((len((LTrim(RTrim(@param1))))) > 0) THEN (CASE WHEN (ue.[Col22] LIKE ((N'%' + @param1) + N'%') or u.Col23 LIKE ((N'%' + @param1) + N'%')) THEN 1 ELSE 0 END) ELSE 1 END) = 1)
) t1 group by Col7

OutSystems动态查询条件实现in查询怎么玩的？

Expand Inline有什么作用？

当设置了“Expand Inline”为Yes时，该参数中的任何内容都不是 SQL 参数，而是在转换并发送到数据库之前合并到 SQL 查询中的 SQL 文本片段。所以假设我有以下 SQL：

SELECT {MyEntity}.*
FROM {MyEntity}
WHERE {MyEntity}.[UserId] = @UserId
  @AdditionalWhereClauses

@UserId是普通参数，@AdditionalWhereClauses是扩展内联参数，则可以将以下 SQL 放入 AdditionalWhereClauses 参数中：

AND {MyEntity}.[UserName] LIKE '%smith%'

它将过滤用户名中带有“史密斯”的用户。

expand 内联参数的典型用途是 IN 值的变量列表或变量 WHERE 子句

总结为一句话就是：开启 Expand Inline 后可以实现早先动态查询功能。因为它是将传入的任何字符串交给数据库直接执行，这也意味着存在着SQL注入的问题。

避免动态值使用expand inline参数

通过使用 expand inline 参数，您可以在 OutSystems SQL 查询中插入SQL内容。从某种意义上说，此参数不是 SQL 参数，因为它不是在数据库中创建的。它是在运行时计算的，并在 SQL 调用中进行文本扩展。您应注意内联参数在与动态值一起使用时可能产生的性能影响。

以下是 expand 内联参数的一些常见用法示例：

示例 1

动态条件。例如，如果用户在 UI 中填写搜索关键字，然后传入一个名为 SearchClause 的展开内联参数，否则只需在该参数中传递一个空字符串。“and {User}.[Name] like '%”+SearchKeyword+”%'”

在 SQL 查询定义中，可以使用 inline 参数以文本方式扩展该内容：

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

示例 2

动态值集。例如，记录列表转换为逗号分隔的值字符串，然后在 UserIds 扩展的内联参数中传递到查询中：

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

有什么影响？

在这两个示例中，每次OutSystems运行查询时，它都可以将不同的文本传递给内联参数。这将为数据库引擎生成不同的查询。经常更改的内联参数不允许数据库优化执行计划，因为引擎会不断生成不同的查询。这对性能有重大影响。一个包含大量数据的复杂查询，在第一次执行时需要几秒钟，在统计优化后，每次下一次调用都以毫秒为单位执行。

最佳实践

以下是一些避免扩展内联参数的建议：

• 避免使用动态 SQL 来应对可选过滤器 – 而是使用覆盖可选性的固定条件。要修复示例 1，请将 SearchKeyword 作为普通参数发送，而不是内联参数 SearchClause，并按如下方式转换查询：

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

• 通过此修复，数据库引擎的查询始终相同。将可选性条件放在第一位 （） 非常重要，以确保在没有 Search 关键字时永远不会执行真实条件。数据库引擎在优化这种类型的“虚拟”条件方面做得很好，因此为此可选过滤器准备查询是有效的。@SearchKeyword = ''
• 使用子查询，而不是将查询结果作为逗号分隔的值注入到下一个查询中。如果示例 2 中的 UserIds 来自一个复杂的查询，用于选择具有特定逻辑的用户，则该查询可以包含在同一个 SQL 查询中。它甚至可以用于避免可能降低性能的 IN 条件。例如：

 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]  

OutSystems动态查询条件实现in查询怎么玩的？

数据库引擎现在可以优化此改进的查询。

• 当逗号分隔的值列表不是来自单个查询时，请使用临时表。有时，值列表是一些复杂逻辑（非 SQL 处理）的结果，这些逻辑不断向列表中添加元素。不要将元素添加到逗号分隔的值列表中，而是将值添加到临时表中（您可以Google搜索“Oracle 临时表”或“SQL 临时表”，了解如何在 SQL 查询中创建临时表）。填充临时表后，可以将示例 2 更改为：

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

OutSystems构建动态SQL语句最佳实践

以下在 OutSystems 中实现动态 SQL 语句的常见用例示例可以帮助您防止 SQL 注入漏洞。此外，请查看SQL注入警告页面，了解当 OutSystems 平台检测到可能导致漏洞的已知不良做法时可能收到的警告的更多信息。

打开或关闭筛选条件

使用一个额外的变量，该变量在不使用启用了Expand Inline属性的参数的情况下打开/关闭条件。您无需为 设置 展开行。filterBySurname

例如，如果要按姓氏提供可选的筛选器，可以按以下方式定义 SQL 查询：

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

Query Parameters 和 — 分别配置了 Boolean 和 Text 数据类型 — 将具有以下值：
filterBySurnamesurnameFilter

filterBySurname = surnameFilter <> ""
surnameFilter = surnameFilter

这样，您可以使用此 SQL 语句列出所有用户的用户名或具有特定姓氏的用户的用户名，同时避免启用 Expand Inline 属性并使用 EncodeSql 函数。

动态构建WHERE …IN （…）子句

不能对子句中的值使用预准备语句，因为不能将查询参数 （） 替换为值数组。因此，在这种情况下，必须启用查询参数的 Expand Inline 属性。WHERE <column> IN (@valuelist)valuelistvaluelist

要正确构建“IN”子句的值，应始终使用 Sanitization 扩展中可用的 BuildSafe_InClauseIntegerList 和 BuildSafe_InClauseTextList 函数之一。

示例 1：

SELECT {Users}.[Username], {Users}.[Surname], {Users}.[Firstname]
FROM {Users}
WHERE {Users}.[IsActive] = 1 AND {Users}.[Id] IN (@idlist)

OutSystems动态查询条件实现in查询怎么玩的？

在此示例中，查询参数是使用 Sanitization 扩展中提供的 BuildSafe_InClauseIntegerList 函数生成的。idlist

此示例假定您已使用过滤器的用户标识符填充了“整数”记录列表，其中“Integer”是在 Sanitization 扩展中定义的结构，具有用于保存长整数值的单一属性。例如："IN (@idlist)"

userids[0] = 4
userids[1] = 45
userids[2] = 76

使用 BuildSafe_InClauseIntegerList 函数定义 SQL 查询参数的值，以构建 IN 子句的内容：idlist

idlist将包含文本 。"4,45,76"

示例 2：

SELECT {Users}.[Username], {Users}.[Surname], {Users}.[Firstname]
FROM {Users}
WHERE {Users}.[IsActive] = 1 AND {Users}.[Surname] IN (@namelist) 

在此示例中，查询参数是使用 Sanitization 扩展中提供的 BuildSafe_InClauseTextList 函数构建的。namelist

此示例假定您已使用过滤器的姓氏填充了“文本”记录列表（其中“Text”是 Sanitization 扩展中定义的结构，具有用于保存文本值的单个属性）。例如："IN (@namelist)"

surnames[0] = "Smith"
surnames[1] = "Johnson"
surnames[2] = "Martinez"

使用 BuildSafe_InClauseTextList 函数定义 SQL 查询参数的值以构建 IN 子句：
namelist

namelist = BuildSafe_InClauseTextList(surnames)

namelist将包含文本 。"'Smith','Johnson','Martinez'"

在SQL查询中实现自定义排序顺序

预准备语句中的查询参数只能用于数据替换;它们不能用作指定表名、表字段、运算符或 SQL 语法（如“ORDER BY”子句）的参数。因此，若要自定义 SQL 查询返回的结果的排序顺序，需要为定义自定义排序顺序的参数启用 Expand Inline 属性。

您不得将最终用户提供的值用作 SQL 语句的一部分 — 这也适用于 SQL 语句的排序顺序。在这种情况下，内置的 EncodeSql 不会保护您，因为它旨在编码字符串文字，而不是 SQL 语句的一部分。

如果必须在最终用户提供的应用程序中提供复杂的排序功能，则应为他们提供一个 UI，他们可以在其中选择所需的排序选项，而无需输入任何列/属性名称。所有列名和排序顺序都应由应用程序从最终用户选择的选项中确定。