Using hexadecimal escapes in parameterized query

[Former Member]

I can't get a result set when using hexadecimal escapes in a parameterized query versus a straight SQL query.

Examples from log (so you can see what the server is seeing):

Without a parameter (results returned as expected):

+2,<,289,PREPARE,SELECT PROD_CODE FROM PRODUCTS WHERE INFO = 't\\x00e\\x00c\\x00h'

Using a parameter, apparently identical to hard-coded value above (no results returned):

=,<,289,PREPARE,SELECT PROD_CODE FROM PRODUCTS WHERE INFO = ?  
+1,H,289,0,varchar,'t\\x00e\\x00c\\x00h'

Is it not possible to do this using parameters?

Fyi, the reason behind this approach is to find some data stored as Unicode inside a long varchar field.

Using SQL Anywhere 12.0.1.3994, Delphi XE

Update: Code Example (Object Pascal)

qryNoParams.SQL.Text := 'SELECT PROD_CODE FROM PRODUCTS WHERE INFO = ' + QuotedStr('t\\x00e\\x00c\\x00h');
qryNoParams.Open; {expected result set returned}

qryParams.SQL.Text := 'SELECT PROD_CODE FROM PRODUCTS WHERE INFO = :INFO';
qryParams.ParamByName('INFO').AsString := 't\\x00e\\x00c\\x00h';
qryParams.Open;  {no result set}

[Breck_Carter]

It looks like you're showing the request log. Please show us the source code.

Note that escaped strings like \\x00 are intended to be placed inside string literals that are parsed by the SQL Anywhere engine and turned into a single character (hex 00 in this case). In your case your parameter may (somehow) have embedded four characters '\\', 'x', '0' and '0' in the string without benefit of the parsing... but I am not an expert in how strings are shown in the request level log.

[Former Member]

I've updated with my simple example.

[Former Member]

I should have tried this before posting, but I can see that a parameterized UPDATE places the /x00 inside the string, instead of parsing them into blanks. Looks like it is parsed differently as you said, simple as that.

[Breck_Carter]

The QuotedStr call ensures that the SELECT passed to SQL Anywhere contains a quoted string, so that SQL Anywhere will parse it properly.

The ParamByName code, however, may have to be changed to use whatever escape syntax is available in Object Pascal... I am guessing that Object Pascal does not parse-and-change \\x00 to anything else. I am guessing that the parameter value must contain the actual data bytes, not a string literal with quotes and escape characters.

Perhaps you can use a "# control string" like #0 to ensure the paramter passed to SQL Anywhere contains True Data Bytes as opposed to Funky String Literals... but that may just reveal my complete lack of Object Pascal knowledge 🙂

[johnsmirnios]

Yes, the parser (lexical analyzer actually) handles the escape sequences. Host variables are sent as-is except that they may go through charset translation. Similarly, you don't need to double-up the quotes when sending quotes in a hostvar.

FWIW, host variables are your best bet for avoiding SQL injection attacks since you don't need to worry about how the string will be parsed as part of the SQL statement. Host variables also the only reliable way to send arbitrary NCHAR strings to a database whose CHAR charset isn't UTF8 since SQL strings are converted to and parsed in CHAR charset.

[Former Member]

Good suggestion that I had only pondered, and it seemed promising, but the data access layer sees the #0 as a null terminator and truncates after the first character. I'm still exploring the code to understand where it happens.