• Redgate Monitor
• Database Security Monitoring

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

Even if all precautions have been taken to prevent SQL Injection attacks, as laid out in the OWASP website, it is still wise to be able to detect if an attempted attack is taking place, and it is essential to know if such an attack is successful.

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 defences, 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 detects 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, that 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.

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 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, and which otherwise should be very infrequent in a well-tested production system.

Types of SQL Injection Attack

There are several types of SQL Injection, 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 error messages that are displayed
• Union-based SQL Injection relies on the attacker being able to concatenate (UNION ALL) 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 being 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 – 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

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 that some errors aren’t considered to be severe enough to be logged, and so can’t be the subject of alerts. Unfortunately, these are just the errors we want to see. Even if they are logged, it wouldn’t help much anyway, because scanning error logs is expensive in terms of time and resources and 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 too, 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 that 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 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 and, once it was working, make any changes to the live session from the Properties window, or just by editing 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 s 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.

Listing 1

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

We can also stop it, if we wish:

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.

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, 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, triggers 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.

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.

Figure 3

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.

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.

Tools in this post

Redgate Monitor

Real-time SQL Server and PostgreSQL performance monitoring, with alerts and diagnostics

Find out more

SQL Toolbelt

The industry-standard tools for SQL Server development and deployment.

Find out more