1

I want to use collation in a query in NHibernate and apparently the only way to do that (at least from what I found) is through adding an sql expression (Can I customize collation of query results in nHibernate?)

So I have something like

c.Add(Expression.Sql("Title COLLATE Divehi_90_BIN2 LIKE ?", 
                     title, 
                     NHibernateUtil.String))

However this matches the exact string and I want to use % on both sides.

Title COLLATE Divehi_90_BIN2 LIKE %?% gives me an error,

but padding it on the title: "%" + title + "%" works.

My question is - Is there a way to properly give parameters in Expression.Sql because using the % on both sides looks like a security flaw - that I am inviting query injection.

PS. I can't change the collation of the column at the database level because the column contains data that are of two languages.

Community
  • 1
  • 1
netslaves
  • 60
  • 6
  • What SQL is generated by NHibernate for the `Title COLLATE Divehi_90_BIN2 LIKE %?%` attempt? – mickfold Apr 24 '13 at 10:21
  • > [SQL: SELECT top 5 this_.Title as Title11_1_, this_.Preface as Preface11_1_, this_.Body as Body11_1_, this_.IsPublished as IsPublis5_11_1_ FROM Books WHERE Title COLLATE Divehi_90_BIN2 LIKE %@p0% and this_.IsPublished = @p1] Positional parameters: #0>women #1>True – netslaves Apr 24 '13 at 11:08
  • I get an Nhibernate.ADO exception - could not execute query. – netslaves Apr 24 '13 at 11:09

1 Answers1

3

The problem is that %@p0% is not a quoted string, i.e. '%@p0%'.

From the NHibernate documentation it seems this is the recommended form.

Expression.Sql("Title COLLATE Divehi_90_BIN2 LIKE ?", 
               String.Format("%{0}%", title),
               NHibernateUtil.String)

Please note that the string String.Format("%{0}%", title) will be passed in as a parameter so you are not inviting sql injection attacks by this approach.

mickfold
  • 2,003
  • 1
  • 14
  • 20