Querying unicode characters
Question

Hi,

I'm trying to query a list of customers and the requirement is to be able to search both the English name and Chinese name. 

I tried with a like condition in an aggregate and couldn't get the right records to return when searching Chinese.

When querying Unicode with SQL Server I need to prefix "N" in front of the search text. 

Example as such:

SELECT {CUSTOMERS}.*
FROM {CUSTOMERS}
WHERE {CUSTOMERS}.NAME LIKE N'%??%';


The problem I'm having is if I want to use an input parameter, it is not able to search. 

Putting this inside Advanced SQL returns no records if I search Chinese:

SELECT {CUSTOMERS}.*
FROM {CUSTOMERS}
WHERE {CUSTOMERS}.NAME LIKE N'%' + @SearchText + '%';


I could get it to work if I manipulate the parameter a little and use Expand Inline but then Service Studio nags with the warning message on SQL injection. What is the correct approach here?


EDIT: "??" in the first query above are two Chinese characters. The forum swallowed them after I submitted the question.

mvp_badge
MVP

Hi Quentin,

I made a quick test on my PE, but I can't reproduce this: both Aggregates and SQL work just fine (as in I can query the right record), see attachment. What database are you using? In the cloud or on-premise?



TestKh.oml

Hi Kilian

It's an external SQL Server database which is connected to through an extension. The whole system is on-premise. 

The nag message has gone away and the query works now after I put the input parameter as

"N'%" + EncodeSql(Variable) + "%'"

instead of 

EncodeSql("N'%" + Variable + "%'")

Which of course makes sense (why would I need to encode something I wrote in the code myself?).

Still I am curious to know what is the best practice here and why it seems to work sometimes (as in Kilian's case) and not all the time.

mvp_badge
MVP

I would guess that has to do with the external database and how it's set-up, in combination with the way that the Platform sends its parameters. When using Expand Inline parameters, the expansion takes place before sending the SQL to the database, while when using normal parameters, the parameters themselves are send to the database, which may cause the database to assume different encoding.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.