Dapper.net ORM best practice - are Stored Procedures necessary to prevent SQL injection / are they faster?
dapper
Question
I am new to Dapper.net & micro ORMs.
It seems they are often used with Stored Procedures. Is it still necessary to use Stored Procedures (as opposed to writing SQL queries directly into your DAL) to prevent SQL Injection attacks.
Also is the performance much better?
What is the best practice for using Dapper & other micro ORMs - to Stored Procedure or not to Stored Procedure?
Accepted Answer
No, stored procedures are not required to prevent injection - you just need to use parameters properly in your direct queries and they are just as safe.
The performance of a SP can be better, but I have often found them to be worse, much worse - which doesn't make any sense, but google 'parameter-sniffing' and you will see there are often times that the query optimizer tries to be too smart with SP's and ends up running the painfully slower - there are work-arounds for this, but more and more I use straight SQL whenever possible, and supplement with a SP only when necessary - which is in few and far between cases.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow