c# - Dynamically created SQL vs Parameters in SQL Server

Question

If I were to select a row from a table I basically have two options, either like this

int key = some_number_derived_from_a_dropdown_or_whatever
SqlCommand cmd = new SqlCommand("select * from table where primary_key = " + key.ToString());

or use a parameter

SqlCommand cmd = new SqlCommand("select * from table where primary_key = @pk");
SqlParameter param  = new SqlParameter();
param.ParameterName = "@pk";
param.Value         = some_number_derived_from_a_dropdown_or_whatever;
cmd.Parameters.Add(param);

Now, I know the first method is frowned upon because of possible sql injection attacks, but in this case the parameter is a integer and thus should not really be possible to inject malicious code.

My question is this: Do you use option 1 in production code because you consider the use safe because of ease of use and control over the inserted parameter (like the above, or if the parameter is created in code)? Or do you always use parameters no matter what? Are parameters 100% injection safe ?

Answer 1

I'll skip the SQL Injection argument, that is just too well known and just focus on the SQL aspect of parameters vs. non parameters.

When you send a SQL batch to the server, any batch, it has to be parsed to be comprehended. Like any other compiler, the SQL compiler has to produce an AST from the text and then operate on the syntax tree. Ultimately the optimizer transforms the syntax tree into an execution tree and finally produces an execution plan and that is actually run. Back in the dark ages of circa 1995 it made a difference if the batch was an Ad-Hoc query or a stored procedure, but today it makes absolutely none, they all the same.

Now where parameters make a difference is that a client that sends a query like select * from table where primary_key = @pk will send exactly the same SQL text every time, no matter what value is interested in. What happens then is that the entire process I described above is short-circuited. SQL will search in memory an execution plan for the raw, unparsed, text it received (based on a hash digest of the input) and, if found, will execute that plan. That means no parsing, no optimization, nothing, the batch goes straight into execution. On OLTP systems that run hundreds and thousands of small requests every second, this fast path makes a huge performance difference.

If you send the query in the form select * from table where primary_key = 1 then SQL will have to at least parse it to understand what is inside the text, since the text is likely a new one, different from any previous batch it seen (even a single character like 1 vs. 2 makes the entire batch different). It will then operate on the resulted syntax tree and attempt a process called Simple Parameterisation. If the query can be auto-paremeterized, then SQL will likely find a cached execution plan for it from other queries that run previously with other pk values and reuse that plan, so at least your query does not need to be optimized and you skip the step of generating an actual execution plan. But by no mean did you achieve the complete short-circuit, the shortest possible path you achieve with a true client parameterized query.

You can look into the SQL Server, SQL Statistics Object performance counter of your server. The counter Auto-Param Attempts/sec will show many times per second SQL has to translate a query received without parameters into an auto-parameterized one. Each attempt can be avoided if you properly parameterize the query in the client. If you also have a high number of Failed Auto-Params/sec is even worse, it means the queries are going the full cycle of optimization and execution plan generation.