A senior developer on my team tells me that writing SQL commands like this is safe "because it's parameterized", but I don't see how

Question

I'm writing some CRUD functions for media associated with certain products. To delete many records, I've been told to write the query as follows:

dataContext.ExecuteCommand("DELETE FROM ProductMedia WHERE ProductId = {0}", productId);

He says this is safe from SQL injection because it's 'parameterized'. I don't agree, but I also don't know a better way.

How do you protect from SQL injection when writing commands in this way?

Answer 1

He says this is safe from SQL injection because it's 'parameterized'.

As per the docs, the code in your question is perfectly valid and will be parameterised correctly:

The syntax for the command is almost the same as the syntax used to create an ADO.NET DataCommand. The only difference is in how the parameters are specified. Specifically, you specify parameters by enclosing them in braces ({…}) and enumerate them starting from 0. The parameter is associated with the equally numbered object in the parameters array.

It is essentially a wrapper - and it takes care of creating the necessary SqlParameter objects and assigning values to them. Other ORMs (like PetaPoco) have similar mechanisms.

Generally, the thing you want to avoid is string concatenation (or something that is effectively concatenation - like string.Format) - which this solution does not use. String concatenation is almost always open to SQL Injection. But, again, the code in your question is fine.