When is it safe to use non-parameterized variables in SQL commands?

Question

I'm writing a select query in a C# method which lets client code provide a row ID and get back an object constructed from the row's data. If the row ID is an integer and I have confirmed it's positive, is there any harm in just passing it through using string.Format? I don't see how any damage could be done if they were only allowed to pass in an int and not a string.

Answer 1

You're answering your own question here. System.Int32 can't contain

';DROP DATABASE xxx;--

If that's what you're worried about. Even passing a negative integer would not negatively impact your database!

Answer 2

You're right, it would be safe to pass an integer that way. However, there is also another side of the story.

While it can be considered as safe to just format an int an create the SQL expression, there is also a performance consideration. When the SQL server sees a query for the first time, it will create and cache the execution plan for that query. The next time the same query is issued, the execution plan will be reused.

If you pass in different strings, it will be seen as separate queries, requiring separate execution plans. If you pass in the same parameterized query each time (with differing parameters), the first execution plan will be re-used by SQL Server.

Even if you don't care about the performance benefit, I would still use a parameterized query for all queries against the database, even for those that can be considered "safe" as you point out - simply to be consistent in the way the application accesses data. If you always use the parameterized query, it also saves you the trouble of determining whether the query is safe each time in order to decide in which way to query the database.