How to Detect SQL Injection Attacks Using Extended Events and SQL Monitor

There are several strategies for detecting SQL Injection attacks and other attempts at penetrating a SQL Server database. It has become increasingly common to add a penetration test to the set of tests that are performed on a release candidate of a database application to check that all the obvious attack vectors are well tied-down and to ensure that the database can detect attempts at penetration. This will make sure that your application and database can successfully resist an attack.

It is important, though, to also provide an alert when, despite all precaution and defenses, the database is being attacked. In this article, I'll be showing how to make a start with monitoring a database to alert you to a possible SQL Injection attack or any obvious attempt to gain illicit access to a database server. I'm not suggesting it is a complete system; you'll expand and evolve the solution in the face of changing methods of attack.

How Can You Detect When an Attack Is Taking Place?

You will be surprised how often a public-facing website is attacked. I once worked for a company whose business was to 'incubate' startups, and we regularly launched websites. I installed on them both intrusion-detection systems to detect attempts to gain illegal access to the network and attack-detection systems to detect when servers were being probed for signs of weakness. They were set to play the sound of Vincent Price uttering a demoniacal laugh whenever an attack was attempted. The laugh happened so often that the programmers eventually complained. The websites were attacked routinely with automated tests and sometimes by a live hacker. This happened so frequently and was carefully tested for all the common vulnerabilities. Never again have I taken database security lightly. At that time, the main objective the attackers had was to take control of the server, but nowadays their focus is more on data.

The SQL Server Audit provides a very effective general-purpose audit mechanism and is ideal for tracking the damage a successful penetration has wreaked. This includes recording data manipulation language (DML) and Data Definition Language (DDL) operations. It detects, for example, all password changes, backups and restores, logins, logouts, database operations, permission changes, and ownership changes. This is, of course, essential for a post-mortem examination, but it is less useful for warning you that an attack is taking place.

Attacks are usually messy and potentially visible because they involve trial and error by the hacker in the information-gathering stage. In fact, the error messages are usually the main vector that the attacker uses to get information. Once the attacker has a connection that can be exploited, such as a website HTTP connection, they will need to assess what permissions they have and what data is available. To do more than that, they will need to bypass any interface and even attempt to escalate the privileges of the login to get to any other data. In the early stages of an attack, this is usually done by trial and error, deliberately triggering SQL errors as they attempt to navigate the schema. The hacker relies on the fact that these errors aren't usually detected by monitoring systems, and some of them aren't even logged, so scanning the error logs won't help. However, if your monitoring tool uses extended events, it can detect errors characteristic of a SQL Injection attack, which, otherwise, should be very infrequent in a well-tested production system.

Types of SQL Injection Attack

There are several types of SQL Injections, depending on the method of attack, the information to which the hacker can get access, and on the available 'surface area' of attack, which is increased by insecure use of tools, such as extended stored procedures.

In-Band Injection

This is the classic attack where the attacker can both launch the attack and obtain results through the same communication channel. This is done with two in-band techniques:

• Error-based SQL Injection gets information about the database from the error messages that are displayed
• Union-based SQL Injection relies on the attacker being able to concatenate (UNIONALL) the results of the information being stolen with the legitimate results.

Both techniques rely on the attacker modifying the SQL being sent by the application and on the errors and returned information being displayed in the browser. It succeeds where either the application developer or the database developer fails to properly parameterize the values that they use in their queries. Both are trial and error techniques, and the errors can be detected.

Blind Injection

Blind SQL injection is used where a result or message can't be seen by the attacker. Instead, the technique relies on detecting either a delay or a change in the HTTP response to distinguish between a query resolving to TRUE or FALSE. It's rather like communicating with the spirit world via tapping.

The errors will be like those for in-band injection, but the process is slower with more errors. Blind SQL Injection will produce several syntax errors and object-not-found errors since the only way of telling that something has worked is the length of time between the call being made and the error is returned.

Out of Band Injection

In out-of-band SQL Injection, the attacker uses SQL Server extensions, such as xp_dirtree, xp_cmdshell, sp_makewebtask (now removed), and xp_sendmail to provide 'exfiltration,' and send results to the attacker via HTTP or DNS. Here, the attackers need to find out whether they have permission to use these tools, so there will be errors generated if access to them is denied.

What Sort of Errors Do We Need to Detect?

So, what needs to be monitored in a production system? Everyone has their own ideas, but I'd want to monitor:

• Error 18456 — this is for failed logins, in case someone tries to gain a password by 'brute-force'
• Errors 102 and 105 — I'd want to see all SQL that fails to execute because the syntax is incorrect. These errors are expected on the development and test servers, but should they ever happen in a production database? You'd certainly find a sudden jump in their frequency during SQL Injection.
• Errors 208 and 2812 —  these attempt to access an invalid object or a stored procedure that cannot be found, illustrating the very characteristic of an injection attack.
• Error 245 — this is used by hackers to get values, such as the name of the database.
• Error 205 - This will happen if an attacker's Union-based injection triggers an error when using a UNION ALL SELECT phrase to find out more about the number of columns in a table.
• All errors involving permissions — I want to know about attempts to access objects to which the intruder is denied.

Assuming that application logins are denied access to system stored procedures, I'd also want to know about all errors involving the use of xp_dirtree, xp_cmdshell, sp_makewebtask, and xp_sendmail, which will tell you that a hacker is attempting to extract data into a file for sending. I'd also want errors from using xp_regread, xp_regwrite from attempts to view or write to the registry. It may be worth providing a way of looking at all SQL being executed for the use of these procedures.

Capturing the Errors From Extended Events

The only realistic way of achieving this is to use extended events. The problem with other techniques, which rely on scanning the error log, is that some errors aren't considered to be severe enough to be logged, and so, they can't be the subject of alerts. Unfortunately, these are just the errors that we want to see. Even if they are logged, it wouldn't help much anyway, because scanning error logs are expensive in terms of time and resources, so you wouldn't be able to use it on a production system.

We need to use extended events, specifically the sqlserver.error_reported event. The system_health built-in event session runs continuously and captures errors, but only severity 20 and above. Our needs are very different. We need to create an event session that captures specific, characteristic errors.

We aim to get an occasional figure for the number of these errors that occur on our system from which we can gauge what is normal. You might think that there wouldn't be any of this type of error in a production system. Why would permissions be refused and access denied? Why would you get syntax errors? I checked on a freshly-installed system and found 250 of these sorts of errors in a 24-hour session. They are nothing to do with intrusions, just Microsoft's monitoring systems. If you add to those corporate systems and bought-in applications, then there is a background buzz of errors — the extent of which must be considered before firing an alert.

Nevertheless, once a DBA is alerted to an abnormality, it must be possible to drill into the details to assess whether an attack is taking place. Since we aren't interested in a permanent audit, we can store the event data in a ring buffer target and just query it occasionally, both for full details of all the errors detected and just to get a summary count of the number of errors over a set period for monitoring.

At the simplest, you can run the summary query on the scheduler and then send an alert if anything more than the baseline occurs. However, it is the next stage that is important, when the production staff who receives the alert checks it out. For this, I like to show activity graphically, because it makes it much easier to take in the whole story at a glance. To do this, we'll use the summary query to create a custom metric in SQL Monitor.

Setting Up the Extended Events Event Session

If you were to start from scratch, the simplest way of doing this is to use SSMS, which has both a wizard and a New Session ( a.k.a.Properties) dialog that allows you to set up an extended event session, select, and configure the events that you want to capture and specify the target in which to capture them. Finally, it offers you the code and creates the session.

Once it's created, you can edit the event session in SSMS by accessing its properties. You can also save it as a template for use as the basis for other more complex sessions. Once the event is running, you can set up a browser pane called the 'Live Data Viewer,' to view all the trapped events or view use the View Target Data option to view the target data or its XML (if using a ring buffer).

You create an event session by navigating Management | Extended Events | Sessions, right-clicking, and picking either the 'New Session Wizard' or the New Session dialog. You can view and edit the Properties of an existing event session from the same menu.

The former will offer to create an event session from a ready-made template. It has a range of Profiler-substitutes, such as SP_Counts, Standard, TSQL, TSOL SPs, TSQL_Duration, and so on. There are also templates for query execution as well as system monitoring. Robert Sheldon aced the topic of using Extended events in SSMS in his article Getting Started with Extended Events in SQL Server 2012, and there is nothing more I need to add here.

If you want to start from a template or just create simple event session with one event, then you can use the Wizard to get the basic syntax. Once it was working, make any changes to the live session from the Properties window or just edit the code to add what you want.

We capture the errors_reported event, which collects the error category, destination, error number, whether the error was intercepted in a TRY_CATCH, error message, severity level, state, and whether it is a user-defined error, as well as these specific fields, which are always collected for this event. We can opt to add some of the general global fields ('actions'), of which there are a great number. We'll want to add a few useful fields that give us name of the database under threat, the SQL Text executed, and details of the client application and user that triggered the error.

Listing 1 shows the final code for our MonitorSuspiciousErrors event session with the event, global fields, and target that we want to use.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='MonitorSuspiciousErrors')  
     DROP EVENT SESSION [MonitorSuspiciousErrors] ON SERVER 
  GO
  CREATE EVENT SESSION MonitorSuspiciousErrors
  ON SERVER
    ADD EVENT sqlserver.error_reported --the event we are interested in
      (ACTION --the general global fields ('actions') we want to receive
      (sqlserver.client_app_name, sqlserver.client_connection_id, sqlserver.[database_name],
      sqlserver.nt_username, sqlserver.sql_text, sqlserver.username)
       WHERE  --the filters that we want to use so and to get just the relevant errors
         error_number=(102) OR error_number=(105) OR error_number=(205) OR (error_number=(207) OR
       error_number=(208) OR error_number=(245) OR error_number=(2812) OR error_number=(18456) 
         OR sqlserver.like_i_sql_unicode_string([message],N'%permission%') 
         OR sqlserver.like_i_sql_unicode_string([message],N'%denied%')
       )
     )
    ADD TARGET package0.ring_buffer --define our data storage target
  WITH --all the optional parameters.
    (
    MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = ON
    );
  GO

Listing 1

Once we have created it, we can then start it like this:

ALTER EVENT SESSION MonitorSuspiciousErrors ON SERVER STATE = START;

We can also stop it, if we wish:

ALTER EVENT SESSION MonitorSuspiciousErrors ON SERVER STATE = STOP;

The Monitoring Metric: How Many Errors in the Past 20 Minutes?

To monitor for signs of an attack, we just need to know how many of these errors our MonitorErrors event session collected within a certain period. Here, we count the number of errors in the past twenty minutes. We also need to do it in a single query.

SELECT Count(*) AS ErrorCount
    FROM sys.dm_xe_session_targets AS xet
      INNER JOIN sys.dm_xe_sessions AS xes
        ON xes.address = xet.event_session_address
      CROSS APPLY
      (SELECT Cast(xet.target_data AS XML)) AS target_data_xml(xml)
      CROSS APPLY target_data_xml.xml.nodes('//RingBufferTarget/event') AS xed(event_data)
    WHERE xes.name = 'MonitorSuspiciousErrors'
      AND xet.target_name = 'ring_buffer'
      AND DateDiff
       ( MINUTE,Convert
          (DATETIME2,
            SwitchOffset(
                Convert(DATETIMEOFFSET,xed.event_data.value('(@timestamp)[1]', 'datetime2')
                       ),
             DateName(TzOffset, SysDateTimeOffset())
                        )
             ),
          GetDate()
         ) < 20;

Listing 2

Ideally, of course, on a production system, this will return zero, and you'd raise an eyebrow and want to be alerted if any of these errors were detected at all. However, as I've mentioned before, you can get a background buzz of errors from Microsoft's monitoring systems. If you haven't checked for level 15 and 16 syntax errors during deployment, then you'll very likely get these on a production server. Even worse, if you have a 'wild west' server that has an open-house policy regarding access by your developers and others, then it may well experience regular background failed login attempts, denials of access or errors coming from syntax errors, and unknown referenced objects.

As for any metric, once you've established the 'baseline,' you can set an alert for any deviations. It is easy to run this query on the SQL Agent scheduler and set it to fire an alert when this value is greater than zero or some criterion value. However, by creating it as a custom metric in SQL Monitor, we get to see a graph of the baseline for the metric and to view any alerts in the context of all current activity on the server.

Installing a SQL Monitor Custom Metric

Having created and started the event session, we use it as a custom metric in SQL Monitor. You can install this metric directly into SQL Monitor, and the website also provides a host of other security-related metrics (see the Auditing, Security and GDPR sections, in particular).

Since this is a server-wide metric rather than a database in SQL Monitor, it is only visible in the master database, so we must collect it from there on each monitored SQL Server instance. Figure 1 shows the custom metric being added.

Figure 1

On the next step, we create an alert for this suspicious activity, with a threshold set once you have established the baseline activity. On a production system, you'll ideally set the alert threshold at zero, but here I set it at 30.

Figure 2

Simulating a SQL Injection Attack

To capture some errors, we'll simulate a SQL Injection attack. We could do this using one of the standard penetration testing tools on your web application, such as SQLMap and OWASP Zap. However, here we'll just do it in SSMS.

I set up a very bad FakeCustomer table, which stored passwords, credit card numbers, and the lot. Then, we'll create a terrible stored procedure with a bad vulnerability that is intended for checking the password supplied by the web user. It concatenates user input parameters directly into the string that is to be executed, which will allow us to try out a bit of ' Little Bobby Tables' SQL Injection. If we, as a malicious attacker, can manipulate the input string and get it in the right format, then we can greatly extend the results that the stored procedure returns. The attacker will likely, and deliberately, trigger errors as they attempt to arrive at the right input format.

We will pretend to be an attacker, a user with no explicit permissions at all to any object, and see how easy it is to get a single result that contains all the passwords and user IDs.

IF Object_Id('dbo.FakeCustomer') IS NOT NULL DROP TABLE FakeCustomer;
  CREATE TABLE dbo.FakeCustomer
    (
    Customer_ID INT IDENTITY NOT NULL PRIMARY KEY,
    Firstname VARCHAR(50) NULL,
    Surname VARCHAR(50) NOT NULL,
    Password VARCHAR(50) NULL,
    User_ID VARCHAR(20) NOT NULL,
    CreditCardNo CHAR(16) NULL,
    SortCode VARCHAR(20) NULL,
    AccountNo VARCHAR(20) NULL,
    InsertionDate DATETIME NOT NULL DEFAULT GetDate()
    ) ON [PRIMARY];
  GO
  --pop some spoof data into the table....
  INSERT INTO dbo.FakeCustomer (Firstname, Surname, [User_ID], [Password],
  CreditCardNo, SortCode, AccountNo)
    SELECT f.Firstname, f.Surname, f.[USER_ID], f.[Password], f.Creditcardno,
      f.SortCode, f.AccountNo
      FROM
        (
        VALUES
          ('Joe', 'McTavish', 'Foo', 'plasticShoe', '7666923165777980',
            '23-45-67', '040592739'),
          ('Lars', 'Porsenna', 'Abe', 'ninegods', '5960711184930897', '76-54-23',
            '014354678'),
          ('Abou', 'Ben-Adam', 'Tribe', 'increase', '9807493817364950',
            '08-48-37', '003948673'),
          ('Phil', 'Factor', 'jig', 'flutersball', '7666923165777980',
            '22-45-44', '020594835')
        ) AS f (Firstname, Surname, [USER_ID], [Password], Creditcardno, SortCode,
      AccountNo
  );
  GO
  IF EXISTS (SELECT * FROM sys.schemas AS S WHERE S.name LIKE 'WebSite') SET NOEXEC ON;
  GO
  --if schema already exists don't execute next
  CREATE SCHEMA WebSite;
  GO
  SET NOEXEC OFF;
  IF EXISTS (SELECT * FROM sys.sysusers AS S2 WHERE S2.name LIKE 'WebUser') DROP USER Webuser;
  -- We need to execute some of the following code with the restricted access rights of a
  -- typical web user that has only access rights to the stored procedure that accesses
  -- the table We then run part of the script as that user.
  CREATE USER WebUser WITHOUT LOGIN WITH DEFAULT_SCHEMA = WebSite;
  GO
  --we will prevent our WebUser from direct access to the table
  -- ... allow WebUser to access the stored procedure that accesses the table
  ALTER AUTHORIZATION ON SCHEMA::[WebSite] TO [WebUser]
  /* Now we give the WebUser user account access to a procedure to authenticate website users. This is of course a terrible idea that introduces a vulnerability, so NEVER ever do it this way  */
  GO
  CREATE OR ALTER PROCEDURE WebSite.Validate @ID VARCHAR(20),
    @Password VARCHAR(100)
  WITH EXECUTE AS SELF --to execute as the login who created this procedure
  AS --no it should never be done this way
    BEGIN --health warning!!! This is a demonstration of how not to do it
      EXECUTE ('
                  Select Firstname, Surname from dbo.FakeCustomer 
                  where (( user_id =''' + @ID + ''') 
                  and (password = ''' + @Password + '''))');
    END;--health warning!!! This is a demonstration of how not to do it
  GO
  execute as user = 'WebUser'
  SELECT * FROM fn_my_permissions(NULL, 'DATABASE');  
  EXECUTE sp_help -- I can't see the tables
  SELECT CURRENT_USER
  SELECT * FROM customers --Error msg 208 tells me that there is no such table
  SELECT * FROM fakecustomer --Error msg 229 tells me that this table exists
  --but I can't select it
  --OK. All I have is this function and its parameters. They are supposed to return the 
  --name if the person exists. Now we start fishing for SQL Injection vulnerability.
  EXECUTE website.Validate 'harry','password'
  --which gives me a blank result, no errors.
  EXECUTE validate 'harry', 'password''; select * from CreditCard; --' 
  --error 102. Incorrect syntax near ';'. Could that mean a terminating bracket?
  -- it certainly suggests that the vulnerability exists
  EXECUTE validate 'harry', 'password''); select * from CreditCard; --' 
  --error 102. Incorrect syntax near ';'. Could that mean a second terminating bracket?
  EXECUTE validate 'harry', 'password'')); select * from CreditCard; --' 
  --error 208. Progress! I needed to add that bracket but what are the names of the tables
  --now, OF course,If I'm in SSMS it is all easy now  
  EXECUTE validate 'harry', 'MyPassword'')); Execute sp_help; --' --this will save time!
  -- Ah. I can see the tables now that the creater of 'validate' can access
  -- Fortunately for me that includes FakeCustomer!
  EXECUTE validate 'harry', 'password'')); select * from FakeCustomer; --' 
  -- So no error but I don't see them on the website as only one result
  --I can always see the details of the colunms in SSMS
  EXECUTE validate 'harry', 'MyPassword'')); EXECUTE sp_help fakecustomer; --' 
  -- Ah. this is fine but the chances of me seeing more than errors or a grid as a 
  --website attacker are pretty remote!
  EXECUTE validate 'harry', 'password'')) union all select ''user'',''pw''; --' 
  --Ah I can see User Pw so I've got the dastatypes right. No error!
  EXECUTE validate 'harry', 'password'')) union all select user,pw from FakeCustomer; --' 
  -- It accepted user as a column but Invalid column name 'pw'. Let's try Pword
  EXECUTE validate 'harry', 'password'')) union all select user,Pword from FakeCustomer; --' 
  -- Invalid column name 'Pword'. Let's try Password
  EXECUTE validate 'harry', 'password'')) union all select user_id,Password from FakeCustomer; --' 
  -- OOH! Nice, got a complete list of uaserids and passwords
  --what database is this?
  EXECUTE validate 'harry', 'password'')) AND 1=CONVERT(int,db_name()); --' 
  --Conversion failed when converting the nvarchar value 'MyWebsite' to data type int.
  --So now I know it is 'MyWebsite'
  REVERT

Listing 3

As you work your way through the above SQL Injection attack, you'll generate the errors indicated, and you'll see the count of errors returned by Listing 3.

On a live, monitored SQL server, we can plot the values recorded for our SQL Monitor custom metric into a nice graph, giving us a baseline for the number of errors detected that were characteristic of these intrusion attempts.

Figure3

Checking on the Errors Made by an Attacker

Regularly and certainly when an alert is raised, we'll want to get the 'Detail View' of what is in our ring buffer for the MonitorErrors event session, so we know exactly what errors were captured, what SQL was being executed, what connection was used, and so on. Listing 4 will do the trick.

DECLARE @Target_Data XML =
            (
            SELECT TOP 1 Cast(xet.target_data AS XML) AS targetdata
              FROM sys.dm_xe_session_targets AS xet
                INNER JOIN sys.dm_xe_sessions AS xes
                  ON xes.address = xet.event_session_address
              WHERE xes.name = 'MonitorErrors'
                AND xet.target_name = 'ring_buffer'
            );
  SELECT 
  CONVERT(datetime2,
          SwitchOffset(CONVERT(datetimeoffset,xed.event_data.value('(@timestamp)[1]', 'datetime2')),
      DateName(TzOffset, SYSDATETIMEOFFSET()))) AS datetime_local,
  --xed.event_data.value('(@timestamp)[1]', 'datetime2') AS time_UTC,
  --xed.event_data.value('(@name)[1]', 'varchar(50)') AS event_type,
  --xed.event_data.value('(data[@name="category"]/text)[1]', 'varchar(255)') AS Category,
  xed.event_data.value('(data[@name="error_number"]/value)[1]', 'int') AS [Error_Number],
  xed.event_data.value('(data[@name="severity"]/value)[1]', 'int') AS Severity,
  xed.event_data.value('(data[@name="message"]/value)[1]', 'varchar(255)') AS [Message],
  xed.event_data.value('(action[@name="username"]/value)[1]', 'varchar(255)') AS UserName,
  xed.event_data.value('(action[@name="nt_username"]/value)[1]', 'varchar(255)') AS NT_Username,
  xed.event_data.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS SQL_Text,
  xed.event_data.value('(action[@name="database_name"]/value)[1]', 'varchar(255)') AS [Database_Name],
  --xed.event_data.value('(action[@name="client_connection_id"]/value)[1]', 'varchar(255)') AS client_conn,
  xed.event_data.value('(action[@name="client_app_name"]/value)[1]', 'varchar(255)') AS client_app_name
  FROM @Target_Data.nodes('//RingBufferTarget/event') AS xed (event_data)

Listing 4

This comes up with the whole record of the inevitable errors that our attacker made.

Figure 4

Conclusion

This article has aimed to illustrate how you can use extended events in SQL Server to look for the signs of a SQL Injection attack. You can then use SQL Monitor to give you a visual clue as to an attack, alongside your other routine monitoring tasks. The Extended Events session is looking for errors that really shouldn't be in a database that is in operation, so the information is useful anyway and should be investigated.

In reading this, it will be difficult not to think of other things that should be monitored. I've kept the example to the error event only. Perhaps, it should monitor all attempts to use extended procedures in case they are used for an out-of-band attack and check for the typical 'WHERE 1=1 ' pattern in SQL. Hopefully, you will extend this example and experiment on ways of keeping one step ahead of the hacker.

All databases could really do with a couple of checks at deployment time. One is to check for syntax errors in test-runs of the database, and the other runs penetration tests on the database application to make sure that there are no weaknesses, either in the application or the database, that can be exploited by an attacker.