c# - How to pass a custom subquery to SQL command in ADO.NET?

Question

I often have to construct long and difficult queries "in code". I know, that I can pass any parameters using SqlDataAdapter.SelectCommand.Parameters.Add() method. But what should I do if i need in some cases make multiple concatenations like this:

var subquery = ConstructSubquery(param1, paramN);
query += subquery;

And so on. Code analyzer in VS offers me to use parameters instead of simple string concatenation, but something like

dataBaseSQL.SqlDataAdapter.SelectCommand.Parameters.Add("@subquery", SqlDbType.VarChar).Value = subquery;
//subquery = "WHERE param1 IN ('A', 'Z') AND param2 <> param3" (for example...)

does not work, of course! Queries are dynamic, subqueries are generated by special functions. What is the best decision?

question from:https://stackoverflow.com/questions/65849478/how-to-pass-a-custom-subquery-to-sql-command-in-ado-net

Answer 1

Parameters are for values, not for statements (or pieces of statements).

A and Z are values that can be put in a parameter (let's say @value1 and @value2) (and we will ignore the complexity of doing the IN operator using a variable number of parameters... there is an hard limit on the number of parameters in SQL Server, 2100). So for example WHERE param1 IN (@value1, @value2) is perfectly ok...

You can't put the whole WHERE param1 IN (@value1, @value2) in a parameter.

You shouldn't/mustn't let the user write SQL statements or piece of SQL statements (so no SELECT or WHERE written by the user), it is very highly insecure, and I don't think it can really be secured.

Even letting the user fill the name of columns to be selected can be insecure. Always ask yourself: what happens if the user writes, as the column name, for example?

-- DROP TABLE SomeTable --

And even letting the user select the column names from a "closed list" could be insecure, because 9 times out of 10 you won't check if the user manipulated the javascript of the page to write whatever he wants in the combobox/listbox (it is quite easy... press F12 on Chrome and the whole javascript of the page is in your hands).