Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using Triggers to Mitigate Some SQL Injection Effects


By:   |   Last Updated: 2013-11-19   |   Comments (4)   |   Related Tips: More > 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:

Using the DML Trigger

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

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:

We get confirmation from SQL Server

When we query the table, we see that our row of 50 "c" characters was inserted as expected:

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:

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:

The DDL trigger stopped the DROP TABLE statement

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


Last Updated: 2013-11-19


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, October 01, 2014 - 10:41:29 PM - K. Brian Kelley Back To Top

Hi Michsel,

 

  The problem scenario is one I have gotten questions on more than a few times. If the only thing you can adjust is the DB and it has to be done now, what can you do?

  This is about it. And I note the limits with each proposed workaround. To your point about sensitive data, please note that I said you cannot prevent the read (SELECT). No trigger works there. I gave solutions against writes snd changing structures. Also, not every critical DB has sensitive data. Perhaps it isn't sensitive but important like the content DB for an organization's website. A SQL injection attack might be used to deface the site and make it a watering hole. So what can be done to try and overcome that?

 

We would hope the organization tackles the app. But we know that isn't always the case. So what can be done to reduce risk? Something is better than nothing." Don't let perfect be the enemy of good" in that situation. 

 

 


Wednesday, October 01, 2014 - 5:23:08 PM - Michael K. Campbell Back To Top

Brian, you're damned smart and know a _ton_ about SQL Server Security. 

Which is why it pains me to say that i think this is a TERRIBLE post. 

Very COOL post in terms of the use of triggers. 

BUT, the idea that a) an application is vulnerable to SQL Injection and that b) the fix is to 'mitigate' the problem via the use of triggers really skips over the fact that if the database in question contains sensitive information, then 'mitigation' is a laughable option AT BEST.  

I think you'd be doing everyone a service to throw in a paragraph that outlines just how serious SQL Injection is and that it MIGHT be necessary to pull the plug on some apps IF they're vulnerable and that the solution you've outlined is ONLY viable in situations where there's NO PII or sensitive info involved (which is DAMNED hard/rare just because most apps track, at the very least, email addresses or other sensitive info). 

Not trying to cause problems or grand-stand, but some org that searches 'sql injection' and finds this article might come to the WRONG conclusions about what they need to do to patch some MAJOR security issues with their apps (otherwise). 

Regards, 

--Mike

 


Wednesday, November 20, 2013 - 1:30:36 PM - K. Brian Kelley Back To Top

Look at the case given: "Unfortunately, there's no real ability to modify the application in a timely manner and we can't take it down."

This is true too much of the time. For instance, your organization decides to implement a 3rd party application and then realizes it has major issues with SQL injection. Also, the app requires ownership of the database or membership in db_owner. Yet you can't shut down the app, nor do you have time to make any major modifications. This still happens all too frequently, too. 

So the question is what do you when your hands are tied? These are some preliminary things that can help. 

Are they all you can do? No. Will they stop a focused attacker? No. Then again, time and again we've seen that little will. Should you be worried about an attacker hopping to other servers? Absolutely.

However, those concerns are beyond the scope of the case given here. Keep in mind that these tips are supposed to focus on a specific feature or issue. We could write a book on all the risks and mitigation strategies to dealing with SQL injection. Some have. 


Wednesday, November 20, 2013 - 11:08:37 AM - SQL Injection Back To Top

Why would you be using a DDL trigger to try and prevent something like DROP TABLE, instead of restricting the permissions the WebUser user has so they simply don't have permissions to execute any more DDL than the application requires (hopefully none)?  Further, does this mean you intend to give the sysadmin role to whoever does Web code/schema updates, which seems quite excessive?  I.e. Why did Webuser have rights to DROP TABLE in the first place?

The "one row at a time" DML triggers are entertaining, but unless they fire off some sort of realtime alert, all they do is guide an attacker who has not gained sysadmin/xp_cmdshell permissions to write a loop of some sort if they want to be destructive in this way, or work harder on priviledge escalation attacks.

Also, when looking at SQL Injection, while you covered the first defenses (fix the app with parameterization), you missed another big one: the service account running SQL Server and, if applicable, your SQL server proxy account need to have minimal permissions.  They should not be domain admins, they should not be administrators anywhere (including the local SQL Server box), and so on and so forth.  Mitigate this early!


Learn more about SQL Server tools