Parameterized Queries /Without/ using queries.
- by Aren B
I've got a bit of a poor situation here. I'm stuck working with commerce server, which doesn't do a whole lot of sanitization/parameterization.
I'm trying to build up my queries to prevent SQL Injection, however some things like the searches / where clause on the search object need to be built up, and there's no parameterized interface.
Basically, I cannot parameterize, however I was hoping to be able to use the same engine to BUILD my query text if possible. Is there a way to do this, aside from writing my own parameterizing engine which will probably still not be as good as parameterized queries?
Update: Example
The where clause has to be built up as a sql query where clause essentially:
CatalogSearch search = /// Create Search object from commerce server
search.WhereClause = string.Format("[cy_list_price] > {0} AND [Hide] is not NULL AND [DateOfIntroduction] BETWEEN '{1}' AND '{2}'", 12.99m, DateTime.Now.AddDays(-2), DateTime.Now);
*Above Example is how you refine the search, however we've done some testing, this string is NOT SANITIZED.
This is where my problem lies, because any of those inputs in the .Format could be user input, and while i can clean up my input from text-boxes easily, I'm going to miss edge cases, it's just the nature of things. I do not have the option here to use a parameterized query because Commerce Server has some insane backwards logic in how it handles the extensible set of fields (schema) & the free-text search words are pre-compiled somewhere. This means I cannot go directly to the sql tables
What i'd /love/ to see is something along the lines of:
SqlCommand cmd = new SqlCommand("[cy_list_price] > @MinPrice AND [DateOfIntroduction] BETWEEN @StartDate AND @EndDate");
cmd.Parameters.AddWithValue("@MinPrice", 12.99m);
cmd.Parameters.AddWithValue("@StartDate", DateTime.Now.AddDays(-2));
cmd.Parameters.AddWithValue("@EndDate", DateTime.Now);
CatalogSearch search = /// constructor
search.WhereClause = cmd.ToSqlString();
