You can parameterize each value, so something like:
string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";
string[] paramNames = tags.Select(
(s, i) => "@tag" + i.ToString()
).ToArray();
string inClause = string.Join(", ", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
for(int i = 0; i < paramNames.Length; i++) {
cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
}
}
Which will give you:
cmd.CommandText = "SELECT * FROM Tags WHERE Name IN (@tag0, @tag1, @tag2, @tag3)"
cmd.Parameters["@tag0"] = "ruby"
cmd.Parameters["@tag1"] = "rails"
cmd.Parameters["@tag2"] = "scruffy"
cmd.Parameters["@tag3"] = "rubyonrails"
No, this is not open to SQL injection. The only injected text into CommandText is not based on user input. It's solely based on the hardcoded "@tag" prefix, and the index of an array. The index will always be an integer, is not user generated, and is safe.
The user inputted values are still stuffed into parameters, so there is no vulnerability there.
Edit:
Injection concerns aside, take care to note that constructing the command text to accomodate a variable number of parameters (as above) impede's SQL server's ability to take advantage of cached queries. The net result is that you almost certainly lose the value of using parameters in the first place (as opposed to merely inserting the predicate strings into the SQL itself).
Not that cached query plans aren't valuable, but IMO this query isn't nearly complicated enough to see much benefit from it. While the compilation costs may approach (or even exceed) the execution costs, you're still talking milliseconds.
If you have enough RAM, I'd expect SQL Server would probably cache a plan for the common counts of parameters as well. I suppose you could always add five parameters, and let the unspecified tags be NULL - the query plan should be the same, but it seems pretty ugly to me and I'm not sure that it'd worth the micro-optimization (although, on Stack Overflow - it may very well be worth it).
Also, SQL Server 7 and later will auto-parameterize queries, so using parameters isn't really necessary from a performance standpoint - it is, however, critical from a security standpoint - especially with user inputted data like this.
Best Answer
Install the additional module
tablefunc
once per database, which provides the functioncrosstab()
. Since Postgres 9.1 you can useCREATE EXTENSION
for that:Improved test case
Simple form - not fit for missing attributes
crosstab(text)
with 1 input parameter:Returns:
C
: the value7
is filled in for the first column. Sometimes, this behavior is desirable, but not for this use case.Safe form
crosstab(text, text)
with 2 input parameters:Returns:
Note the correct result for
C
.The second parameter can be any query that returns one row per attribute matching the order of the column definition at the end. Often you will want to query distinct attributes from the underlying table like this:
That's in the manual.
Since you have to spell out all columns in a column definition list anyway (except for pre-defined
crosstabN()
variants), it is typically more efficient to provide a short list in aVALUES
expression like demonstrated:Or (not in the manual):
I used dollar quoting to make quoting easier.
You can even output columns with different data types with
crosstab(text, text)
- as long as the text representation of the value column is valid input for the target type. This way you might have attributes of different kind and outputtext
,date
,numeric
etc. for respective attributes. There is a code example at the end of the chaptercrosstab(text, text)
in the manual.db<>fiddle here
Advanced examples
Pivot on Multiple Columns using Tablefunc - also demonstrating mentioned "extra columns"
Dynamic alternative to pivot with CASE and GROUP BY
\crosstabview
in psqlPostgres 9.6 added this meta-command to its default interactive terminal psql. You can run the query you would use as first
crosstab()
parameter and feed it to\crosstabview
(immediately or in the next step). Like:Similar result as above, but it's a representation feature on the client side exclusively. Input rows are treated slightly differently, hence
ORDER BY
is not required. Details for\crosstabview
in the manual. There are more code examples at the bottom of that page.Related answer on dba.SE by Daniel Vérité (the author of the psql feature):
The previously accepted answer is outdated.
The variant of the function
crosstab(text, integer)
is outdated. The secondinteger
parameter is ignored. I quote the current manual:Needless casting and renaming.
It fails if a row does not have all attributes. See safe variant with two input parameters above to handle missing attributes properly.
ORDER BY
is required in the one-parameter form ofcrosstab()
. The manual: