When can you safely use non-parameterized variables in SQL commands?

Question

I am writing a select request in a C # method that allows the client code to provide a row identifier and return an object built from the row data. If the string id is an integer and I confirmed it is positive, is there any harm just passing it using string.Format? I do not see how it would be possible to cause damage if they were allowed to pass only int, not a string.

Answer 1

You are correct, it would be safe to pass an integer in this way. However, there is another side to the story.

Although it can be considered safe to just format int to create an SQL expression, performance is also considered. When the SQL server first sees the query, it will create and close a execution plan for this query. The next time the same request is issued, the execution plan will be reused.

If you pass different lines, this will be considered as separate requests requiring separate execution plans. If you send the same parameterized query each time (with different parameters), the first execution plan will be reused by SQL Server.

Even if you do not care about performance, I will still use a parameterized query for all database queries, even for those that can be considered “safe”, as you indicate, just to be consistent in how the application accesses the data. If you always use a parameterized query, it also eliminates the need for you to determine whether the query is safe each time to decide how to query the database.

Answer 2

You answer your question here. System.Int32 cannot contain

';DROP DATABASE xxx;-- 

If this is what you worry about. Even passing a negative integer would not adversely affect your database!