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


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


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, 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.ParamByName('INFO').AsString := 't\x00e\x00c\x00h';
qryParams.Open;  {no result set}

asked 10 Jun '15, 13:59

Dan%20Hacker's gravatar image

Dan Hacker
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

Follow this question

By Email:

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



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

Question tags:


question asked: 10 Jun '15, 13:59

question was seen: 1,071 times

last updated: 11 Jun '15, 16:29