This is a migrated thread and some comments may be shown as answers.

Handle filter generated parameters on server side for a grid serverFiltering=true

11 Answers 3128 Views

Grid

This is a migrated thread and some comments may be shown as answers.

Bilal asked on 28 Aug 2012, 03:53 PM

Hi,

I'm using Kendo with ASP .NET Web Forms.
I can see that handling the filter generated parameters of a grid with serverFiltering=true is so complex.
The autogenerated params are something like that:

http://127.0.0.1:81/Service.svc/GetAssetsJSONP?take=50&skip=0&page=1&pageSize=50&filter[filters][0][field]=ModelsModelCodeAS&filter[filters][0][operator]=eq&filter[filters][0][value]=kjkjkjjk&filter[filters][1][field]=ModelsModelCodeAS&filter[filters][1][operator]=eq&filter[filters][1][value]=dfsdfsd&filter[filters][2][logic]=and&filter[filters][2][filters][0][field]=ModelsModelNameAS&filter[filters][2][filters][0][operator]=eq&filter[filters][2][filters][0][value]=fdfsdfsdf&filter[filters][2][filters][1][field]=ModelsModelNameAS&filter[filters][2][filters][1][operator]=eq&filter[filters][2][filters][1][value]=fdsfdsfsdfsd&filter[logic]=and

How am I supposed to deal with this on server side? Please Kendo clarify this.

Thanks,
Bilal

11 Answers, 1 is accepted

answered on 09 Nov 2012, 04:53 PM

"When serverFiltering is enabled the developer is responsible for performing it on the server side. You can use the parameterMap function to translate the filter expression to something which you can parse easily on the server side."

This sounds great. How about an example? You build this component that submits parameters in a manner which I've never seen yet provide no example on how to use it? The page you reference appears to allow the developer to set pre-defined sorts, filters, and grouping based on pre-defined fields and values. What if I don't want the records to initially be filtered or sorted in any way. From the documentation is looks like all I'm left with is attempting to parse out the users selections on the server, which is what I'm trying to do. So how can this be done? None of the posts that ask this specific question has an answer from Telerik. Does Telerik even know?

answered on 17 Nov 2012, 08:22 AM

I ended up writing the following for my needs a while back. It should be easy enough to translate into whatever you need. Also you will want to do some optimizations before implementing directly as I've limited the sorting and filters to 20 etc. This has never been used in production by me so take it for what it is. Also I have a string extension for Parse going so you will need to do your own default values and casting.

public class GridRequestParameters

{

public int Page { get; set; }

public int PageSize { get; set; }

public int Skip { get; set; }

public int Take { get; set; }

public string FilterLogic { get; set; }

public IEnumerable<SortingInfo> Sortings { get; set; }

public IEnumerable<FilterInfo> Filters { get; set; }

public static GridRequestParameters Current

{

get

{

var p = new GridRequestParameters();

p.Populate();

return p;

}

//TODO: pull default values from config

internal void Populate()

{

if (HttpContext.Current != null)

{

HttpRequest curRequest = HttpContext.Current.Request;

this.Page = curRequest["page"].Parse<int>();

this.PageSize = curRequest["pageSize"].Parse(Configuration.Settings.GridDefaults.PageSize);

this.Skip = curRequest["skip"].Parse(0);

this.Take = curRequest["take"].Parse(Configuration.Settings.GridDefaults.QuerySize);

this.FilterLogic = curRequest["filter[logic]"].EmptyDefault("AND").ToUpper();

//build sorting objects

var sorts = new List<SortingInfo>();

var x = 0;

while (x < 20)

{

var sortDirection = curRequest["sort[" + x + "][dir]"];

if (sortDirection == null)

{

break;

}

var sortOn = curRequest["sort[" + x + "][field]"];

if (sortOn != null)

{

sorts.Add(new SortingInfo {SortOn = sortOn, SortOrder = sortDirection});

}

x++;

}

Sortings = sorts;

//build filter objects

var filters = new List<FilterInfo>();

x = 0;

while (x < 20)

{

var field = curRequest["filter[filters][" + x + "][field]"];

if (field == null)

{

break;

}

var val = curRequest["filter[filters][" + x + "][value]"] ?? string.Empty;

var strop = curRequest["filter[filters][" + x + "][operator]"];

if (strop != null)

{

filters.Add(new FilterInfo

{

Operator = FilterInfo.ParseOperator(strop),

Field = field,

Value = val

});

}

x++;

}

Filters = filters;

}

}

public class FilterInfo
    {
        public string Field { get; set; }
        public FilterOperations Operator { get; set; }
        public string Value { get; set; }
 
        public static FilterOperations ParseOperator(string theOperator)
        {
            switch (theOperator)
            {
                    //equal ==
                case "eq":
                case "==":
                case "isequalto":
                case "equals":
                case "equalto":
                case "equal":
                    return FilterOperations.Equals;
                    //not equal !=
                case "neq":
                case "!=":
                case "isnotequalto":
                case "notequals":
                case "notequalto":
                case "notequal":
                case "ne":
                    return FilterOperations.NotEquals;
                    // Greater
                case "gt":
                case ">":
                case "isgreaterthan":
                case "greaterthan":
                case "greater":
                    return FilterOperations.Greater;
                    // Greater or equal
                case "gte":
                case ">=":
                case "isgreaterthanorequalto":
                case "greaterthanequal":
                case "ge":
                    return FilterOperations.GreaterOrEquals;
                    // Less
                case "lt":
                case "<":
                case "islessthan":
                case "lessthan":
                case "less":
                    return FilterOperations.LessThan;
                    // Less or equal
                case "lte":
                case "<=":
                case "islessthanorequalto":
                case "lessthanequal":
                case "le":
                    return FilterOperations.LessThanOrEquals;
                case "startswith":
                    return FilterOperations.StartsWith;
 
                case "endswith":
                    return FilterOperations.EndsWith;
                    //string.Contains()
                case "contains":
                    return FilterOperations.Contains;
                case "doesnotcontain":
                    return FilterOperations.NotContains;
                default:
                    return FilterOperations.Contains;
            }
        }
    }


public class SortingInfo
{
    public string SortOrder { get; set; }
    public string SortOn { get; set; }
}


public enum FilterOperations
   {
       Equals,
       NotEquals,
       Greater,
       GreaterOrEquals,
       LessThan,
       LessThanOrEquals,
       StartsWith,
       EndsWith,
       Contains,
       NotContains,
   }

answered on 17 Nov 2012, 08:37 PM

Hi Dan,

Thanks for your reply. I did some coding on my end to translate filters to a SQL clause when server-side filtering is enabled in Kendo UI Grid and came up with following approach, which I have tried on my end and it seems to work so far. Of course, there is room for further improvement.

You need to define 4 methods in your aspx code-behind, 2 of which are custom in the sense that you need to change them for every databinding scenario, while the other 2 are generic methods that can be used across all databinding scenarios without any changes. The generic methods can be expanded to include more filter operators and more column types. At the moment, I have included only string, int and bool column types in these generic methods, and most probably not all filter operators, but its easy to add to these generic methods once you understand how these 4 methods interact with each other.

GetColumnType(string columnName) ( custom method)
GetDatabaseColumnName(string filterColumnName) ( custom method)
string GetSqlFilterClauseForAllColumns() ( generic method)
string GetSqlFilterClauseForColumn(string filterColumnName, string filterOperator, string filterValue) ( generic method).

The complete code for these methods is as given in code block below. NOTE: I have not included any sorting in this, but only filters.

UPDATE: I have not tried my methods when calling a static PageMethod in a aspx Webform, so use this code only if you are not calling a static PageMethod in your code-behind. I guess when using static PageMethod, we might have to change how we get the 'Request' object in these methods so that its obtained from 'System.Web.HttpContext.Current.Request'.
Thanks
Sunil

/// <summary>

/// Gets column's data type only for database columns.

/// For non-database columns YOU MUST return an empty string so it gets ignored when creating filter strings.

/// *****CUSTOM METHOD****** THAT YOU MUST CHANGE FOR EACH KENDO UI GRID

/// DATABINDING SCENARIO

/// </summary>

/// <param name="columnName"></param>

/// <returns></returns>

///

public string GetColumnType(string columnName)

{

if (columnName == "ProductId")

{

return "int";

}

else if (columnName == "ProductName")

{

return "string";

}

else if (columnName == "Name")

{

return "string";

}

return string.Empty;

}

/// <summary>

/// Maps the column name in Kendo Ui Grid to actual database columns

/// *****CUSTOM METHOD****** THAT YOU MUST CHANGE FOR EACH KENDO UI GRID

/// DATABINDING SCENARIO /// </summary>

/// <param name="filterColumnName"></param>

/// <returns></returns>

public string GetDatabaseColumnName(string filterColumnName)

{

if (filterColumnName == "CategoryName")

{

return "Name";

}

      else if (filterColumnName == "ProductId")
     {
         return "ProductId";
     }
  else if (filterColumnName == "ProductName")
     {
         return "ProductName";
     }

else //this else is there so SQL INJECTION is completely avoided

{

return string.Empty;

}

/// <summary>

/// Gets the filters translated to SQL for database columns only.

/// Filters on non-database columns like template columns are

/// ignored by this method.

/// ******THIS IS A GENERIC METHOD****** AND WILL BE THE SAME FOR ALL

/// SCENARIOS OF DATABINDING OF KENDO UI GRID

/// </summary>

/// <returns></returns>

public string GetSqlFilterClauseForAllColumns()

{

int i = 0;

string a = "filter[filters][{0}]";

string simpleFilterField = "filter[filters][{0}][field]";

string simpleFilterOperator = "filter[filters][{0}][operator]";

string simpleFilterValue = "filter[filters][{0}][value]";

string complexFilterOperator = "filter[filters][{0}][logic]";

string complexFilterField1 = "filter[filters][{0}][filters][0][field]";

string complexFilterOperator1 = "filter[filters][{0}][filters][0][operator]";

string complexFilterValue1 = "filter[filters][{0}][filters][0][value]";

string complexFilterField2 = "filter[filters][{0}][filters][1][field]";

string complexFilterOperator2 = "filter[filters][{0}][filters][1][operator]";

string complexFilterValue2 = "filter[filters][{0}][filters][1][value]";

string filterOperator = "filter[logic]";

string filterString = string.Empty;

while (true)

{

if (Request.Params[string.Format(simpleFilterField, i.ToString())] != null)

{

if (!string.IsNullOrEmpty(filterString))

{

filterString = filterString + " " + Request.Params[filterOperator] + " ";

}

if (Request.Params[string.Format(complexFilterOperator, i.ToString())] != null)

{

filterString = filterString + " (" + GetSqlFilterClauseForColumn(Request.Params[string.Format(complexFilterField1, i.ToString())], Request.Params[string.Format(complexFilterOperator1, i.ToString())], Request.Params[string.Format(complexFilterValue1, i.ToString())]) + " " + Request[string.Format(complexFilterOperator, i.ToString())] +

" " + GetSqlFilterClauseForColumn(Request.Params[string.Format(complexFilterField2, i.ToString())], Request.Params[string.Format(complexFilterOperator2, i.ToString())], Request.Params[string.Format(complexFilterValue2, i.ToString())]) + " ) ";

}

else

{

filterString = filterString + " " + GetSqlFilterClauseForColumn(Request.Params[string.Format(simpleFilterField, i.ToString())], Request.Params[string.Format(simpleFilterOperator, i.ToString())], Request.Params[string.Format(simpleFilterValue, i.ToString())]);

}

else

{

break;

}

i++;

}

return filterString;

}

/// <summary>

/// Gets the filter clause in SQL language for a database column.

/// Non-database columns are ignored by this method.

/// ******THIS IS A GENERIC METHOD****** AND WILL BE THE SAME FOR ALL

/// SCENARIOS OF DATABINDING OF KENDO UI GRID /// </summary>

/// <param name="filterColumnName"></param>

/// <param name="filterOperator"></param>

/// <param name="filterValue"></param>

/// <returns></returns>

public string GetSqlFilterClauseForColumn(string filterColumnName, string filterOperator, string filterValue)

{

    //get the actual database column name
     filterColumnName = GetDatabaseColumnName(filterColumnName);

if (filterColumnName == string.Empty)

//do not create a SQL clause for a non-database columns Or if user specified a column name different from what we are expecting. THIS WILL PREVENT SQL INJECTION.

{

return string.Empty;

}

//replace all single quote/s or double quotes with 2 single quotes to prevent any chance of SQL Injection

filterValue = filterValue.Replace("'","''");
filterValue = filterValue.Replace(""","''");

string sqlClause = string.Empty;

switch(GetColumnType(filterColumnName))

{

case "string":

if (filterOperator == "contains")

{

sqlClause = filterColumnName + " like '%" + filterValue + "%' ";

}

else if (filterOperator == "doesnotcontain")

{

sqlClause = filterColumnName + " not like '%" + filterValue + "%' ";

}

else if (filterOperator == "startswith")

{

sqlClause = filterColumnName + " like '" + filterValue + "%' ";

}

else if (filterOperator == "endswith")

{

sqlClause = filterColumnName + " like '" + filterValue + "%' ";

}

else if (filterOperator == "eq")

{

sqlClause = filterColumnName + " = '" + filterValue + "' ";

}

else if (filterOperator == "neq")

{

sqlClause = filterColumnName + " <> '" + filterValue + "' ";

}

break;

case "int":

if (filterOperator == "eq")

{

sqlClause = filterColumnName + " = " + filterValue + " ";

}

else if (filterOperator == "neq")

{

sqlClause = filterColumnName + " <> " + filterValue + " ";

}

else if (filterOperator == "lt")

{

sqlClause = filterColumnName + " < " + filterValue + " ";

}

else if (filterOperator == "lte")

{

sqlClause = filterColumnName + " <= " + filterValue + " ";

}

else if (filterOperator == "gt")

{

sqlClause = filterColumnName + " > " + filterValue + " ";

}

else if (filterOperator == "gte")

{

sqlClause = filterColumnName + " >= " + filterValue + " ";

}

break;

case "bool":

if (filterOperator == "eq" && filterValue == "true")

{

sqlClause = filterColumnName + " = cast( 1 as bit) ";

}

else if (filterOperator == "eq" && filterValue == "false")

{

sqlClause = filterColumnName + " = cast( 0 as bit) ";

}

break;

default: //column is not a database column but a template column, so we ignore it

break;

}

return sqlClause;

}

answered on 17 Nov 2012, 11:46 PM

That's a good point. So how would you create SQL that is based on dynamically generated filters? Use stored procedure with parameters?

I don't think any SQL Injection would be possible, if the dynamic SQL is created inside a stored procedure, and this stored procedure has a parameter for each column value, each column's filter operator and each column's filter value. Or you think even then there may be some scope of SQL injection?

UPDATE: I think when using the approach I mentioned in last post, SQL Injection would not happen, since the column name is being decided by the method GetDatabaseColumnName. If this method is implemented so that a column name other than any one from a fixed set of column names would result in an empty string being returned as the database column name, which then causes the method I have GetSqlFilterClauseForColumn to return an empty string for filter even though you could have specified a dangerous filter value of 'delete from products'.
On the other hand, if you do specify a correct column name with a dangerous filter value of 'delete from products', then it will have NO effect, since we would end up with a query as below using the methods I have in place, which does NO HARM at all. So it appears that my approach is 100% SQL INJECTION proof if developer handles the GetDatabaseColumnName method to return an empty string when the column name does not match any one of the list of column names.

SELECT * FROM LargeProducts lp WHERE ProductName = 'delete from Products'

Also, I am replacing all single quotes and double quotes passed within the filter value by two single quotes in GetFilterClauseForColumn, which will make sure that any attempt to attack through a drop or delete query would not happen.
filterValue = filterValue.Replace("'","''");
filterValue = filterValue.Replace(@"""","''");UPDATE:I think to be 100% injection proof, we have to use ADO.Net parameters in our dynamic SQL. Even after including some checks in my methods, there is still a chance of SQL Injection.

answered on 18 Nov 2012, 07:26 AM

Here is another solution after Dan pointed out that my original approach was prone to SQL Injection. This new approach uses parameterized dynamic SQL . To use this simply use sample code like below code below.

SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["northwndConnectionString"].ConnectionString);

string sql = @

"with x as (SELECT  distinct   row_number() over (  order by ProductId asc ) AS RowNumber, ProductID, ProductName,  name as CategoryName FROM LargeProducts p INNER JOIN Category c ON c.ID = p.CategoryID  {0} )  "

+

@"SELECT * FROM x where RowNumber > @rowNum1 and RowNumber < =@rowNum2 ORDER BY RowNumber asc;";

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

cmd.CommandType = CommandType.Text;

cmd.Parameters.AddWithValue("@rowNum1", skip);

cmd.Parameters.AddWithValue("@rowNum2", (skip + take));

string filterOperator = Request.Params["filter[logic]"];

sqlWhereClauseParameterized = CreateParameterizedDynamicSQL(filters, filterOperator, cmd);

cmd.CommandText = string.Format(sql, string.IsNullOrEmpty(sqlWhereClauseParameterized) ? string.Empty : " where " + sqlWhereClauseParameterized);

You should include following 4 methods in your server-side code. Two of these are custom methods which you need to implement appropriately. the other 2 are generic methods and you don;t need to do anything to them except improve them with better or more functionality.

/// <summary>

/// Gets column's data type only for database columns.

/// For non-database columns YOU MUST return an empty string so it gets ignored when creating filter strings.

/// *****CUSTOM METHOD****** THAT YOU MUST CHANGE FOR EACH KENDO UI GRID

/// DATABINDING SCENARIO

/// </summary>

/// <param name="columnName"></param>

/// <returns></returns>

///

public static string GetColumnType(string columnName)

{

if (columnName == "ProductId")

{

return "int";

}

else if (columnName == "ProductName")

{

return "string";

}

else if (columnName == "Name")

{

return "string";

}

return string.Empty;

}

/// <summary>

/// Maps the column name in Kendo Ui Grid to actual database columns

/// *****CUSTOM METHOD****** THAT YOU MUST CHANGE FOR EACH KENDO UI GRID

/// DATABINDING SCENARIO /// </summary>

/// <param name="filterColumnName"></param>

/// <returns></returns>

public static string GetDatabaseColumnName(string filterColumnName)

{

if (filterColumnName == "CategoryName")

{

return "Name";

}

else if (filterColumnName == "ProductName")

{

return "ProductName";

}

else if (filterColumnName == "ProductId")

{

return "ProductId";

}

return string.Empty;

}

/// <summary>

/// Gets the filters for all columns, including multiple filters for a column.

/// Filters are retuned as a collection. Each List within the outer List

/// represents multiple filters for the same column.

/// ******THIS IS A GENERIC METHOD****** AND WILL BE THE SAME FOR ALL

/// SCENARIOS OF DATABINDING OF KENDO UI GRID

/// </summary>

/// <returns></returns>

public static List<List<Filter>> GetSqlFiltersForAllColumns()