Constructing Query for Where clause

Chetan Sharma asked on March 16, 2016 14:26

Hi All,

I need some help constructing this query using K# or some server side method.

  1. Right now I send a category like in the URL htp://website.com/?cat=Business and have a query like this to handle and filter the records.

BlogCategory like Case '{?cat|(default)All?}' When 'All' Then BlogCategory Else '%{?cat|(default)All?}%' END

Now we have a change in requirement that will allow users to select multiple categories and we are thinking of sending it like this htp://website.com/?cat=Business|People and write some query that can help me fetch records for both categories i.e Business and People. A Blog can only be assigned to only one category.

  1. Now we would also like to provide our users to filter records using Keywords which we are using right now like this

/FilterByAuthor.aspx?tagname=Cooperative&groupid=4

We would also like to give them ability to select multiple keywords for filtering same as above.

Any help or direction would be highly appreciated.

Thanks, Chetan

Correct Answer

Chetan Sharma answered on March 19, 2016 10:00

Thanks Trevor for sharing your code. I had to modify this a bit to suit my needs. I tried this today. Yesterday I didn't have access to my FTP server so I ahd to resort on using Transformatios + macros. I devised this to resolve my problem and it worked pretty well.

This problem has to approaches. The one shared by Trevor in his code and the one done by me which is kind of a quick hack.

Trevor's approach will give you flexibility to call method in any transformation. However if you are looking for a one time use then my approach will work neatly.

{% z = ""; foreach (x in QueryString.GetValue("cat").Split("|")) { z += "BlogCategory like '" + x + "' OR "}; z.TrimEnd(" OR ") |(handlesqlinjection)false #%} AND

({% z = ""; foreach (x in QueryString.GetValue("Author").Split("|")) { z += "Author like '" + x + "' OR "}; z.TrimEnd(" OR ") |(handlesqlinjection)false #%})
1 votesVote for this answer Unmark Correct answer

Recent Answers


Trevor Fayas answered on March 16, 2016 15:36 (last edited on March 16, 2016 15:38)

To do this you have a couple options. Safest is to create a custom macro method that renders a character separated string into a proper in (ex in ('val1','val2') ), but make sure in that method to escape quotes of the listed items, and in the macro then turn escape quotes of.

If you need an example code just tell me, I have the method somewhere on one of my previous sites.

Other option is to, in the macro, split the query string value and then usr string.join to do the same, but you will probably lose the ability to escape the split items and thus open up to security attacks (unless there's already a macro method to escape quotes).

0 votesVote for this answer Mark as a Correct answer

Chetan Sharma answered on March 16, 2016 15:43

Thanks Trevor for your generosity. That will be perfect if you could share the code example.

Regards Chetan

0 votesVote for this answer Mark as a Correct answer

Trevor Fayas answered on March 16, 2016 15:46

I'll put a note to get to it later today, I'm out in meetings all day and don't have access to my computer, but check back later!

0 votesVote for this answer Mark as a Correct answer

Zachary Perry answered on March 16, 2016 21:41

Should be able to use these methods for escaping quotes and other possible SQL Injection issues:

 HTMLHelper.HTMLEncode(result);
 SqlHelper.EscapeQuotes(result);
 SqlHelper.EscapeLikeQueryPatterns(result);
 SqlHelper.EscapeLikeText(result);
2 votesVote for this answer Mark as a Correct answer

Trevor Fayas answered on March 17, 2016 01:39

Sorry for the delay, long day!

Place this in a CustomMacroMethods.cs in your App_Code (i would make a subfolder of your site codename if you want to keep good coding standards)

using CMS.MacroEngine;
using CMS.Helpers;
using System.Linq;
using System.Collections.Generic;
using CMS.DataEngine;
using System;
using CMS;

// Makes all methods in the 'CustomMacroMethods' container class available for string objects
[assembly: RegisterExtension(typeof(CustomMacroMethods), typeof(string))]
// Registers methods from the 'CustomMacroMethods' container into the "String" macro namespace
[assembly: RegisterExtension(typeof(CustomMacroMethods), typeof(UtilNamespace))]

public class CustomMacroMethods : MacroMethodContainer
{
    [MacroMethod(typeof(string), "Takes a character seperated list of values and renders them into a comma seperated list with '' around them. Ex: abc|def would be resolved 'abc','def'", 1)]
    [MacroMethodParam(0, "DelimeterSeperatedValue", typeof(string), "The Character seperated list")]
    [MacroMethodParam(1, " Delimeter", typeof(string), "The character that seperates the items, defaults to | when not defined.")]
    public static object GenerateWhereIn(EvaluationContext context, params object[] parameters)
    {
        // Branches according to the number of the method's parameters
        switch (parameters.Length)
        {
            case 1:
                // Overload with one parameter
                return GenerateWhereIn(ValidationHelper.GetString(parameters[0], ""));

            case 2:
                // Overload with two parameters
                return GenerateWhereIn(ValidationHelper.GetString(parameters[0], ""), ValidationHelper.GetString(parameters[1], "|")[0]);

            default:
                // No other overloads are supported
                throw new NotSupportedException();
        }
    }

    [MacroMethod(typeof(string), "Escapes any single quotes and replaces with double quotes for SQL queries", 1)]
    [MacroMethodParam(0, "Value", typeof(string), "The Value to Escape")]
    public static object EscapeQuotes(EvaluationContext context, params object[] parameters)
    {
        // Branches according to the number of the method's parameters
        switch (parameters.Length)
        {
            case 1:
                // Overload with one parameter
                return SqlHelper.EscapeQuotes(GenerateWhereIn(ValidationHelper.GetString(parameters[0], "")));
            default:
                // No other overloads are supported
                throw new NotSupportedException();
        }
    }

    private static string GenerateWhereIn(string value, char delimeter = '|')
    {
        List<string> values = value.Split(delimeter).ToList();
        values.ForEach(x => SqlHelper.EscapeQuotes(x));
        return "'"+string.Join("','", values.ToArray()) + "'";
    }
}

To use this, here's how you call it:

MyValue in ({% "abc|def".GenerateWhereIn() |(handlesqlinjection)false #%}) ==> MyValue in ('abc','def')

MyValue in ({% "".GenerateWhereIn() |(handlesqlinjection)false #%}) ==> MyValue in ('')

MyValue in ({% "abc;def".GenerateWhereIn(";") |(handlesqlinjection)false #%}) ==> MyValue in ('abc','def')

MyValue in ({% Util.GenerateWhereIn("abc;def", ";")|(handlessqlinjection)false #%}) == MyValue in ('abc','def')

0 votesVote for this answer Mark as a Correct answer

Chetan Sharma answered on March 19, 2016 10:03 (last edited on March 19, 2016 10:03)

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.