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

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.

When using a strongly-typed platform, you can't inject code into a query with a bool, int, or datetime value. What you can do is kill performance by forcing sql server to re-compile every query, and by preventing it from getting good statistics on what queries are run with what frequency (which hurts cache management).

Really, there's no good reason not to use query parameters for these values. It's the right way to go about this. Go ahead and hard-code values into the sql string when they really are constants, but otherwise, why not just use a parameter? It's not like it's hard.

I also like to think long-term. What happens when today's old-and-busted strongly-typed code base gets ported via automatic translation to the new-hotness dynamic language, and you suddenly lose the type checking, but don't have all the right unit tests yet for the dynamic code?

In fact, parameterizing works only if you are using it unconditionally, all the way around. The moment you start thinking if you have to use a parameter or not, you put yourself in danger.

I have been thinking on the matter for a while, resulting in the article (though based on the PHP environment) where I tried to sum everything up. It occurred to me that the question of protection from SQL injection is often substituted with some other topics, like string escaping, type casting and such. Although some of the measures can be considered safe when used by themselves, there is no system, nor a simple rule to follow. Which makes it very slippery ground, putting too much on the developer's attention and experience.

And eventually I found that prepared statements is indeed The Holy Grail:

• it can be expressed in the form of one simple rule that is easy to follow.
• it is essentially undetacheable measure, means the developer cannot interfere, and, willingly or unwillingly, spoil the process.
• protection from injection is really only a side effect of the prepared statements, which real purpose is to produce syntactically correct statement. And a syntactically correct statement is 100% injection proof. Yet we need our syntax to be correct despite of any injection possibility.
• if used all the way around, it protects the application regardless of the developer's experience. Say, there is a thing called second order injection. And a very strong delusion that reads "in order to protect, Escape All User Supplied Input". Combined together, they lead to injection, if a developer takes the liberty to decide, what needs to be protected and what not.

Thinking further, I discovered that current set of placeholders is not enough for the real life needs and have to be extended, both for the complex data structures, like arrays, and even SQL keywords or identifiers, which have to be sometimes added to the query dynamically too, but a developer is left unarmed for such a case, and forced to fall back to string concatenation.

In my opinion if you can guarantee that the parameter you working with can never be contain a string it is safe but I would not do it in any case. Also you will see a slight performance drop due to the fact that you are performing concatenation. The question I would ask you is why don't you want to use parameters?

Edit

When I say "parameter," think something like public int Query(int id).

The short answer is Yes, it is OK.

Attackers can never inject anything in the int argument of method.

Maybe off-topic but in performance point of view, it's better to use parameters, so they will be compiled once and cached for next usage.

Original

Even when the field is integer, your query may be subject to SQL Injection.

Suppose you have a query in your application:

"SELECT * FROM Product WHERE Id=" + TextBox1.Text

An attacker can insert into TextBox1

1; DELETE Product

If you don't want to use parametrized query here, you should use typed values:

string.Format("SELECT * FROM Product WHERE Id={0}", int.Parse(TextBox1.Text))

Let's not just think about security or type-safe considerations.

The reason you use parametrized queries is to improve performance at the database level. From a database perspective, a parametrized query is one query in the SQL buffer (to use Oracle's terminology although I imagine all databases have a similar concept internally). So, the database can hold a certain amount of queries in memory, prepared and ready to execute. These queries do not need to be parsed and will be quicker. Frequently run queries will usually be in the buffer and will not need parsing every time they are used.

UNLESS

Somebody doesn't use parametrized queries. In this case, the buffer gets continually flushed through by a stream of nearly identical queries each of which needs to be parsed and run by the database engine and performance suffers all-round as even frequently run queries end up being re-parsed many times a day. I have tuned databases for a living and this has been one of the biggest sources of low-hanging fruit.

NOW

To answer your question, IF your query has a small number of distinct numeric values, you will probably not be causing issues and may in fact improve performance infinitesimally. IF however there are potentially hundreds of values and the query gets called a lot, you are going to affect the performance of your system so don't do it.

Yes you can increase the SQL buffer but it's always ultimately at the expense of other more critical uses for memory like caching Indexes or Data. Moral, use parametrized queries pretty religiously so you can optimize your database and use more server memory for the stuff that matters...

In some cases, it IS possible to perform SQL injection attack with non-parametrized (concatenated) variables other than string values - see this article by Jon: http://codeblog.jonskeet.uk/2014/08/08/the-bobbytables-culture/ .

Thing is that when ToString is called, some custom culture provider can transform a non-string parameter into its string representation which injects some SQL into the query.