I have a small situation here. I am stuck with a commercial server that does not do much sanitation / parameterization.
I am trying to create my queries to prevent SQL Injection, however some things, such as the search / where clause in the search object, need to be created and there is no parameterized interface there.
In principle, I can’t parameterize, but I was hoping that I could use the same engine to GET the query text, if possible. Is there a way to do this other than writing my own parameterization mechanism, which is probably still not as good as parameterized queries?
Update: Example
The where clause should be created as a sql query, where the clause is 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);
* The above example is how you refine your search, however we did some testing, this line is NOT SANITIZED .
This is where my problem is because any of these inputs in .Format can be entered by the user, and although I can easily clear my input from text fields, I'm going to skip extreme cases, this is just the nature of things. I have no way to use a parameterized query because Commerce Server has some crazy backward logic in how it handles an extensible set of fields (schemas) and free text search words are precompiled. This means that I cannot go directly to sql tables
What I would like / love / see is something like:
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();