4 ms·

In a compiled language like C#, "wrapping" SQL is a good idea, at the very least to catch bugs at compile time rather than runtime. Also makes it easier to swit

In a compiled language like C#, "wrapping" SQL is a good idea, at the very least to catch bugs at compile time rather than runtime. Also makes it easier to switch from one DB provider to another if you need to. You are less likely to suffer from SQL injection if you use at least some kind of wrapper, although a minimal wrapper that takes a SQL with placeholders and arguments would do for that. My preferred way to wrap is to use EF with a dash of Linq, which is excellent and yields great readability and flexibility. There is no way I am going to be willingly writing SQL statements in production code (unless I am forced to do so by some legacy problem). Worse case if the SQL is so complex that it is too much for Linq, then write a stored procedure. Back in 2002 I had to write hand crafted SQL before mainstream ORMs and at the time I thought it sucked bad! I was looking for ways to generate the code from schema but didn't have time to go and back-fix all the legacy code ad-hoc SQL statments!

rjbwork 12y ago
I personally prefer SQL + Dapper instead of ORM stuff if it is Linq-level SQL, and sprocs w/ Dapper otherwise.
CWuestefeld 12y ago
You are less likely to suffer from SQL injection if you use at least some kind of wrapper Pretty much all of our coding guidelines on my team are just guidelines. The one absolute law is that all data going to the DB must be paramaterized, nothing goes in as string substitutions. But virtually all of our DB access (like, at least 99%) is by stored proc anyway. We're just barely able to keep up with performance requirements by tuning things just right, and in my experience, EF isn't able to generate queries that are as efficient as we can by hand with some fiddling. I know that sounds like "I write in assembler, 'cause no compiler is as good as me", but that is what the reality seems like.
- collyw 12y ago
  Out of interest, are there more types of SQL injection, or are they all a case of inserting a semicolon, and running a second query afterwards to get the information you want? Surely that is something that could be disabled at the driver level for security? Make sure it only send one statement at a time. Correct me if I have missed something.
  - CWuestefeld 12y ago
    That's what it boils down to, but limiting execution to a single command would only half fix the issue anyway. It would still be possible to put an early termination into a string and comment out everything else in the command, thereby completely changing what it meant. Passing data properly through parameters isn't really very hard, there's no point in looking for incomplete work-arounds.

rjbwork 12y ago

I personally prefer SQL + Dapper instead of ORM stuff if it is Linq-level SQL, and sprocs w/ Dapper otherwise.

CWuestefeld 12y ago

You are less likely to suffer from SQL injection if you use at least some kind of wrapper Pretty much all of our coding guidelines on my team are just guidelines. The one absolute law is that all data going to the DB must be paramaterized, nothing goes in as string substitutions. But virtually all of our DB access (like, at least 99%) is by stored proc anyway. We're just barely able to keep up with performance requirements by tuning things just right, and in my experience, EF isn't able to generate queries that are as efficient as we can by hand with some fiddling. I know that sounds like "I write in assembler, 'cause no compiler is as good as me", but that is what the reality seems like.

collyw 12y ago
Out of interest, are there more types of SQL injection, or are they all a case of inserting a semicolon, and running a second query afterwards to get the information you want? Surely that is something that could be disabled at the driver level for security? Make sure it only send one statement at a time. Correct me if I have missed something.
- CWuestefeld 12y ago
  That's what it boils down to, but limiting execution to a single command would only half fix the issue anyway. It would still be possible to put an early termination into a string and comment out everything else in the command, thereby completely changing what it meant. Passing data properly through parameters isn't really very hard, there's no point in looking for incomplete work-arounds.