Is it safe to not parameterize an SQL query when the parameter is not a string?

johnnyRose picture johnnyRose · Sep 18, 2015 · Viewed 10.7k times · Source

In terms of SQL injection, I completely understand the necessity to parameterize a string parameter; that's one of the oldest tricks in the book. But when can it be justified to not parameterize an SqlCommand? Are any data types considered "safe" to not parameterize?

For example: I don't consider myself anywhere near an expert in SQL, but I can't think of any cases where it would be potentially vulnerable to SQL injection to accept a bool or an int and just concatenate it right into the query.

Is my assumption correct, or could that potentially leave a huge security vulnerability in my program?

For clarification, this question is tagged which is a strongly-typed language; when I say "parameter," think something like public int Query(int id).

Answer

Rob picture Rob · Sep 18, 2015

I think it's safe... technically, but it's a terrible habit to get into. Do you really want to be writing queries like this?

var sqlCommand = new SqlCommand("SELECT * FROM People WHERE IsAlive = " + isAlive + 
" AND FirstName = @firstName");

sqlCommand.Parameters.AddWithValue("firstName", "Rob");

It also leaves you vulnerable in the situation where a type changes from an integer to a string (Think employee number which, despite its name - may contain letters).

So, we've changed the type of EmployeeNumber from int to string, but forgot to update our sql queries. Oops.