The fact of the matter is that SQL injection is still a problem at the end of 2012. Web sites are still being hacked by using SQL injection on a regular basis. The usual response from Microsoft.NET developers is that they can parameterize in their code, which solves things. That's very true. That's effective. However, the problem is with a series of unspoken assumptions.

The first unspoken assumption is that only developers who know what they are doing with respect to defensive coding will ever touch the web application in question. The second unspoken assumption is that should another application hit against the same database (or set of databases), you'll have a similar level of competence. A final unspoken assumption is that the application will be in a Microsoft.NET language.

That's an awful lot of assumptions and as a security professional, I am uneasy when I see a list of assumptions like that. Anyone who has been a developer for a reasonable amount of time eventually runs across a case where a previous developer did a shoddy job, either due to being rushed or due to a lack of ability/skill/experience. Since experienced developers have all run across this situation, those assumptions I mentioned earlier mean we can be in real trouble from the SQL Server side. That's why I still like stored procedures for accessing data in SQL Server.

The Disclaimer:

I realize that there are those who are hard core with respect to not using stored procedures. Questions about performance, ease of use, extensibility, platform agnostic coding, etc., should all factor into how you build anything. Looking at any one piece without the rest of the considerations often leads to poor architecture decisions. This isn't some polemic defense for stored procedures. Rather, I'm merely trying to show how stored procedures can be used to make SQL injection attacks more difficult to execute successfully. Whether or not you use stored procedures in your solutions should be weighed with all of the other factors considered.

Information Disclosure in the Database:

Prior to SQL Server 2005, if you queried the sysobjects table in a database, you saw all the objects. Even if you didn't have access to an object, you still saw it. If you're still using SQL Server 2000, this article doesn't really apply to you, and let me strongly encourage you to consider upgrading if you aren't already pushing the subject within your organization.

As of SQL Server 2005, when you query sysobjects or sys.objects or any of the other views that reveal metadata about the database, you only see information on the objects you have permissions to access. Therefore, if you don't have SELECT, INSERT, UPDATE, or DELETE permissions against a table, you can't learn of its existence. If you don't have EXECUTE rights against a stored procedure, you don't know it's there. The reason I'm making this point is that it's nearly impossible to attack something you don't know of within SQL Server. This isn't physical warfare where you can serendiptiously hit an enemy formation with a stray bomb or missile. 

Demonstration:

We can talk about this information disclosure protection or we can see it in action. Let's do the latter. First, we want to create a sample database along with a database only user. This database only user will serve the purpose of being what we test with to demonstrate how SQL Server protects information.

CREATE DATABASE WebTest;
GO 
USE WebTest;
GO 
CREATE USER WebUser WITHOUT LOGIN;
GO 

Now that we have that done, let's create some objects to work with. We'll create a schema, Internet, because later I'm going to give the user EXECUTE rights against that schema. We'll also create a table.

CREATE SCHEMA Internet AUTHORIZATION dbo;
GO 
CREATE TABLE Internet.ExampleTable (
SomeID INT IDENTITY(1,1) NOT NULL,
SomeColumn CHAR(5) NOT NULL,
CONSTRAINT PK_ExampleTable PRIMARY KEY (SomeID)
);
GO

With that done, let's see the difference in what we can see with a super user account and what the limited account WebUser can see.

-- View sys.objects as database owner or dbo
SELECT s.[name] AS 'Schema', o.[name] AS 'Object',
o.type_desc AS 'ObjectType'
FROM sys.objects o
JOIN sys.schemas s 
ON o.schema_id = s.schema_id
WHERE [type] IN ('U', 'P');
GO 
-- View sys.objects as our example user
EXECUTE AS USER = 'WebUser';
GO 
SELECT s.[name] AS 'Schema', o.[name] AS 'Object',
o.type_desc AS 'ObjectType'
FROM sys.objects o
JOIN sys.schemas s 
ON o.schema_id = s.schema_id
WHERE [type] IN ('U', 'P');
GO 
REVERT;
GO 

So all this is well and good, but it's not usable. Let's make it so with a couple of stored procedures that refer to the table. Since the stored procedures and the table have the same owner, they make use of ownership chaining. This is great. We can control access to the table through the stored procedures without revealing the existence of the table to an attacker.

-- Granting access to the table through stored procedures
GRANT EXECUTE ON SCHEMA::Internet TO WebUser;
GO 
CREATE PROC Internet.QueryTable
AS
BEGIN
SET NOCOUNT ON;
SELECT SomeID, SomeColumn 
FROM Internet.ExampleTable;
END;
GO 
CREATE PROC Internet.DeleteRow
@SomeID int
AS
BEGIN
SET NOCOUNT ON;
-- This only allows one row to be deleted at a time.
-- Yes, an attacker could automate, but otherwise it would
-- be tedious to destroy data.
DELETE FROM Internet.ExampleTable
WHERE SomeID = @SomeID;
END;
GO 

Once you've built the stored procedures (don't forget the GRANT EXECUTE against the Internet schema), go back and run the queries against sys.objects. You'll see that as WebUser you now see the stored procedures. However, you still don't see the tables. This is what we want. If you can't see the tables, you can't query their structure. If you can't query their structure, you have to try and determine how to access them based on how the application passes data in and how the stored procedures response. This already is substantially harder for our wannabe attacker. This is a good thing. Perhaps he or she will decide our site isn't worth the trouble and will move on to an easier one.

Don't Be Low Hanging Fruit:

There is something to that idea of being more difficult than our neighbor to crack into. After all, that's why home security companies give you the signs and the stickers that advertise you are using them. A burglar is going to have to weigh whether to go after your house, which is likely protected by a system, versus a house just down the street that shows no signs of such. Since home owners with alarm systems understand this, they tend to advertise that they have a system. This is deterrence. It's also why some security companies will give their stickers and signs to folks that don't have a system. It benefits those folks and serves two purposes: first it advertises the security company and second it builds some good will with a potential future customer.

We want to be like the folks with the advertisment saying, "My house is protected." If an attacker doesn't have a particular reason to go after us specifically, he or she is likely to move on if it looks difficult. There are plenty of suckers with poorly protected data that is worth just as much as ours. An attacker looking to make money is going to maximize his or her time and effort. That will mean moving on from us.

Why You Should Hate db_datareader and db_datawriter:

These two fixed database roles are very popular. They give immediate access to tables and views in a particular database. They are an effective shortcut. I despise them because they give implicit permissions to these tables and views. In other words, if we query sys.database_permissions, we will not see any permissions that lead us to believe that a user has access to the given tables and views. It's only by taking the second and third steps of querying these roles that we figure out such is the case. To see the impact on information disclosure, add WebUser to the db_datareader role and re-run the query to see what that user can see:

-- Add WebUser to db_datareader, then re-run the query 
-- above against sys.objects as WebUser.
EXEC sp_addrolemember @membername = 'WebUser', @rolename = 'db_datareader';
GO 

Obviously, if the user has db_owner rights or some similarly granted permissions, you have the same problem. That's why we insist on the Principle of Least Privilege: only the rights to do the job; no more and no less.

Resetting the Permissions:

Of course, if you want to toggle back and forth to see how the various permissions change what WebUser can see, here are the "undo" scripts:

-- Revoke access and re-run the query to see the differences again
REVOKE EXECUTE ON SCHEMA::Internet TO WebUser;
GO 
EXEC sp_droprolemember @membername = 'WebUser', @rolename = 'db_datareader';
GO

A Limit to What Can Be Done:

Stored procedures also control how much or how little data can be viewed or manipulated. I understand that a competent developer will ensure that the application will do only what it's supposed to do. However, see the assumptions at the start of this article. Those assumptions can easily be wrong, especially over time. All it takes is one misstep and there's a serious problem with the app. Note these lines from the delete stored procedure:

DELETE FROM Internet.ExampleTable
WHERE SomeID = @SomeID;

The SQL statement only allows rows corresponding to the one ID to be deleted. In other words, you're not going to have a mass delete. While an attacker could get smart and automate the delete, he or she'd have to take additional steps to do so. In security we understand it's a matter of when and not if there is a breach. I know that's a pessimistic attitude, but it's a realistic one.

One of the keys to surviving a breach is to make it more and more difficult on an attacker without impinging on our end user. By restricting how data is touched in this manner, we can do exactly that: make it hard on the attacker while being transparent on the end user. Yes, this does mean more work on the developer's part. And this is where you get into those questions of whether or not it's worth the effort. There's no hard and fast rule. Weigh the considerations and then make your choice.