Ways and techniques to get defense from SQL-injections

Question

Hello, i have some WinForms app (Framework to develop some simple apps), written in C#. My framework later would be used to develop win forms applications. Other developers they are beginers often and sometimes do not use Parameters - they write direct SQL in code. So first i need somehow to do protection in my framework base classes in C#. Do solve this, one developer suggested me to using an ORM such as NHibernate, which takes care of this issue for you (and you don't have to write SQL statements yourself most of the time). So I want to ask, is there some general alternatives(other ways and techniques) when i want to get defense from SQL-injections.Some links or examples would be very nice.

Answer 1

I don't see how there is any means to protect any SQL-based library from developer misuse without crippling its functionality (i.e. never giving direct access to the database).

Even with NHibernate or Linq to SQL it's possible to bypass the mapping layers and directly write a SQL statement.

Personally I think your best option would be to write in BIG BOLD TEXT that people who use your library need to PARAMETERIZE THEIR QUERIES. Failing that, you could try to do some kind of clumsy input sanitization, but that is honestly a flimsy second-rate hack.

Parameterized queries have been around for so long now, there's no excuse for anyone writing code that touches any database to not be aware of it or understand how to use it. The only cure for ignorance is education.

Maybe if we knew more about what this library is supposed to do with respect to data access, we could offer more targeted suggestions...

Answer 2

Agree with Aaronaught, a framework will not completely prevent the possibility. I would never substitute stringent validation on the data layer. Also provide an abstraction layer around your data access that you open up as the API rather then allow developers to connect directly to database.

Answer 3

It sounds like you need to train your developers to use parameter binding instead of looking for a technical solution.

One other alternative would be to keep the database layer in a different project and only allow your SQL savy developers to code in it. The GUI can be in a different project. That way the GUI programmers won't mess up your DB.

Answer 4

Security is usually a process, not a product or api. It is also an evolving process, we have to adapt or get hacked.

A heavy handed approach: You can force everyone to write stored procedures,and not allow direct table access from the accounts that are allowed to talk to the database. (GRANT EXECUTE ON etc)

Then you would need to ensure that nobody writes any fancy stored procedures that take a sql query as a parameter and evaluates it dynamically.

This tends to slow down development, and I personally would not use it, but I have consulted at several shops that did.