没有动态SQL的自定义SQL列公式 [英] Custom SQL column formulas without dynamic SQL

问题描述

我们有一个程序，允许用户将未修改的原始输入数据映射到标准化的最终表中.

We have a program that allows users to map raw unmodified input data to a standardized final table.

通常，这是简单的一对一匹配，不需要任何特殊逻辑.

In general it's a simple one-to-one match without any special logic needed.

例如； raw_table.raw_col_1将映射到final_table.col_1，raw_table.raw_col_2将映射到final_table.col_2，等等.

For example; raw_table.raw_col_1 will map to final_table.col_1, raw_table.raw_col_2 will map to final_table.col_2, etc.

但是，一个客户希望能够对final_table.col_3进行如下映射:

However, one customer wants the ability to have final_table.col_3 to be mapped as follows:

case 
    when (raw_col_1 = 'S12' and raw_col_2 = 'D18') or raw_col_3 is not null then raw_col_3      
    else 'GF17' 
end 

还有其他类似的请求.

在加载final_table时，我可以使用动态SQL轻松实现此目的.但是，这使我们容易受到SQL注入攻击的影响.

I can easily achieve this using dynamic SQL when loading the final_table. However, that leaves us open to SQL injection attacks.

有没有一种方法可以允许这种类型的自定义字段映射而无需借助动态SQL?

Is there a way we can allow this type of custom field mapping without resorting to dynamic SQL?

推荐答案

您正在进入允许开发工具在运行时向最终用户公开的领域-配置在某些时候变得非常复杂，以至于需要或模仿它.代码的力量.您有两种选择:

You're getting into the area of allowing development tools to be exposed to end users at runtime - configuration at some point becomes complex enough that it requires or mimics the power of code. You have a couple of options:

1)提供一个可以说明用例的用户界面-例如，简化的查询生成器.并确保所有单独的组件都经过验证或绑定.这是否可行，将取决于复杂性的级别以及要在这种用户界面中投入多少精力.

1) Provide a user interface that can account for the use cases - for example a simplified query builder. And make sure that all of the individual components are validated or bound in. Whether or not this is feasible, will end on the level of complexity, and how much effort you want to put into such a user interface.

2)提供管理员级别的自定义，允许客户提供更复杂的逻辑.由于这是一个Oracle数据库，您可以让他们将其作为PL/SQL函数提供，该函数可以返回该值.

2) Provide an admin level of customization which allows customers to provide more sophisticated logic. Since this is an Oracle database, you could have them provide this as a PL/SQL function which can return the value.

第二个选项可以通过用户界面或后端加载器完成.但是，无论哪种情况，您都应确保管理员了解这是特权很高的功能，并审核其中的内容以及谁可以访问它.

The 2nd option could either be done through a user interface, or via a backend loader. However, in either case, you should make sure that the administrators understand that this is highly privileged functionality, and audit what goes in and who has access to it.

您还可以配置此方式，以使程序包处于受限模式中，但具有更多的受限特权(并以定义者的权限调用)，尽管执行此操作的最佳方法将取决于您所使用的数据库的版本.使用. 12c在此区域提供了更多安全功能.

You can also configure this such a way that the package is in a limited schema has much more limited privileges (and is called with definer's rights) although the best way to do that will depend on the version of the database that you are using. 12c provides more security features in this area.

这篇关于没有动态SQL的自定义SQL列公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！