Disable all queries in SQL Server that don't use named parameters?

Question

It seems that one could stop all threat of Sql injection once and for all by simply rejecting all queries that don't use named parameters. Any way to configure Sql server to do that? Or else any way to enforce that at the application level by inspecting each query without writing an entire SQL parser? Thanks.

Answer 1

There are only a couple ways to do this. OMG Ponies has the best answer: don't allow direct sql statements against your database and instead leverage the tools and security sql server can provide.

An alternative way would be to add an additional tier which all queries would have to go through. In short you'd pass all queries (SOA architecture) to a new app which would evaluate the query for passing on to sql server. I've seen exactly one company do this in reaction to sql injection issues their site had.

Of course, this is a horrible way of doing things because SQL injection is only one potential problem.

Beyond SQL Injection, you also have issues of what happens when the site itself is cracked. Once you can write a new page to a web server it becomes trivial to pass any query you want to the associated database server. This would easily bypass any code level thing you could put in place. And it would allow the attacker to just write select * from ... or truncate table ... Heck, an internal person could potentially just directly connect to the sql server using the sites credentials and run any query they wanted.

The point is, if you leverage the security built into sql server to prevent direct table access then you can control through stored procedures the full range of actions availble to anyone attempting to connect to the server.

Answer 2

1. Remove the grants for a role to be able to SELECT/UPDATE/INSERT/DELETE against the table(s) involved
2. Grant EXECUTE on the role for stored procedures/functions/etc
3. Associate the role to database user(s) you want to secure

It won't stop an account that also has the ability to GRANT access, but it will stop the users associated to the role (assuming no other grants on a per user basis) from being able to execute queries outside of the stored procedure/functions/etc that exist.

Answer 3

And how do you want to check for that? Queries sometimes have constant values that would just as easy be added to the query. For instance, I have a database that is prepared to be multi lingual, but not all code is, so my query looks like this:

SELECT NAME FROM SOMETABLE WHERE ID = :ID AND LANGUAGEID = 1

The ID is a parameter, but the language id isn't. Should this query be blocked?

You ask to block queries that don't use named parameters. That can be easily enforced. Just block any query that doesn't specify any parameters. You can do this in your application layer. But it will be hard to block queries like the one above, where one value is a parameter and the other one isn't. You'll need to parse that query to detect it, and it will be hard too.

I don't think sql server has any built in features to do this.