Xdb SQL Injections Attempts Can't Do Any Harm, but They'll Still Fill Up Your Databases With Useless Records

During routine maintenance I saw our xDB was busy, having received a couple hundred thousand new contacts in a short amount of time. Having gone through an exercise of updating the excludedUserAgents configuration, I was curious to see what I might have missed. Looking at the User Agents of the new traffic revealed something alarming, but also presented the opportunity for my next module.


Finding the Issue

If you've read my past post, you can check to see what User Agents should be excluded from the collection process by looking at their entries in the Interactions table. Running this command will show you any bots that are getting recorded: 

SELECT distinct UserAgent FROM [xdb_collection].[Interactions]   

Since I was looking for something causing a lot of traffic, I expected to see a variation of Googlebot, MsnBot, WorldIndexer, etc.  I wasn't expecting SQL injection attempts in the User Agent field like the following:

0kq90lIe') OR 28=(SELECT 28 FROM PG_SLEEP(15))—
CdhdbJRV')); waitfor delay '0:0:15' -


Blocking the Injection Attempts

The first thing to do in a situation like this is to tell your cyber security team and key stakeholders. Thankfully, I have exceptional support from all things IT in this installation, so they configured WAF to block any such attempts. But you might not have this luxury, so you can still be protected with my Exclude Robots by Agent module.

This module will check the User Agent, and block the request from being recorded if there is a match against your preferences. It has a default configuration with keywords such as "select" and "sleep", so it takes action should these be seen. You can enable logging and email notification to match your desired level of being "In the know". In the end however, if you just want to install it without any changes, it will work just fine that way too.

More Than Just Cyber Security

Yes, this module will allow you to block requests with naughty words in the request's User Agent value, but it can be useful for non-malicious maintenance as well. 

The current method of ignoring bots is to enter the User Agent value for each variation, like so:

  UserAgent 1.0
  UserAgent 2.0
  UserAgent 2.0

This can be a bit inefficient because you need to enter the exact match against the bot, so you'll wind up with hundreds of entries:

Chrome/89.0.4389.125 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)
Chrome/89.0.4389.130 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)
Chrome/89.0.4389.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)
Chrome/90.0.4430.84 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)
Chrome/90.0.4430.97 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)

- or -

OmniExplorer_Bot/2.3 (+http://www.omni-explorer.com) WorldIndexer
OmniExplorer_Bot/2.57 (+http://www.omni-explorer.com) WorldIndexer
OmniExplorer_Bot/2.67 (+http://www.omni-explorer.com) WorldIndexer
OmniExplorer_Bot/2.78a (+http://www.omni-explorer.com) WorldIndexer
OmniExplorer_Bot/2.82 (+http://www.omni-explorer.com) WorldIndexer

With my module you won't need these multiple lines anymore. Simply adding Googlebot or WorldIndexer will manage all variations for you.

Coming Up Next

In the next post I'll provide a link to my Exclude Robots by Agent module, which will efficiently observe and ignore any data that's coming into the xDB matching your given keywords. We'll walk through:

  1. Installation and configuration of the module.
  2.  Simulating an attack against your instance. 
  3. Validating the module protected you from this data flood.

See you then!