Is there a better way to split an array into a string

Question

I am trying to pass an Array to a method and then return a string I can use for a SQL WHERE clause. I have the following and it works just fine. But is there a better way? I am looking for one of the two results;

WHERE (ColumnName IN (12, 34, 56, 78, 90))
WHERE (ColumnName IN ('12', '34', '56', '78', '90'))

    public static string setInSearchFilter(string psSearchFilter, string psColumnName, 
        string[] paObjectValues, bool pbIsString)
    {
        string lsDelimiter = "'", lsRetVal = string.Empty, lsObjectValues = string.Empty;

        if (!pbIsString)
        {
            lsDelimiter = string.Empty;
        }

        if (!string.IsNullOrEmpty(psSearchFilter))
        {
            lsRetVal = psSearchFilter + " AND ";
        }

        for (int i = 0; i <= paObjectValues.GetUpperBound(0); i++)
        {
            lsObjectValues += lsDelimiter + paObjectValues[i] + lsDelimiter;

            if (i < paObjectValues.GetUpperBound(0))
            {
                lsObjectValues += ", ";
            }
        }

        return lsRetVal += "(" + psColumnName + " IN (" + lsObjectValues + "))";
    }

See string.Join and the many duplicates. FWIW: I recommend using (dynamically added) placeholders, as SQL is SQL. — user166390
– user166390, Commented Nov 6, 2012 at 21:17
Have you looked into paramaterized queries or other forms of sanitized input? This code is just begging for a sql injection attack. — tmesser
– tmesser, Commented Nov 6, 2012 at 21:18
@YYY - Yes, I will be doing a paramaterized query. Just trying to get this to work first. — ggrewe1959
– ggrewe1959, Commented Nov 6, 2012 at 21:45

McGarnagle · Accepted Answer · 2012-11-06 21:39:54Z

1

As suggested in the comments, string.Join:

string.Format(
    "({0}" + string.Join("{0},{0}", paObjectValues) + "{0})",
    lsDelimiter 
);

This assumes there's something in the list, so make sure to throw or return if the filter set is empty.

You might also consider doing some kind of validation on the items to prevent SQL injection:

if (paObjectValues.Any(item => !int.TryParse(item))) 
    throw new Exception("Items must be numeric");

edited Nov 6, 2012 at 21:39

answered Nov 6, 2012 at 21:26

McGarnagle

103k31 gold badges235 silver badges264 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marc Gravell · Accepted Answer · 2012-11-06 21:25:51Z

1

I would add parameters for every option (allowing query-plan re-use and injection-safety), or I would investigate a "split" UDF. Or, better, I'd use something like an ORM or micro-ORM which may often have this type of capability built in. For example, with LINQ you can often use an array/list and Contains. Or with "dapper" you can use a subtle variant of the IN syntax which expands the inputs into separate parameters - specifically: where x.Foo in @bar becomes where x,Foo in (@bar0, @bar1, @bar2) if the "bar" parameter has 3 values.

answered Nov 6, 2012 at 21:25

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Collectives™ on Stack Overflow

Is there a better way to split an array into a string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related