Using an arbitrary number of parameters in ORMLite Query

Question

I am in the process of fixing some of our bad sql queries that are vulnerable to sql injection. Most are straight queries with no inputs, but our search field takes search terms that are not parameterised. A snippet is below:

using (var db = ORMLite.Open())
{
    StringBuilder sb = new StringBuilder();
    sb.Append("select * from column1, column2");

    if (terms.Count() > 0)
    {
        sb.Append("where (column1 like '%@term0%' or " + column2 + " like '%@term0%') ");
        if (terms.Count() > 1)
        {
            for (int i = 1; i < terms.Count(); i++)
            {
                sb.Append("and (column1 like '%@term" + i + "%' or " + column2 + " like '%@term" + i + "%') ");
            }
        }
    }

    List<POCO> testQuery = db.Select<POCO>(sb.ToString());
}

The @term components are where I intend to use parameters (they used to be of the form '" + term[i] + '", but any term with malicious code would just be inserted. When I move to my select statement, I would like to add the parameters. This is normally done as so:

List testQuery = db.Select(sb.ToString(), new { term0 = "t", term1 = "te", term2 = "ter" });

However I can have any number of terms (term.count() is the number of terms). How can I pass in an anonymous object with any number of terms? Or is there a better method?

Answer 1

I'm looking for almost the same thing in Postgresql. Based on this SO question the answer looks like "you have to perform multiple queries."

I can get the unique row IDs from my table given the partial parameterized query, and then directly paste those unique IDs back into the query -- since those row IDs will be safe.

Here's an example of what I mean, but the c# is probably wrong (sorry):

string query = "SELECT unique_id FROM table WHERE (column1 LIKE '%@term%' OR column2 LIKE '%@term%')";
string safeIDs;
List uniqueRowIDs = db.Select(query, new {term = term[0]});

for (int i = 1; i < terms.Count(); i++) {
    // Loop to narrow down the rows by adding the additional conditions.
    safeIDs = uniqueRowIDs.Aggregate( (current, next) => current + string.Format(", '{0}'", next) );

    uniqueRowIDs = db.Select(
            query + string.Format(" AND unique_id IN ({0})", safeIDs),
            new {term = term[i]});
}
// And finally make the last query for the chosen rows:
safeIDs = uniqueRowIDs.Aggregate( (current, next) => current + string.Format(", '{0}'", next) );
List testQuery = db.Select(string.Format("SELECT * FROM table WHERE unique_id IN ({0});", safeIDs));

Another option for your case specifically could be to just get all of the values that are like term0 using a parameterized query and then, within the c# program, compare all of the results against the remaining terms the user entered.