Database Security Question

Well, It seems like such a simple solution to the many problems that can arise from inse开发者_开发技巧cure services and applications. But I'm not sure if it's possible, or maybe nobody's thought of this idea yet...

Instead of leaving it up to programmers/developers to ensure that their applications use stored procedures/parameterised queries/escape strings etc to help prevent sql injection/other attacks - why don't the people who make the databases just build these security features into the databases so that when an update or insert query is performed on the database, the database secures/sanitizes the string before it is inserted into the database?

The database would not necessarily know the context of what is going on. What is malicious for one application is not malicious for another. Sometimes the intent IS to

  drop table users--

It is much better to let the database do what it does best, arranging data. And let the developers worry about the security implementations.

The problem is that the database cannot readily tell whether the command it is requested to execute is legitimate or not - it is syntactically valid and there could be a valid reason for the user to request that it be executed.

There are heuristics that the DBMS could apply. For example, if a single request combined both a SELECT operation and a DELETE operation, it might be possible to infer that this is more likely to be illegitimate than legitimate - and the DBMS could reject that combined operation. But it is hard to deal with a query where the WHERE condition has been weakened to the point that it shows more data than it was supposed to. A UNION query can deliberately select from multiple tables. It is not sufficient to show that there is a weak condition and a strong condition OR'd together - that could be legitimate.

Overall, then, the problem is that the DBMS is supposed to be able to execute a vast range of queries - so it is essentially impossible to be sure that any query it is given to execute is, or is not, legitimate.

The proper way to access the database is with stored procedures. If you were using SQL Server and C#/VB.NET you could use LINQ to SQL, which allows you to build the query in the language witch then gets turned into a parameterized SP. Good stuff.