Using hexadecimal escapes in parameterized query

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}

sa-12.0.1 parameters

asked 10 Jun '15, 13:59

Dan Hacker
176●5●5●13
accept rate: 0%

edited 10 Jun '15, 15:51

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.

(10 Jun '15, 15:06) Breck Carter

Replies hidden

I've updated with my simple example.

(10 Jun '15, 15:57) Dan Hacker

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.

(10 Jun '15, 16:03) Dan Hacker

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

(10 Jun '15, 19:34) Breck Carter

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.

(11 Jun '15, 09:03) John Smirnios

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.

(11 Jun '15, 16:29) Dan Hacker

showing 1 of 6 show all flat view

Be the first one to answer this question!

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sa-12.0.1 ×63
parameters ×23

question asked: 10 Jun '15, 13:59

question was seen: 2,694 times

last updated: 11 Jun '15, 16:29

SAP SQL Anywhere

Using hexadecimal escapes in parameterized query

Follow this question

Related questions