Does any database let you disable non-parameterized queries and therefore disable SQL injection?

Question

Is there a database that allows this already?

If not, here is how I think it could be done. I think it would require modifying the database software and the driver or code that is assembling the SQL query. I believe this is sometimes done by the database itself and sometimes by the driver.

A setting like 'Only Safe Parameters' would be enabled for the database user account used by the web server. If this is enabled then only parameters that are marked safe will be allowed. The network protocol of the database must have the ability for the driver to communicate that the parameters in the query were replaced by the driver safely and not by the calling code.

The user account used by an admin to do queries would not have this feature enabled because we don't use parameterized queries when doing administration.

Would this work? Is there something I'm missing?

Answer 1

There's no DBMS that does that.

The main problem is that there is no way for a database server to tell what parts of an SQL query were generated from user input, so no way to disallow queries that were formed with user input. Once your pieces are added into one string, you can't get it apart to work out what was what any more.

However, what a DBMS could perhaps do is observe that the majority of SQL injection flaws are in the form of literal values:

"SELECT * FROM things WHERE id='"+id+"'"

and ban this by disallowing literals (at least strings and ints) from queries. This would be a bit inconvenient in that you would no longer be able to write queries with static literals in:

"SELECT * FROM things WHERE id=? AND active=1", id

instead you would have to use a parameter for everything:

"SELECT * FROM things WHERE id=? AND active=?", id, 1

This would definitely be an interesting feature for a DBMS to introduce as an option. However, it still would not catch user input used for other purposes, for example column names:

"SELECT * FROM things ORDER BY "+sortcolumn

which happens more often than one would like.

The network protocol of the database must have the ability for the driver to communicate that the parameters in the query were replaced by the driver safely and not by the calling code.

Or, better, use a protocol that passes parameters separately to the body of the query. Some DBMSs and data access layers do this, some don't.

Answer 2

Yes, there is something you're missing. Unless you also write the database to not allow anything but parameterized where clause this won't work. How is the database going to know when someone is sending just a query string, or when something is getting paramterized? What if the software just passes the query itself, no parameters. i.e., your code uses:

sqlcommand = "Select * from users where userid = @id"
parameter p = new parameter(someId)
sqlcommand.parameters.add(p)

Driver or whatever can send this to the database as :

Select * from users where userid = 1234

Answer 3

Static code analysis

There is a variant of your idea that is used in practice and works pretty well according to my experience:

Static code analysis tools like Findbugs can identify invocations of database methods (e.g. executeUpdate) with SQL statements that are not compile-time constants.

It is not perfect through: It will miss invocations of database methods via reflection.

Problems with analysis after the fact

SELECT * FROM sossys WHERE type = 2;

Might be a perfectly fine hard coded statement. Okay, one can argue that parameters should always to be used even for non dynamic statements.

Assume the following statement is sent to the database server

SELECT * FROM person, account 
WHERE account.person_id = person.id AND person.id=:1

This statement look good on a quick glance. It was created by a search algorithm which dynamically joins relevant tables. In this case, it was tricked to join the account table, resulting in an exposure of the password hashes.

Because of the nature of SQL there is no way to use static SQL statements for this, unless the number of optional tables is very small. The static code analysis approach will create a warning to trigger manual review.

Summary

I am not aware of a database or driver which prevents dynamically created queries. Your approach will not catch everything, but will likely catch the common mistakes. Until it becomes available, the static code analysis tools are a very good option to go with.

Answer 4

Not by default.

What you can do is disable direct CRUD access to any table by any non-administrative user (such as the logins used by your websites and software), and instead provide access to CRUD operations by giving EXECUTE permissions on specific stored procedures. This is the "poor man's service layer"; you can't do anything except what a stored procedure allows, and those stored procedures should never treat input variables' data as commands in themselves. I've seen this done many times in industry, often eschewing the newer ORM model.

I mentioned "service layer"; that's basically the other way you lock down a database against attack in general, by requiring all data requests to go through a service layer, which implements the actual queries in a manner impervious to injection (such as using an ORM, which executes most statements through sp_executesql, which keeps all passed parameters separate from the actual command). It works in the same basic way as the stored procedures, except you now have an additional line of security; nothing on the client side or in the webserver even needs to know how to log into the database.