Stored Procedures and SQL Injection
By Brian Kelley, 2013/02/18
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.
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.
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;
CREATE USER WebUser WITHOUT LOGIN;
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;
CREATE TABLE Internet.ExampleTable (
SomeID INT IDENTITY(1,1) NOT NULL,
SomeColumn CHAR(5) NOT NULL,
CONSTRAINT PK_ExampleTable PRIMARY KEY (SomeID)
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.