In this post, I will introduce you to a special T-SQL script that you can use for detecting potential SQL injection attacks in your database, as well as application-level bugs that you didn’t necessarily know you had.
Symptoms of malicious behavior
The story behind this begins with one of our managed customers requesting an alert about SQL injection attacks. The first question that we raised is – how would one identify when a SQL injection attack is taking place?
Well, while researching the issue, I found that such attacks can usually be identified based on the following patterns:
- Brute force attack with multiple and rapid attempts (most of which would be failed attempts)
- Attempts to “piggy-back” on existing queries and try to “break” the command by injecting additional commands for the following purposes:
- Detect the number of columns returned in the query and the data type of each column
- Detect the list of objects (tables) in the database
- Detect the scope of available permissions to the current login
- Steal data from tables in the database
- Perform other operations such as updating tables, elevating permissions, creating additional backdoors, corrupting the database, and more.
An interesting “side effect” of such operations is – errors. Lots and lots of errors are raised inside the database during this brute-force attack. Luckily, this would be a rather specific list of error types:
- Syntax errors
- Object reference errors (non-existing tables or columns)
- Missing permission errors
So, in order to monitor for SQL injection attacks, we would be monitoring for the above list of error types.
Not necessarily malicious
But interestingly enough – we would be getting an added benefit here. Even if there is no SQL injection attack, it’s still possible that such errors would be raised by the application – simply due to bugs.
Furthermore, these errors in the database may be happening without anyone even noticing! How could that be, you ask? Well, it could be due to bad error handling that “swallows” the error entirely, or because the errors are logged but no one is bothering to look at the logs, or maybe because the errors are caught but an undetailed error message is logged/displayed to the user (I can’t even count how many times I encountered “general database error” messages in applications), or because the developers simply decided to mark this as a “known issue” that they didn’t bother to fix and they didn’t think to ask their DBA about it… The reasons are numerous and varying.
Either way, as a DBA you should have the capability to monitor for such errors, swoop in as a savior and fix a dusty bug that either everyone forgot about, or no one even knew about.
Perhaps there are syntax errors due to a typo or buggy generation of dynamic SQL commands.
Perhaps there are reference errors because a developer misspelled a stored procedure, column, or table name. Or simply because a previously-existing object was dropped or renamed.
Perhaps there are permission-denied errors because the application user doesn’t have the necessary permissions to do what they wanted to do.
And in general – the application is not actually doing what it was supposed to be doing. In other words – the application is not reliable.
Hence the name that was given to this script – Application Reliability Monitor.
The scripts
Due to some syntax differences, there are actually two scripts – one for common SQL Servers, and a separate one for Azure SQL Databases.
Full versions of these scripts are available in our Madeira Toolbox:
Behind the scenes
The condition creates an Extended Events session (with a ring-buffer target) called “DBSMART_Monitor_SuspiciousErrors” and checks it for new content within the past 1 hour.
The XE session monitors for application-level errors such as object reference errors, syntax errors, and permission errors.
If you want to retrieve the full list of error messages that would be monitored by this condition, you can run the following query:
SELECT *
FROM sys.messages
WHERE language_id = 1033
AND ((
message_id = (102)
OR message_id = (105)
OR message_id = (205)
OR message_id = (207)
OR message_id = (208)
OR message_id = (245)
OR message_id = (2812)
OR message_id = (15281)
)
OR
(
[severity]>(10)
AND (
[text] LIKE N'%permission%'
OR [text] LIKE N'%denied%'
)
)
)
The XE session is limited to 8 MB of memory and 1000 events maximum.
Whenever the session is filled with more than 100 events, it is dropped and re-created to “flush” its contents.
You can control this behavior using the 3 parameters at the top of the scripts:
DECLARE
@MinutesBack int = 60
,@MinimumEventsForFlush int = 100
,@MinimumEventsForOutput int = 1
The monitoring script ignores the following use cases:
- Errors due to the non-existence of tables __MigrationHistory and EdmMetadata (these are generated by Entity Framework code-first migrations and cannot be fixed as this is simply how Entity Framework works).
- Errors due to the non-existence of the table MSysConf (this is something generated by Microsoft Access, and is not expected to be fixed by Microsoft any time soon).
- Errors coming from known non-critical programs such as:
- SolarWinds SQL Sentry (these usually happen when SQL Sentry attempts to generate an execution plan for an incomplete/faulty command that it captured).
- SSMS (SQL Server Management Studio) to ignore user errors from manually-run code.
- CEIP (SQL Server Customer Experience Improvement Program) which no one really cares about.
- Other known monitoring and utility tools such as check_mssql_health, SQL Server Performance Investigator, Red Gate tools, etc.
What do I do with it?
The scripts above can be used as part of a monitoring tool that periodically runs them in the database.
But what should you do once the monitoring detects something and an alert is triggered?
You can run one of the queries below to retrieve the current contents of the XE session.
Note that the contents may not reflect exactly what happened when the alert was triggered because it may have been flushed since then.
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Retrieve buffer contents
IF OBJECT_ID('tempdb..#events') IS NOT NULL DROP TABLE #events
CREATE TABLE #events (event_xml XML);
INSERT INTO #events
SELECT X.query('.')
FROM (SELECT xdata = CAST(xet.target_data AS xml), CAST(xet.target_data AS xml).value('(RingBufferTarget/@totalEventsProcessed)[1]','int') AS evcount
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe ON xe.address = xet.event_session_address
WHERE xe.name = 'DBSMART_Monitor_SuspiciousErrors'
AND target_name= 'ring_buffer'
) AS a
CROSS APPLY xdata.nodes (N'//event') AS session_events (X)
--WHERE session_events.X.value (N'(@timestamp)[1]' , N'datetime2(7)') > DATEADD(hh,-1,GETUTCDATE())
OPTION (RECOMPILE);
-- Unfurl raw data
SELECT DBName = QUOTENAME([database_name])
, TimestampUTC_From = MIN(event_timestamp_utc)
, TimestampUTC_To = MAX(event_timestamp_utc)
, [error_number]
, [message]
, session_id
, client_app_name
, client_host_name
, client_process_id
, username
, sql_text
, instance_count = COUNT(*)
FROM (
SELECT
[database_name] = session_events.event_xml.value (N'(event/action[@name="database_name"]/value)[1]' , N'NVARCHAR(1000)') ,
event_name = session_events.event_xml.value (N'(event/@name)[1]' , N'NVARCHAR(1000)') ,
event_timestamp_utc = session_events.event_xml.value (N'(event/@timestamp)[1]' , N'datetime2(7)') ,
session_id = session_events.event_xml.value (N'(event/action[@name="session_id"]/value)[1]' , N'NVARCHAR(1000)') ,
[error_number] = session_events.event_xml.value (N'(event/data[@name="error_number"]/value)[1]' , N'NVARCHAR(1000)') ,
[message] = session_events.event_xml.value (N'(event/data[@name="message"]/value)[1]' , N'NVARCHAR(MAX)') ,
client_app_name = session_events.event_xml.value (N'(event/action[@name="client_app_name"]/value)[1]' , N'NVARCHAR(1000)') ,
client_host_name = session_events.event_xml.value (N'(event/action[@name="client_hostname"]/value)[1]' , N'NVARCHAR(1000)') ,
client_process_id = session_events.event_xml.value (N'(event/action[@name="client_pid"]/value)[1]' , N'NVARCHAR(1000)') ,
username = session_events.event_xml.value (N'(event/action[@name="username"]/value)[1]' , N'NVARCHAR(1000)') ,
sql_text = session_events.event_xml.value (N'(event/action[@name="sql_text"]/value)[1]' , N'NVARCHAR(1000)')
FROM #events AS session_events
) AS ev
GROUP BY [database_name], [error_number], [message], session_id, client_app_name, client_host_name, client_process_id, username, sql_text
OPTION (RECOMPILE);
Equivalent Azure SQL DB Query:
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Retrieve buffer contents
IF OBJECT_ID('tempdb..#events') IS NOT NULL DROP TABLE #events
CREATE TABLE #events (event_xml XML);
INSERT INTO #events
SELECT X.query('.')
FROM (SELECT xdata = CAST(xet.target_data AS xml)
FROM sys.dm_xe_database_session_targets AS xet
JOIN sys.dm_xe_database_sessions AS xe ON xe.address = xet.event_session_address
WHERE xe.name = 'DBSMART_Monitor_SuspiciousErrors'
AND target_name= 'ring_buffer'
) AS a
CROSS APPLY xdata.nodes (N'//event') AS session_events (X)
OPTION (RECOMPILE);
-- Unfurl raw data
SELECT DBName = QUOTENAME([database_name])
, TimestampUTC_From = MIN(event_timestamp_utc)
, TimestampUTC_To = MAX(event_timestamp_utc)
, [error_number]
, [message]
, session_id
, client_app_name
, client_host_name
, client_process_id
, username
, sql_text
, instance_count = COUNT(*)
FROM (
SELECT
[database_name] = session_events.event_xml.value (N'(event/action[@name="database_name"]/value)[1]' , N'NVARCHAR(1000)') ,
event_name = session_events.event_xml.value (N'(event/@name)[1]' , N'NVARCHAR(1000)') ,
event_timestamp_utc = session_events.event_xml.value (N'(event/@timestamp)[1]' , N'datetime2(7)') ,
session_id = session_events.event_xml.value (N'(event/action[@name="session_id"]/value)[1]' , N'NVARCHAR(1000)') ,
[error_number] = session_events.event_xml.value (N'(event/data[@name="error_number"]/value)[1]' , N'NVARCHAR(1000)') ,
[message] = session_events.event_xml.value (N'(event/data[@name="message"]/value)[1]' , N'NVARCHAR(MAX)') ,
client_app_name = session_events.event_xml.value (N'(event/action[@name="client_app_name"]/value)[1]' , N'NVARCHAR(1000)') ,
client_host_name = session_events.event_xml.value (N'(event/action[@name="client_hostname"]/value)[1]' , N'NVARCHAR(1000)') ,
client_process_id = session_events.event_xml.value (N'(event/action[@name="client_pid"]/value)[1]' , N'NVARCHAR(1000)') ,
username = session_events.event_xml.value (N'(event/action[@name="username"]/value)[1]' , N'NVARCHAR(1000)') ,
sql_text = session_events.event_xml.value (N'(event/action[@name="sql_text"]/value)[1]' , N'NVARCHAR(1000)')
FROM #events AS session_events
) AS ev
GROUP BY [database_name], [error_number], [message], session_id, client_app_name, client_host_name, client_process_id, username, sql_text
OPTION (RECOMPILE);
The above query will provide useful details about the various application errors that were captured.
These application errors may indicate either of the following:
- The application is attempting to access an object that doesn’t exist.
- The application is attempting to access an object for which it has no permission.
- The application is attempting to run a command with a syntax error (this usually happens with dynamic SQL).
- There is a SQL injection attack attempting to brute-force the database and gather data or metadata using an application vulnerability.
You will have to examine the errors and use your personal discretion to decide which of the above scenarios is relevant.
Using the information that you find, you may find that the application may have a bug that no one was aware of until now.
Or, you may need to alert everyone about a possible SQL injection attack and require them to patch up the vulnerability ASAP… Speaking of which…
SQL Injection who?
SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. Any procedure that constructs [dynamic] SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. Even parameterized data can be manipulated by a skilled and determined attacker.
Source: Microsoft Documentation
Suppose you’re not sure whether your application/website has SQL injection vulnerabilities and you want to find out without waiting for an attack. In that case, you should review this list of Vulnerability Scanning Tools recommended by the OWASP foundation. Let your CTO or PM choose which tool is the best fit for the relevant app, and use it to proactively scan it for SQL injection and other vulnerabilities.
Who knows, maybe it’ll find something even before it’s exploited by an actual hacker 😀.
Conclusion
After implementing this alert as part of our managed DBA service for all of our customers, we started detecting all kinds of errors and bugs that no one would’ve detected otherwise.
Some customers were actually upset because once we detected a bug and reported it to them, they felt nervous that now they have to open some ancient legacy code to fix a bug they didn’t know about for decades 😅.
But most customers were actually very grateful that we help them make their app more stable and reliable 👍.
And of course, we all feel safer now that there’s something monitoring for potential SQL injection attacks 🛡.
Got ideas? Comments? Stories that you want to share? Go ahead and write them below!