search

petdance / bobby-tables

bobby-tables.com, the site for preventing SQL injections
http://bobby-tables.com/
245 stars 85 forks source link

Improved C# Examples #76

Closed mason-mcglothlin closed 6 years ago

mason-mcglothlin commented 6 years ago

The existing examples for C# were out of date.

First off, the first example was overly verbose. It had a needless declaration of a parameter object. Adding the parameter value can be combined into one statement.

Secondly, the SqlCommand class in C# inherits from DbCommand, which implements IDisposable. The proper way to handle an IDisposable object is to create it in a using statement or to call .Dispose() on it within a finally block. Since those likely to be learning about Bobby Tables are to new the language, it's good to show them the right way so they don't get started on the wrong foot. Improperly handling IDisposable objects can lead to connection exhaustion and hard to trace bugs in software.

And lastly, the second example showed using the AddWithValue method for creating a parameter as a simpler way. As Joel Coehoorn (Microsoft MVP) points out on his blog entry Can we stop using AddWithValue() already?, this is a dangerous method to use.

So, I've modernized the first example to show proper usage of IDisposable objects and eliminated the unnecessary variable to set the value. Since Oracle has two quirks (using a colon instead of ampersand and needing to explicitly enable binding parameters by name) I've shown an Oracle example as well. I considered doing MySQL, which I think can use question marks or ampersands for the parameters, but I was unaware if there were any other quirks with it, since I haven't used that DBMS very much. An Sqlite example might be good too.

Additionally, I added a Dapper example. Dapper is the micro-ORM that powers Stack Overflow. Using ADO.NET is a boring drag. I haven't used any other micro ORM's and I figured I'd leave Entity Framework to someone else.

petdance commented 6 years ago

Excellent, thank you.

petdance commented 6 years ago

Your page is all code, but your comments here in the PR are very illuminating. Could you update the PR to include some of the comments you make, explaining why the code does what it does? That link to "Can we stop using AddWithValue() already?" sounds especially relevant, and mentioning how "Improperly handling IDisposable objects can lead to connection exhaustion and hard to trace bugs in software."

mason-mcglothlin commented 6 years ago

Yes, I'll work on that. I wanted to avoid information overload for new users. But perhaps below the initial examples I'll add some more detailed explanation.

petdance commented 6 years ago

Excellent. Thank you. I think it will help a lot to have links and explanation of the "why".

mason-mcglothlin commented 6 years ago

@petdance Alright, I've added some explanation and fixed a couple of issues with the code (forgot to provide a variable name and used colons instead of ampersands when demonstrating SQL Server with Dapper).

Let me know if I need to add any more clarification.

petdance commented 6 years ago

Thanks very much, Mason. I've deployed this. 916f0ea