Escaping single quotes in the PLACEHOLDER clause of a HANA SQL statement

Question

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 '' '''))

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.

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?

Answer 1

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.

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

As for the why:

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

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

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.

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.

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.

For example

 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.

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.