Paolo ponzano - 354 - Feb 4, 2015

Hello again,
I’ve got on an mvc application a class that automatically compose the WHERE clause based on filters that arrives from a Telerik Kendo Grid. I use an extension method to check SQL Injection but sometimes it generates an exception of type

ArgumentException

Potential illegal fragment detected 

This one generates the exception 

var field = “INTESTAZIONE”;
var value = “nicastro”; //a colleague of mine
"{0} LIKE ‘%{1}%’".Fmt(field, (value ?? “”).Replace("’", “’’”).SqlVerifyFragment());

This one not

var field = “INTESTAZIONE”;
var value = “ponzano”;
"{0} LIKE ‘%{1}%’".Fmt(field, (value ?? “”).Replace("’", “’’”).SqlVerifyFragment());

I think it’s a bug… or maybe servicestack hates my colleague nicastro a lot!

Thanks

This is tripping up OrmLite’s built-in raw sql verification.

You can control what tokens are considered illegal by modifying the OrmLiteUtils.IllegalSqlFragmentTokens collection: https://github.com/ServiceStack/ServiceStack.OrmLite/blob/master/src/ServiceStack.OrmLite/OrmLiteUtils.cs#L184

Or in the latest v4.0.37+ on MyGet you can by-pass verification with the new Unsafe* API’s, e.g:

q.UnsafeWhere(“cast(IntCol as varchar) LIKE ‘%2065%’”);

https://github.com/ServiceStack/ServiceStack/wiki/MyGet

paolo ponzano:

Hello Demis,
I don’t understand why nicastro string fails … try it yourself

string res1 = “ponzano”.SqlVerifyFragment(); //works
string res2 = “nicastro”.SqlVerifyFragment(); //exception

if you try with 

string res2 = “niastro”.SqlVerifyFragment(); // without C it works

BTW I want an explicit validation on those strings so I  won’t bypass the verification

Thanks

Sergej Loch:

I’m not 100% sure but with “c” you will get “cast” and “cast” is a reserved keyword in most rdbms. 

paolo ponzano:

I’ve checked your sources …
https://github.com/ServiceStack/ServiceStack.OrmLite/blob/4290229cd50ae6475a3edffc198bbdc87cc54539/src/ServiceStack.OrmLite/OrmLiteUtils.cs

it falls on “cast” illegal sqlfragment 

  public static string[] IllegalSqlFragmentTokens = { 
            “-”, “;-”, “;”, “%”, “/”, “/”, “@@”,"@", 
            “char”, “nchar”, “varchar”, “nvarchar”,
            “alter”, “begin”, “cast”, “create”, “cursor”, “declare”, “delete”,
            “drop”, “end”, “exec”, “execute”, “fetch”, “insert”, “kill”,
            “open”, “select”, “sys”, “sysobjects”, “syscolumns”, “table”, “update” };

niCASTro … so also with “fidel CASTro” will fail…

can this be fixed in some way?

yes it’s a public writable property so you can just assign a new array that doesn’t contain “cast”.