AFAIK, a parameterized query is commonly used to prevent SQL injection attacks. What if there is no end-user input that can cause SQL injection? In this case, is it safe to un-parameterize a parameterized query to avoid parameter sniffing?

If I need to specify RDBMS, I'm talking about SQL Server and MySQL.

For example, changing the following query:

SELECT * FROM Foo WHERE name = #{name}

into this:

SELECT * FROM Foo WHERE name = 'John' /* The literal value('John') can be changed as the query is generated */
  • What RDBMS? SQL Server has different parameterisation levels that could mean the literal value 'John' gets parameterised by the query optimiser anyway. – HandyD Jan 21 '20 at 4:46
  • @HandyD Let's say SQL Server and MySQL. Sorry. I didn't know it can be RDBMS dependant. I'll update my question. – user2652379 Jan 21 '20 at 4:55
  • @HandyD Wait, does this mean I can get parameter sniffing even if I use literal value for filtering on SQL Server? – user2652379 Jan 21 '20 at 5:06
  • 1
    Yes, if SIMPLE or FORCED parameterisation is enabled on the database (SIMPLE is the default) then the query optimiser will attempt to parameterise literals (like 'John') in the query to improve query plan re-use. You can turn parameterisation OFF, however, there are performance implications to this. Link – HandyD Jan 21 '20 at 5:33
  • 1
    Note that in SIMPLE, not all queries/literals are guaranteed to be parameterised, but with FORCED they will all be. Another link – HandyD Jan 21 '20 at 5:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.