How to Detect SQL Injection Attacks using Extended Events and SQL Monitor?

Phil Factor illustrates how to track errors signaling a potential SQL Injection attack on one of your SQL Server databases using a custom SQL Monitor metric that uses Extended Events diagnostic data.

Post to visitor

This is a Phil Factor guest post. Phil Factor, aka Database Mole, (real name omitted to shield the guilty), has 30 years of experience in database-intensive applications.

He has remained resolutely anonymous throughout his career, despite having once been screamed at by an angry Bill Gates at an exhibition in the early 1980s.

He is a contributor to Simple Talk and SQLServerCentral on a daily basis.

Even though, as set out on the OWASP website, all steps have been taken to avoid SQL Injection attacks, it is always prudent to be able to predict when an attempted attack is taking place, and it is vital to know whether such an attack is successful.

There are numerous techniques and other efforts to infiltrate a SQL Server database to detect SQL Injection attacks. It has become increasingly popular to add a penetration test to the list of checks conducted on a database program release candidate, to verify that all the apparent attack vectors are well locked down, and to ensure that penetration attempts can be identified by the database. This would ensure sure an intrusion can be successfully resisted by the program and database.

It is necessary, however, to also provide a warning when the database is being targeted, notwithstanding all safeguards and protections. I’ll teach you how to start tracking a database in this article to alert you to a potential SQL Injection attack or some clear effort to obtain unauthorized access to a database server. I’m not saying it’s a complete system; in the face of evolving attack tactics, you’ll extend and evolve the solution.

How do you detect when an attack is occurring?

How much a public-facing website is targeted can confuse you. I once worked with an organization whose job was ‘incubating’ startups, and we launched websites on a daily basis. In order to detect attempts to obtain unauthorized entry to the network and attack warning systems to detect when servers were being searched for signs of vulnerability, I built all intrusion-detection systems on them. They were set to play the sound of a demonic laugh uttered by Vincent Price if an assault was attempted. The laughing erupted so often that the programmers protested inevitably. With automatic checks, the websites were regularly targeted, and often by a live intruder, who carefully tested for all the usual vulnerabilities. I’ve never taken database protection lightly ever before. The main target the attackers had at that time was to take possession of the server, but nowadays their attention is more on data.

SQL Server Audit offers a very robust audit mechanism for general purposes and is suitable for monitoring the harm a good intrusion has caused. This entails tracking operations with Data Processing Language (DML) and Data Description Language (DDL). For starters, it detects any changes to passwords, backups and restores, logins, logins, database operations, changes to permissions and changes to ownership. For a post-mortem inspection, this is, of course, necessary, but it is less helpful to alert you that an assault is taking place.

Attacks are typically chaotic and potentially obvious because in the information-gathering stage they require trial and mistake by the hacker. In reality, error messages are typically the primary vector used by the attacker to get data. Once the attacker has a contact that can be abused, such as an HTTP website connection, they may need to decide what rights they have and what information is available. To do more than that, to get to any other data, they would need to circumvent any gui, and even attempt to escalate the login privileges. This is commonly achieved by trial and error in the early stages of an attack, intentionally causing SQL errors as they try to access the schema. The hacker depends on the fact that detection systems do not normally notice these errors, and some of them are not even logged, so it won’t help to search the error logs. If the control tool uses extended events, however, it can detect errors that are typical of a SQL Injection attack and that would otherwise be very uncommon in a well-tested manufacturing environment.

SQL Injection Attack Types

Depending on the method of attack, the data to which the hacker can access, and the available ‘surface area’ of attack, which is enhanced by unsafe use of tools such as extended stored procedures, there are several types of SQL Injection.

Injection with in-Band

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

Error-based SQL Injection retrieves database information from error messages which are displayed.
Union-based SQL Injection is based on the ability of the attacker to concatenate (UNION ALL) the results of the stolen information, with the legitimate results.

Both techniques rely on the attacker modifying the SQL sent by the application and the errors displayed in the browser and the information returned. It succeeds where the values they use in their queries are not properly parameterized by either the application developer or the database developer. Both are methods of trial and error, and it is possible to identify the errors.

Injection Blind

Blind SQL injection is used where the attacker can’t see a response or post. Instead, to differentiate between a question resolving TRUE or FALSE, the technique relies on detecting either a pause or a change in the HTTP response. It’s quite like interacting through tapping with the spirit world.

The errors are going to be like those for in-band injection, except with more errors, the operation is slower. Several syntax errors and object-not-found errors can be caused by Blind SQL Injection, because the only way to say if anything has succeeded is the length of time between making the call and returning the error.

Injection from Out of Band

The attacker uses SQL Server extensions such as xp dirtree, xp cmdshell, sp makewebtask (now removed) and xp sendmail for out-of-band SQL Injection to provide ‘exfiltration’ and send results via HTTP or DNS to the attacker. Here, attackers need to figure out if they have authorization to use these resources, because if access to them is refused, errors will be created.

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 – 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 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 – attempts to access an invalid object, or a stored procedure that cannot be found. Very characteristic of an injection attack.
  • Error 245 – used by hackers to get values such as the name of the database.
  • Error 205 – which 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

Using extended experiences is the most practical means of doing this. The issue with other approaches that focus on searching the error log is that certain errors are not deemed to be serious enough to be logged, so warnings will not be the focus. These are, sadly, exactly the failures we want to see. It wouldn’t benefit much either, even though they are logged, because scanning error logs is costly in terms of time and money and so you wouldn’t be able to do it on a production device.

Extended events, precisely the sqlserver.error reported event, must be used. The system-health built-in event session continues to run and also captures faults, but just 20 and over gravity. Our desires are rather distinct. We need to construct an event session that catches errors that are unique and distinctive.

For the amount of these errors that exist in our system, we try to get an occasional statistic from which we can calculate what is natural. You would imagine that in a manufacturing environment there wouldn’t be any of this sort of mistake. Why will allowances be declined and access denied? Why can you have errors in syntax? On a newly installed device, I tested and found 250 of these kinds of mistakes in a 24-hour session. They have nothing to do with intrusions, only the management mechanisms of Microsoft. When you add enterprise systems and bought-in software to it, so there is a background buzz of failures, the magnitude of which must be taken into consideration before the alarm is fired.

Nevertheless, it must be possible to dig into the specifics after a DBA is alerted to an abnormality to determine if an assault is taking place. Since we are not involved in a permanent audit, we will store the case data in a ring buffer target and then periodically query it, both for full information of all the observed errors, and also to get a summary count of the number of errors for tracking over a fixed time.

You should run the overview question on the scheduler at its simplest, and only give a warning if anything more than the baseline happens. However, when the production staff who gets the alarm checks it out, it is the next step that is relevant. I like to illustrate activity graphically with this, so it makes it much easier to take a look at the entire story. To do this, we can build a custom metric in the SQL Monitor using the overview query.

Setup the Case Session for Extended Activities

The best way to do this if you were to start from scratch 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, pick and customize the events that you would like to record, and determine the destination to capture them. Finally, it contains the code for you and generates the session.

Once it’s developed, by accessing its resources, you can edit the SSMS event session. As the foundation for other more complicated sessions, you can also save it as a guide for use. Once the event is running, you can set up a ‘Live Data Monitor’ browser panel to display all of the stuck events, or use the Target Data View option to view the target data, or the XML Data Viewer option (if using a ring buffer).

By browsing Administration | Expanded Events | Sessions, you create an event session by right-clicking and selecting either the ‘Current Session Wizard’ or the New Session dialog. You can access and edit the Existing Event Session Assets from the same menu.

From a ready-made prototype, the former will offer to build an event session. It has a variety of replacements for Profiler, such as SP Counts, Normal, TSQL, TSOL SPs, TSQL Duration, etc. As well as device monitoring, there are also models for query execution. In his article Getting Started with Extended Events in SQL Server 2012, Robert Sheldon covered the issue of using Extended Events in SSMS, and there is nothing else that I need to say here.

If you want to start with a blueprint, or just create a simple event session with a single event, you can use the Wizard to get the basic syntax and make some adjustments to a live session from the Properties window after it has worked, or simply modify the code to add what you want.

The error-reported occurrence that gathers the category of error, target, error number, whether the error was intercepted in a TRY CATCH, error message, severity rating, state, and whether it is a user-defined error is collected. We may choose to include any of the general global fields (‘actions’), of which there are a significant number, as well as these unique fields, which are often gathered for this case. We want to include a few helpful fields that give us the name of the threatened database, the SQL text that was executed, and the client program and user information that caused the error.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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:

1
ALTER EVENT SESSION MonitorSuspiciousErrors ON SERVER STATE = START;

We can also stop it, if we wish:

1
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 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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 = ‘MonitorSuspiciousErrors’
                AND xet.target_name = ‘ring_buffer’
            );
  SELECT  Count(*) AS ErrorCount
  FROM @Target_Data.nodes(‘//RingBufferTarget/event’) AS xed (event_data)
  WHERE DateDiff
       ( MINUTE,Convert
          (DATETIME2,
            SwitchOffset(
                Convert(DATETIMEOFFSET,xed.event_data.value(‘(@timestamp)[1]’, ‘datetime2’)
                       ),
             DateName(TzOffset, SysDateTimeOffset())
                        )
             ),
          GetDate()
         ) <20;

 

Ideally, of course, this would yield zero on a manufacturing device, and if any of these failures have been found at all, you will lift an eyebrow and wish to be notified. However, as I’ve described before, you can get a background buzz of errors from Microsoft’s monitoring systems. If, during implementation, you haven’t tested for syntax errors at levels 15 and 16, it is very possible that you will have them on a production server. Much worst, if you have a ‘wild west’ portal that has an open-house strategy to connect the developers and others, frequent history unsuccessful authentication attempts, access denials or failures that could well be encountered as a result of syntax errors and unexplained referenced artifacts.

As for any measure, once you have defined the ‘baseline’, you can set a warning for any deviations. You can conveniently execute this query on the SQL Agent scheduler and configure it to fire an alarm if the value is higher than zero or if a certain criterion value is set. However, by building it as a custom metric in the SQL Monitor, we can see a baseline graph for the metric and display any warnings in the background of all the latest operation on the server.

Installing a SQL Monitor Custom Metric

After building and beginning the event session, we use it as a custom metric in the SQL Monitor. You can load this metric directly on the SQL Dashboard and you can also use a number of other security-related metrics on the website (see the Auditing, Security and GDPR sections, in particular).

As this is a system-wide metric rather than a table, it is only available in SQL Monitor’s master database, so we need to collect it from there on any SQL Server instance monitored. The addition of the custom metric is seen in figure 1.

At the next point, we produce an alarm for this unusual behaviour, with a threshold set once the baseline activity is identified. Ideally, on a production device, you’ll set a warning threshold at zero, but here I set it at 30.

Simulating an assault with SQL Injection

In order to catch any errors, we can simulate the SQL Injection attack. We will do this by using one of the standard penetration test tools, such as SQLMap and OWASP Zap, on your web application. We’re all going to do it here, in SSMS, though.

I set up a really bad FakeCustomer table that stored passwords, credit card numbers, a lot of them. Then we can create an awful stored system with a bad vulnerability, which is supposed to validate the password provided by the web user. User input parameters are automatically concatenated into the string to be executed, allowing us to try a little ‘Little Bobby Tables’ SQL Injection. As a malicious attacker, if we can modify the input string to get it into the right format, we can massively extend the results returned by the stored process. The attacker will certainly, and purposely, create errors as they attempt to arrive at the right input format.

We’re going to pretend to be an attacker, to see how easy it is to get a single result containing all passwords and user IDs, an individual with no special permissions for any item at all.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
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

You will create the indicated errors as you work your way through the above SQL Injection attack, and you will see the count of errors returned by Listing 3.

We can plot the values reported for our SQL Monitor custom metric on a live, tracked SQL server into a pleasant graph, giving us a benchmark for the amount of observed errors that were characteristic of these attempts at intrusion.

Checking on the errors made by an attacker

We would like to get the ‘Summary View’ of what is in our ring buffer for the MonitorErrors event session periodically, and definitely when an alarm is raised, because we know precisely what errors were captured, what SQL was being run, what link was used, and so on. Listing 4 is going to do the trick.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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 = ‘MonitorSuspiciousErrors’
                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)

Conclusion

The purpose of this article is to explain how extended events from SQL Server can be used to search for signs of a SQL injection attack. You can then use SQL Monitor to send you a visual hint of an attack, along with your other regular tracking functions. The extended Events session is looking for errors that should not actually be in a report that is in operation, but the information is still useful and should be examined.

It would be difficult not to worry about other items that could be tracked after reading this. I’ve just kept this example in case of an error. Perhaps it will detect any efforts to use extended procedures and look for the standard ‘WHERE 1=1’ pattern in SQL in the event that they are used for an out-of-band attack. Hopefully, this example will be expanded and you will experiment with ways of keeping the hacker one step ahead.

Both databases really could do with a few reviews at deployment time. One is to search in the database test runs for syntax errors, and the other runs penetration checks on the database program to ensure that there are no bugs that can be abused either in the application or the database by an attacker.