在HANA SQL语句的PLACEHOLDER子句中转义单引号 [英] Escaping single quotes in the PLACEHOLDER clause of a HANA SQL statement

问题描述

我注意到在PLACEHOLDER子句的上下文中，"HANA SQL"如何转义单引号不一致.例如，考虑以下PLACEHOLDER子句片段:

I noticed an inconsistency in how "HANA SQL" escapes single quotes in the context of the PLACEHOLDER clause. For example, consider the following PLACEHOLDER clause snippet:

('PLACEHOLDER' = ('$$CC_PARAM$$','''foo'',''an escaped single quote \'' '''))

上面的PLACEHOLDER子句包含分配给CC_PARAM的多个值.范围.我们可以看到第二个参数的 inside 中，我们有一个单引号，并用反斜杠转义.但是，我们将每个参数的单引号 换成另一个单引号(即，我们用''代替\''.在第一种情况下可以使用\''格式，但是在第二种情况下无法使用''格式.

The PLACEHOLDER clause above contains multiple values assigned to the CC_PARAM. parameter. We can see that inside of the second argument we have a single quote that's escaped with a backslash. However, we escape the single quotes outside each argument with another single quote (i.e. we do '' instead of \''. It's possible to use the \'' format for the first case, but it's not possible to use the '' format in the second case.

为什么会有这种差异?它使多输入输入参数中的转义引号变得棘手.我正在寻找以编程方式为HANA设计SQL查询.我在这里想念什么吗?在所有情况下在''上使用\''是否安全?还是我需要逻辑来判断单引号在哪里出现并适当地转义?

Why is there this discrepancy? It makes escaping quotes in multi-input input parameters tricky. I'm looking to programmatically craft SQL queries for HANA. Am I missing something here? Is it safe to use \'' over '' in all cases? Or do I need logic that can tell where a single quote occurs and escape as appropriate?

推荐答案

此处的隐式规则(由软件的实现方式决定)是对于计算视图的参数值，反斜杠\用于转义单个引号.

The implicit rule here - given by how the software is implemented - is that for parameter values of calculation views, the backslash \ is used to escape the single quotation mark.

对于所有标准SQL字符串出现，两次使用单引号''是区分语法元素和字符串文字的正确方法.

For all standard SQL string occurrences, using the single-quotation mark twice '' is the correct way to differentiate between syntax element and string literal.

原因:

• PLACEHOLDER语法不是SQL，而是特定于HANA的命令扩展名.因此，当前的实现没有违反任何通用标准.

• the PLACEHOLDER syntax is not SQL, but a HANA-specific command extension. So, there is no general standard that the current implementation violates.

，此命令扩展被嵌入，并分别钳制到标准SQL语法中，并且必须由同一解析器处理.

that given, this command extension is embedded into, respectively clamped onto the standard SQL syntax and has to be handled by the same parser.

但是参数不仅由SQL解析器解析一次，而且由基于计算视图实例化计算方案的组件再次解析.稍作斜视，不难看出parameters接口是一个通用的键值接口，该接口允许将各种信息传递给calc.引擎.

But the parameters are not only parsed once, by the SQL parser but again by the component that instantiates the calculation scenario based on the calculation view. With a bit of squinting it's not hard to see that the parameters interface is a general key-value interface that allows for all sorts of information to be handed over to the calc. engine.

有人可能会争辩说，通过键值对提供参数的整个方法与常规SQL语法方法不一致，并且是正确的.从另一方面来说，这种方法可以为向HANA特定部分添加新命令元素提供一般灵活性，而无需在结构上更改语法(以及语法分析器). 显而易见的缺点是，键名和值都是字符串类型的.为了避免丢失所需的内部字符串"转义，需要使用与主SQL转义字符串不同的转义字符串.

One might argue that the whole approach of providing parameters via key-value pairs is not consistent with the general SQL syntax approach and be correct. On the flip side, this approach allows for general flexibility for adding new command elements to the HANA-specific parts, without structurally changing the syntax (and with it the parser). The clear downside of this is that both the key names, as well as the values, are string-typed. To avoid losing the required escaping for the "inner string" an escape string different from the main SQL escape string needs to be used.

在这里，我们有两种不同的方式来处理字符串值以用作过滤条件.

And here we are with two different ways of handing over a string value to be used as a filter condition.

很有趣，两种方法仍可能导致相同的查询执行计划.

Funny enough, both approaches may still lead to the same query execution plan.

事实上，在许多带有输入参数的场景中，字符串值将在内部转换为符合SQL的形式.当输入参数用于过滤或calc中的表达式时，就是这种情况.可以转换为SQL表达式的视图.

As a matter of fact, in many scenarios with input parameters, the string value will be internally converted into a SQL conforming form. This is the case when the input parameter is used for filtering or in expressions in the calc. view that can be converted into SQL expressions.

例如

 SELECT
     "AAA" 
FROM "_SYS_BIC"."sp/ESC"
     ('PLACEHOLDER' = ('$$IP_TEST$$',  'this is a test\''s test'));

在我的系统上显示以下执行计划

shows the following execution plan on my system

OPERATOR_NAME   OPERATOR_DETAILS
PROJECT         TEST.AAA
  COLUMN TABLE  FILTER CONDITION: TEST.AAA = 'this is a test's test' 
                (DETAIL: ([SCAN] TEST.AAA = 'this is a test's test'))   

请注意转义符\'的删除方式.

Note how the escape-\' has been removed.

所有内容:使用PLACEHOLDER值时，需要使用\'转义，而在所有其他情况下，需要使用''转义. 对于查询构建器而言，实现这一目标应该并不困难，因为您可以在处理PLACEHOLDER语法时考虑到这一点.

All in all: when using PLACEHOLDER values, the \' escaping needs to be used and in all other cases, the '' escaping. That should not be terribly difficult to implement for a query builder as you can consider this when dealing with the PLACEHOLDER syntax.

这篇关于在HANA SQL语句的PLACEHOLDER子句中转义单引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！