Constructing Query for Where clause

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).

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

Regards Chetan

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!

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);

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 |(identity)GlobalAdministrator%}) == MyValue in ('abc','def')