I try to POST /api/v2/statements to Snowflake with statement

INSERT INTO test (EXPRESSION) VALUES ('(?<=\\?)|&')

In database I have (?<=?)|&. But I need (?<=\?)|&. That is why I escape backslash. When I post statement with 4 backslashes (\\)

INSERT INTO test (EXPRESSION) VALUES ('(?<=\\\\?)|&')

In database I have what I need (?<=\?)|&

Could someone explain what is going on here ?

CodePudding user response：

You're getting two levels of backslash escaping. You need to escape the backslash, so you add a backslash. That means you need \\. Snowflake also escapes backslashes, so it will turn that into a single backslash.

select '\\';

In order to get two backslashes, one you need and one that escapes it, you need to send Snowflake four.

select '\\\\';

CodePudding user response：

Welcome to the age old, "string parsing problem". Whenever a string is parsed and processed to hand to another layer, the prior layer might need the escaping escaped.

In the SQL parser as witnessed by the INSERT command you have to escape to get past the parser. The fact you have to escape it a second time implies there is a parse on the REST API, which doesn't surprise me. You would notice this if you tried to put that same INSERT command into a Stored Procedure to do the insert, it needs double parsing, as the parser reading the stored procedure undoes one level of escaping, then the SQL parser when the stored procedure is run undoes a level.

This is the reason some programming languages have string literal forms that have no parsing of the tokens, to help avoid some of the pain of N escaping, but those string forms don't allow putting in non-printable tokens, as the string is not parsed to allow those. eg a Unicode token /u0020 or /r /n /t like token.