By: K. Brian Kelley | Updated: 2013-11-19 | Comments (4) | Related: > SQL Injection
Problem
I support a web application and we've determined is vulnerable to SQL injection. Unfortunately, there's no real ability to modify the application in a timely manner and we can't take it down. Is there anything within SQL Server I can do to help mitigate some of the SQL injection attacks?
Solution
You can use triggers, both DDL and DML triggers, to try and mitigate some of the effects. Keep in mind this won't provide disclosing information (selects) via a SQL injection attack. That's because a DML trigger only fires on an INSERT, UPDATE, or DELETE statement. There are no triggers for a SELECT statement.
Let's set up an example:
-- Setup Script
USE master;
GO
IF DB_ID('WebDB') IS NOT NULL
DROP DATABASE WebDB;
GO
CREATE DATABASE WebDB;
GO
USE WebDB;
GO
SELECT TOP 50 REPLICATE('a', 200) AS 'SomeColumn'
INTO dbo.SomeTable
FROM sys.all_columns AS ac
SELECT SomeColumn
FROM dbo.SomeTable;
GO
CREATE USER WebApp WITHOUT LOGIN;
GO
CREATE ROLE WebAppRole;
GO
EXEC sp_addrolemember @membername = 'WebApp', @rolename = 'WebAppRole';
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON OBJECT::dbo.SomeTable TO WebAppRole;
GO Do note that we're making a couple of assumptions here:
- The application does not require a login with membership in the sysadmin fixed server role. If this is the case, there's just very little you can do.
- We understand the behavior of the application and it's never going to change more than one row of data for the table in question.
That last assumption is true just for this example. If you know there are cases where particular tables require multi-row changes, then you have to try to tailor your triggers to prevent as much as you can. For instance, if you know the application will never issue a command that will delete all the rows from a given table, then you can do a comparison between the number of rows in the deleted resultset and the actual table. If they match, you prevent the DELETE command from completing successfully. With these assumptions, we can successfully craft both a DML trigger and a DDL trigger to try and reduce the impact of SQL injection:
USE WebDB;
GO
-- DDL Trigger
CREATE TRIGGER BlockDML
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
-- This assume the account used by the application
-- is not a member of the sysadmin role (it better
-- not be)
IF NOT(IS_SRVROLEMEMBER('sysadmin') = 1)
BEGIN
ROLLBACK TRAN;
END;
END;
GO
-- DML Trigger on dbo.SomeTable
CREATE TRIGGER SomeTable_All
ON dbo.SomeTable
FOR INSERT, UPDATE, DELETE
AS
BEGIN
-- This assume the account used by the application
-- is not a member of the sysadmin role and that
-- the app should only be modifying one row at a time
IF NOT(IS_SRVROLEMEMBER('sysadmin') = 1)
BEGIN
IF (SELECT COUNT(*) FROM INSERTED) > 1
OR (SELECT COUNT(*) FROM DELETED) > 1
BEGIN
ROLLBACK TRAN;
END;
END;
END;
GO Using the DML Trigger
Now let's make an update to the table as a member of the sysadmin server role. Note that you'll be able to insert more than a single row:
USE WebDB;
GO
-- Execute as sysadmin role
-- This will be successful
INSERT INTO dbo.SomeTable (SomeColumn)
SELECT TOP 20 REPLICATE('b', 50)
FROM sys.all_columns ac;
GO You should see that the command executed successfully:
If you want, you can query the table to verify the rows are all there:
SELECT SomeColumn FROM dbo.SomeTable ORDER BY SomeColumn DESC;
Now try and execute the following commands in turn as the WebApp user. All of these attempt to affect 2 or more rows. All of them will fail because the DML trigger is enforcing the 1 row modification limit.
-- Execute as if it were coming from the web application
-- All of these should fail because > 1 row
-- INSERT
EXECUTE AS USER = 'WebApp';
GO
INSERT INTO dbo.SomeTable
(SomeColumn)
SELECT TOP 2 REPLICATE('c', 50)
FROM sys.all_columns ac
-- Because the transaction is going to end when the INSERT fails,
-- If you want to run something as yourself, highlight and
-- re-execute just the REVERT statement. This is true for all the
-- batch executions where the trigger terminates.
REVERT;
GO
-- UPDATE
EXECUTE AS USER = 'WebApp';
GO
UPDATE dbo.SomeTable
SET SomeColumn = REPLICATE('c', 50);
REVERT;
GO
-- DELETE
EXECUTE AS USER = 'WebApp';
GO
DELETE FROM dbo.SomeTable;
REVERT;
GO Each time you should see something like this:
If we follow the limit, and we execute operations that only affect 1 row at a time, we're okay, as the following will work:
-- Since these are only one row, they will succeed
-- Execute them in the proper order - Insert, then Update, the Delete
-- INSERT
EXECUTE AS USER = 'WebApp';
GO
INSERT INTO dbo.SomeTable
(SomeColumn)
VALUES (REPLICATE('c', 50));
REVERT;
GO And we get confirmation from SQL Server that it does:
When we query the table, we see that our row of 50 "c" characters was inserted as expected:
Therefore, if we can anticipate the behavior of the web application, we can potentially create some DML triggers that will guard against the most malicious of attempts. Here is the rest of the one row operations by WebApp:
-- UPDATE
EXECUTE AS USER = 'WebApp';
GO
UPDATE dbo.SomeTable
SET SomeColumn = REPLICATE('d', 50)
WHERE SomeColumn = REPLICATE('c', 50);
REVERT;
GO
SELECT SomeColumn
FROM dbo.SomeTable
ORDER BY SomeColumn DESC;
GO
-- DELETE
EXECUTE AS USER = 'WebApp';
GO
DELETE FROM dbo.SomeTable
WHERE SomeColumn = REPLICATE('d', 50);
REVERT;
GO
SELECT SomeColumn
FROM dbo.SomeTable
ORDER BY SomeColumn DESC;
GO Using the DDL Trigger
DBAs may need to modify the database from time-to-time. But unless you have an usual application, the application user doesn't need to be able to make changes. That's what the DDL trigger is designed to prevent. It does a check to see if the user is a member of the sysadmin fixed server role. If not, it rolls back the DDL operation.
For instance, let a sysadmin server rolemember drop the table:
USE WebDB; GO -- Prove as a sysadmin the trigger isn't going to block DROP TABLE dbo.SomeTable; GO
And we get that the command completed successfully:
Now let's recreate the table so we can test with WebApp. We'll also give WebApp membership in the db_owner role, the maximum role in the database.
-- Recreate table for test as WebApp;
SELECT TOP 50 REPLICATE('a', 200) AS 'SomeColumn'
INTO dbo.SomeTable
FROM sys.all_columns AS ac
SELECT SomeColumn
FROM dbo.SomeTable;
GO
-- Make WebApp a member of the db_owner role
EXEC sp_addrolemember @membername = 'WebApp', @rolename = 'db_owner';
GO WebApp isn't a member of sysadmin, but it is a member of db_owner. However, this code will still fail:
EXECUTE AS USER = 'WebApp'; GO DROP TABLE dbo.SomeTable; GO REVERT; GO
You should get a result like the following:
That's because the DDL trigger stopped the DROP TABLE statement. In order for an attacker to counteract such a statement, he or she would have to figure out what trigger was firing and then sending in the command to disable or drop it. While it's still doable if the application has membership in db_owner or similarly elevated rights, it's that much harder. And that's all we can expect at the SQL Server level.
Next Steps
- Understand how deleted and inserted work with DML triggers.
- Learn how to use parameterization, especially with sp_executesql, to try and mitigate SQL injection attacks.
- Consider and prepare for recovery from a SQL injection attack.
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2013-11-19
