0

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
nganju
  • 720
  • 6
  • 16

3 Answers3

4
  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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • +1000. without a doubt this is the best way. Why did you mark it as a wiki? – NotMe Feb 05 '11 at 23:47
  • But that will force you to put all queries in stored procedures which might not be desirable at all. You're not blocking parameter-less queries, you're just blocking all queries. :) – GolezTrol Feb 05 '11 at 23:48
  • Stored procedures reduce code readability and introduce code push complexity, etc. There is another SO question that discusses this. It seems trivial for Sql server to detect WHERE clauses without named params and just reject them. It's odd that there's no way to do this. – nganju Feb 06 '11 at 00:02
  • 2
    @nganju: SPs reduce code readability?! I suppose you can't read the contents if you don't have access, but that's for a reason. SPs can be stored as flat files for development viewing -- no one else needs to see it. A database takes queries -- there's enough trouble with syntax checking and valid columns, you want to add user rights into the mix vs constructing the access appropriately? Good luck with that. – OMG Ponies Feb 06 '11 at 00:11
  • 2
    @GolezTrol: Exactly, it's the only way. I see SPs as preferred -- the only reason not to use SPs is to centralize the code base for use on other databases, but that's a fools errand. ORM works, but is known for queries that don't perform as well as actually constructed ones -- which is why ORM supports SP/etc. So you're back to square one -- writing DB specific code. – OMG Ponies Feb 06 '11 at 00:13
  • @Chris Lively: I'm not sure there's a better means, and some let their distaste for SPs and/or lack of DB knowledge demonstrate their sense of entitlement that things should be easier rather than learning to work with the technology. – OMG Ponies Feb 06 '11 at 00:15
  • @OMG Ponies: I will never understand those that think leveraging the features that a database provides is seen as a bad thing. Especially when those features promote security, faster development, easier deployments, easier tweaking, and on and on... Thanks for continuing to try and bring light to the self blinded masses. – NotMe Feb 06 '11 at 00:20
  • 1
    @Chris Lively: Agreed. I'm always struck by those who will learn Javascript/Ruby/Python/etc but shy from SQL :/ This might not be the best that SQL Server provides, but is the most likely to work on any database. – OMG Ponies Feb 06 '11 at 00:27
  • The post that discusses pros/cons of store procedures is at http://stackoverflow.com/questions/15142/what-are-the-pros-and-cons-to-keeping-sql-in-stored-procs-versus-code . Plenty of space there to make your case and continue to insult the "self-blinded masses". Let's try to stay on the topic of THIS question. – nganju Feb 06 '11 at 00:46
  • @nganju: You raised the issue. Frankly, readability is quite the reach to justify not using. Unless you mean something else, but there is a sentiment against working with SQL that does come off as uninformed, and infantile. And remember that votes on SO really only indicate crowd opinion, not hard fact -- you really should become more educated & decide for yourself than trust the highest voted question. – OMG Ponies Feb 06 '11 at 01:55
2

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.

NotMe
  • 87,343
  • 27
  • 171
  • 245
  • Kind of. Only the db access part would have to be a separate application hidden behind a firewall and only accessible through a particular way by a whitelist of machines (the IIS servers). Too much trouble when easier, better, ways exist. – NotMe Feb 06 '11 at 00:36
0

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.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
  • In my scheme you would have to pass the 1 as a parameter. Not a huge price to pay to get rid of SQL injection forever. – nganju Feb 06 '11 at 00:00
  • By using bind variables that shouldn't be a problem. The database should escape those values automatically. – GolezTrol Feb 06 '11 at 00:23
  • That's not true. Either the '--' are inside a string parameter and are treated as text or the parameter isn't a string and the query will just fail when its value is '--'. Under no circumstances should the value of a parameter be treated as a separate statement. – GolezTrol Feb 06 '11 at 00:36
  • Sorry, too many conversations at a time -- forgot it needs the leading comment to end the text field. – OMG Ponies Feb 06 '11 at 01:51