Unicode string is not correctly encoded when using StartsWith


I found an issue in OrmLite with the StartsWith operator and unicode/utf8 characters. The Insert and Update methods works as expected, however when doing a Delete (I suspect that Select is affected too) with a StartsWith condition sends an Ascii text instead of a Unicode/UTF8 text.

It looks like there is a “N” missing in the generated SQL code: upper("Setting") like ''SOLR/SYNONYMS/EN-US/103B5145-F3C6-43F7-BFF9-9C7B9D7C2C80 動物あれこ/%''))'

It should be easy to add the missing “N” but I was wondering if this operation could be changed to use SQL parameters instead of using dynamic SQL to prevent SQL injection.

I included the relevant code below.

.Net code

db.DeleteAsync<Data.Configuration>(x => x.Scope == scope
                                    && x.Component == component
                                    && (x.Setting == setting || x.Setting.StartsWith(setting + BaseSettings.SettingDelimiter)));

Generated SQL (Sql Server)

exec sp_executesql N'DELETE FROM "Configuration" WHERE ((("Scope" = @0) AND ("Component" = @1)) AND (("Setting" = @2) OR upper("Setting") like ''SOLR/SYNONYMS/EN-US/103B5145-F3C6-43F7-BFF9-9C7B9D7C2C80 動物あれこ/%''))',N'@0 nvarchar(max) ,@1 nvarchar(max) ,@2 nvarchar(max) ',@0=N'All',@1=N'All',@2=N'Solr/Synonyms/en-US/103b5145-f3c6-43f7-bff9-9c7b9d7c2c80 動物あれこ'

I should point out that I am using V4.0.52

Strings are escaped so there’s no SQL Injection possible, but OrmLite has been moving to db params as the default option for a while now and as of the current v4.0.57 release, it’s no longer an option and OrmLite’s legacy API’s which don’t use db params have been deprecated and moved to the ServiceStack.OrmLite.Legacy namespace.

The StartsWith/Contains/EndsWith expressions were the last remaining expressions in SqlExpression<T> that we missed converting across, but have now been converted into using db params from this commit .

This change is available from v4.0.57 that’s now available on MyGet.

Thanks for the quick fix.