Structured Query Language (SQL) is a language used to query, operate, and administer Relational Database Management Systems (RDMS). Major database systems that use SQL include MySQL, PostgreSQL, SQL Server, DB2, and Oracle. Since database systems are commonly used in modern web applications to store user-supplied data, these systems are fruitful targets for hackers. A SQL injection attack occurs when a hacker subverts the original intent of the web application by submitting his/her own SQL statements to read, update, or alter the database. Depending on how the web application processes the input, a successful SQL injection attack can expose sensitive information, delete important data, bypass authentication, and more; therefore, it is important that web applications be routinely tested for SQL injection vulnerabilities. Injection attacks such as these make it to OWASP list of Top 10 web application vulnerabilities. This post aims to teach testers and developers on how to detect SQL injection vulnerabilities manually and automatically via a step-by-step process.
STEP 1: Learn SQL (Optional)
Cutting to the chase, it helps to know SQL prior to detecting SQL injection vulnerabilities. This is a bitter pill to swallow, but luckily, SQL is not difficult to understand. And fortunately, all a tester needs to know are the basics of making SQL queries. It is quick and easy to set up a testing environment – visit www.mysql.com and download the latest version of MySQL Community Server. It is a free database supported by an active community of opensource developers and enthusiasts. Unless there is a requirement to be a backend web developer for database-driven web applications, there is no need to be an expert at SQL to detect SQL injection vulnerabilities. Simply practice creating and modifying tables using common SQL clauses, conditions, and operators. Another relief is that the use of SQL across database systems is generally consistent; however, there are only small differences in the syntax depending on the database system. Testers can review the following basic SQL concepts provided below:
SQL Statements
A SQL statement is simply a standalone SQL query that expresses some action to be carried out on the database. When writing SQL statements, it is common to write certain clauses in all capital letters (e.g., SELECT, WHERE, INSERT), but this not necessary. The capital letters are only used to help distinguish keywords (Note: make it a habit of doing this writing style). Additionally, some statements require a semi-colon (;) at the end of each SQL statement. MySQL, for example, requires the semi-colon at the end of each SQL statement. SQL Statements can be further divided into two types based on their effects on the database: 1) Data Definition Language, or DDL, changes the database structure. Accordingly, DDL SQL statements are used to manage the database objects, such as tables and columns. In other words, database objects like tables and columns are created, modified, or removed using DDL SQL statements. CREATE, ALTER, and DROP are all examples of DDL keywords. And 2) Data Manipulation Language, or DML, consists of changing the data only. DML is used to manage the data that resides in the tables and columns. The data inside a table is inserted, updated, or deleted using DML SQL statements. INSERT, UPDATE, and DELETE are all examples of DML keywords.
SQL Clauses
The most common clause is probably the SELECT clause as it is the first clause in any SELECT statement. Its main purpose is to retrieve data from the database table and return it in a tabular structure. It defines the columns that should be returned in the final tabular result set. It is always executed after the FROM clause and any optional WHERE, GROUP BY, and HAVING clauses, if present. The FROM clause builds an intermediate tabular result set from which the SELECT clause ultimately selects the data to be returned. However, the GROUP BY clause changes the structure of this intermediate table. On the other hand, the WHERE clause is an optional clause, and it acts as a filter on the rows of the result set produced by the FROM clause. It extracts only those records that fulfill a specified criterion. The WHERE clause mainly depends on a condition that evaluates as either true, false, or unknown. A general statement would be:
SELECT columnName FROM tableName WHERE columnName = someValue;
This is just an introduction to the basics, so it is highly encouraged that testers know a little more about SQL prior to finding and exploiting SQL injection vulnerabilities.
STEP 2: Learn Basic Server-Side Programming (Optional)
Modern web applications are dynamic; therefore, most web pages are generated by a server-side programming language, such as PHP, JSP, ASP.NET, Perl, and Python. A simple example of how PHP fits into the client-server architecture is when a web server receives a request for a web page from a client. The server uses the extension of the requested file to determine which server or program should process the request. If the extension is .php, then the web server calls the PHP interpreter to process the request and the data that is submitted with the request. This PHP page can use the data it gets from the web browser to access the appropriate data from the database. To visualize how this works, imagine a rudimentary login form on a web application that is handled by PHP. This server-side code obtains the username and the password from the POST content and assigns them to two variables $name and $pass. These two variables are used to dynamically construct SQL statements:
$name=$_POST("UserName");
$pass=$_POST("UserPass");
sql=”SELECT * FROM Users WHERE Username=’$name’ AND password=’$pass’”
sql=”SELECT * FROM Users WHERE Username=’admin’ AND password=’root’”
When the server is provided a valid username and password, such as “admin” and “root,” the login is successful, but what happens if a user manipulates the SQL query? Assuming there is no sanitization, if the user inserts ‘ OR 1=1’ as the name and password, it will return all rows from the table users since 1=1 is always true. This means that it will return all the results in the users table:
$name=$_POST(“UserName”);
$pass=$_POST(“UserPass)”);
sql=”SELECT * FROM Users WHERE Username=’$name’ AND password=’$pass’”
sql=”SELECT * FROM Users WHERE Username=’ ‘OR ‘1’=’1’ ’ AND password=’ ‘ OR ‘1’=’1’ ‘”
STEP 3: Spider the Web Application
Spidering is a necessary task in information gathering because it identifies injection points in which the tester can interact with the backend database. It may also reveal unintended information exposure about the backend database management system. Testers can use their own manual spidering methods, or they can rely on automated tools to do the heavy lifting for them. However, when using automated tools, testers should bear in mind that they can be very noisy.
Manual Spidering Methods
The most basic way to spider a web site is by simply browsing through the site and noting potential injection points. But the disadvantage to this approach is that testers may miss injection points or additional pages where the tester can interact with the backend database. For that reason, it is beneficial supplement manual spidering methods with Burp Suite. After setting up a browser configured to use a local proxy, testers can manually browse through the web application. This is accomplished by turning off Burp’s intercept switch under the Proxy tab so that traffic can pass through the proxy. Testers should follow every link, submit on every form, and log in to as many areas of the site as possible. While the tester browses through the web application, the sitemap within Burp automatically populates under the Target tab. The image below depicts Burp Suite spidering the WebGoat web application on localhost.
Sitemaps
Speaking of sitemaps, they are an absurdly easy way of doing basic information gathering on a web site. Sitemaps document different areas of the site, with separate sitemaps for posts, pages, and so on. By appending the sitemap.xml slug to end of the URL, the web application often returns an XML file detailing the site’s structure. Testers must pay particular attention to pages that might have a form for submitting them, such as contact forms, pre-order forms, referrals, etc.
Automatic Spidering Methods
Burp Suite Pro has an amazing crawling mechanism that maps a web site’s structure automatically. Interestingly, Burp’s crawler mimics the way a user browses the web application by simulating user clicks, navigation, and input submissions. This method of automatic crawling reduces the undesirable “looping” observed in other automatic spidering tools.
Burp Suite Community Edition users are not at a total loss though. OWASP ZAP is an open source tool pre-packaged in every Kali Linux installation. It also crawls a web site and automatically detects vulnerabilities. The Spider tab at the bottom of the ZAP windows displays all the links discovered on the web site. Once the spidering is completed, ZAP starts an active scan, which launches a variety of “attacks” against the links listed in the Spider tab. Using ZAP is as simple as providing the URL to ZAP’s GUI.
Brute-Forcing Directories and Forced Browsing
Another tool that they may be beneficial to spidering a web site is wfuzz’s brute forcing capabilities. This tool is designed for brute-forcing web applications, which can be used for finding hidden resources (e.g., servlets, scripts, etc.), brute-forcing GET and POST parameters for injection vulnerabilities, brute forcing form parameters, and general fuzzing. The success of brute-forcing and finding hidden resources where potential injection points may be concealed, however, is dependent on the tester’s wordlist(s). Daniel Miessler’s Git repository, SecLists, is a collection of multiple types of lists used during security assessments. List types include usernames, passwords, URLs, sensitive data patterns, fuzzing payloads, web shells, and many more. Testers can clone this repo and incorporate it with wfuzz using the -w flag. Furthermore, supplying the -hc flag tells wfuzz to ignore 404 (Not Found) status codes.
A final tool that assists with crawling a web application is DirBuster. This tool is multi-threaded java application designed to brute force directories and files names on web application servers. It comes pre-packaged in Kali Linux as both a GUI- and CLI-based program. Once the assessment is finished, testers can inspect enumerated directories for potential injection points.
A note about stealth: Active information gathering tools engage the target, so if stealth is of upmost importance, most of these tools can be configured with spoofing options. However, during a bug bounty engagement, stealth should not be a concern, but it is important to at least spoof the user agent to bypass IPS and WAF systems. When employing stealth, a tester can camouflage tool signatures to avoid detection and triggering an alarm, hide their attacks within legitimate traffic, modify attacks to hide the source and type of traffic, or make the attack invisible using non-standard traffic types or encryption. Stealth scanning techniques typically involve adjusting the source IP stack and tool identification settings, modifying packet parameters, or using proxies with anonymity networks. Commercial tools can sometimes tag their packets with an identifying sequence and, although this can be useful in post-test analysis of a system’s logs, it can also trigger certain IDS/IPS or firewalls
STEP 4: Manual Testing Methods
After a tester locates a potential injection point (e.g., a POST parameter in a form field, a GET parameter in a URL), testers can begin testing for SQL injection vulnerabilities. Manually testing for SQL injection vulnerabilities is beneficial when automated scanners are not allowed during a bug bounty engagement or a web application is too sensitive for automated testing. Furthermore, manual testing methods are required for verifying and reproducing SQL injection vulnerabilities discovered from automated scan results.
Error-based Testing
Testers can test for errors by typing a single quote (‘) into an injection point. If it returns an internal server error (or any other inappropriate result), then the injection point is likely vulnerable to SQL injection because it indicates that the single-quote is not being properly sanitized. If a single quote does not work, the tester can also try a double-quote (“), for example, test and test’. If the result of this submission invokes an error message or any unexpected/extraordinary message, such as a blank load page, or a success message, then the tester can be sure that a SQL injection is possible in that part of the web application.
Boolean-based (Tautology) Testing
This type of testing involves injecting true statements and usually involves manipulating the WHERE clause of a SQL statement. When detecting SQL injection vulnerabilities, a tester must change the act and purpose of an appropriate database query. One of the possible methods to perform this test is by making the query always true and, after that, insert some test code. Changing the database query to always true can be performed by simply submitting SQL conditions, such as ‘x’=’x’, 1=1, or something like ‘ or 1=1;–. Testers should keep in mind that while checking if changing the query to always true can be performed or not, different quotes must be tried for single quotes (‘) and double-quotes (“). Therefore, if the tester submits ‘ or 1=1;–, then it is wise to also try ” or 1=1;–. Common tautology-based statements include:
- test’ OR ‘1’=’1
- ‘ OR 1=1 –+
- ‘ OR 1=1;–
- ‘ OR 1=1;
- ‘1’=’1’
- test’ OR ‘1’=’1’
- ‘test’ or 1=1’;
If the placement of the single-quote is confusing, see Step 1 (This is why it is beneficial to understand SQL prior to testing for SQL injection vulnerabilities).
To demonstrate how this works, think of a web application that displays user information by typing a username into an input field. User-supplied input is sent to the server and gets inserted into a SQL query, which is then processed by a SQL interpreter. The SQL query to retrieve user information from the backend database looks like this:
“SELECT * FROM users WHERE name = ‘” + username + “’”;
The “*” character is a wildcard, which means select from all columns. The variable “username” holds the input from the user and “injects” it into the query. If the input were “Smith,” the query would look like this:
SELECT * FROM users WHERE name = ‘Smith’”;
This translates to “select from all columns from the users table where the name column equals Smith.” If this input is not sanitized, it could retrieve all the data for the user with the name “Smith.” How much data that is depends on how many columns are in the table. But if an attacker supplies an unexpected input like a SQL query, the query itself can be modified to perform other actions on the database, such as test’ OR ‘1’ =’1. Submitting the aforementioned input changes the SQL query to the following:
"SELECT * FROM users WHERE name = 'test' OR '1' = '1'";
If not sanitized, the above query returns all entries from the users table because the WHERE clause was modified to be always true. The same results can be achieved via test’ OR 1 = 1; –. OWASP’s WebGoat (a deliberately insecure web application) demonstrates this testing method perfectly. See below:
By submitting ‘ OR ‘1’ = ‘1, the query is changed to retrieve all users from the users table.
Below is a more practical example of manually testing for SQL injection vulnerabilities in WebGoat. In the following form submission, the query in the code builds a dynamic query by concatenating a number, making it susceptible to numeric SQL injection. Imagine the query looks like: “SELECT * FROM user_data WHERE login_count = ” + Login_Count + ” AND userid = ” + User_ID;
By submitting single quotes, the server returns a helpful error exception message, which indicates that the form is vulnerable to SQL injection. The error message discloses that it is not expecting a string (‘), but likely an integer instead.
The next step in this testing process is to figure out which field (Login_Count or User_Id) is susceptible to SQL injection. By submitting an input “test” in Login_Count field and an input of “1” in User_Id field, the server returns no error this time.
However, after reversing the inputs, the server now invokes an error. It appears that the User_Id field is susceptible to SQL injection because the web application is not sanitizing or validating the string input “test.” In a secure instance, the web applications should only accept integers if it expects an integer and deny anything else.
Since the User_Id field is vulnerable, a tester can use a tautology-based statement to retrieve all the data from the user_data table, as depicted below. Since the WHERE clause was changed to always true, all the columns for the table are disclosed:
STEP 5: Automated Testing
Automated detection of SQL injection vulnerabilities has its advantages and disadvantages. An obvious benefit is that automated scanners save time; however, they are noisy. Additionally, some of these tools, such as sqlmap and Burp Suite, are designed to both detect and exploit SQLi vulnerabilities. Therefore, these automated scanners must be handled with caution on production applications.
sqlmap & Burp Suite
sqlmap is a popular CLI tool for detecting and exploiting SQL injection vulnerabilities. The simplest use of sqlmap uses the -u flag to target the parameters being passed in a specific URL. As sqlmap probes the parameters passed in the URL, it prompts the tester to answer several questions about the direction and scope of the attack, such as:
It looks like the back-end DBS is ‘MySQL.’ Do you want to skip test payloads specific for other DBMSes?
If the identity of the backend is already identified, it is a good idea to indicate a “yes” response to reduce the number of attacks and any possible noise in the final report. Most importantly, there are also risk-level questions, such as:
for the remaining tests, do you want to include all tests for ‘MySQL’ extending provided level (1) and risk (1) values?
Recall that sqlmap both detects and exploits SQL injection vulnerabilities. Unless the tester is testing against a sandboxed instance, completely independent from all production systems, a lower-risk option is preferable. Using the lower risk level ensures that sqlmap tests the form(s) with harmless SQL inputs designed to cause the database to sleep or enumerate hidden information – and not corrupt data or compromise authentication systems. Here is a demonstration of using Burp Suite and sqlmap in SQLi Labs to automatically detect SQL injection vulnerabilities and discover SQL injection payloads. Below is a simple login form with a username and password field:
By entering “test” in each field and intercepting the request in Burp Suite, a tester can note parameters to pass to sqlmap: uname=test&passwd=test&submit=Submit
The next step is to simply run sqlmap using the information above. The full sqlmap command is sqlmap -u http://localhost/sqli-labs-php7-master/Less12/ –data=”uname=test&passwd=test&submit=Submit”. The -u flag passes the URL to be tested while the –data flag passes the parameters to be tested. If the tester knows which parameter to specifically test, then they can pass the -p flag. Keep in mind that if the web application requires authentication, sqlmap must be passed the session information of the currently logged in user using the –cookie flag. The output of this command results in the output shown below:
sqlmap was able to identify several different SQL injection payloads. Importantly, any time an automated tool detects a SQL injection vulnerability, it must be manually reproduced to verify the results. This will ensure that the result is not a false-positive.
After randomly selecting a payload from sqlmap, the request can be sent to Burp Suite’s Repeater tab (Note: Right-Click and select “Send to Repeater”) and the payload is inserted into the POST request to the server. Notice that the payload contains SLEEP(5). Judging that the response under the Response pane took 5 seconds to load the page, it can be concluded that this part of the web application is vulnerable to time-based SQL injection. To assist testers with sqlmap during their bug bounty engagements, below is a cheat sheet that can be used as a reference when using sqlmap to find SQL injection vulnerabilities:
The same results can also be found using solely Burp Suite. When intercepting the request in Burp Suite’s Proxy tab, testers can send it to the Intruder tab (Note: Right-Click and select “Send to Intruder”). Under the Intruder tab, a tester must select the payload positions within the POST request under the Positions subtab. The image below depicts the highlighted parameters to be tested as they are the injection points that interact with the database. Additionally, Burp Intruder supports various attack types – these determine the way in which payloads are assigned to payload positions. The attack types include Sniper, Battering Ram, Pitchfork, and Cluster Bomb. This demonstration uses the Sniper attack type.
Once the payload positions are selected, navigate to the Payloads subtab. Under the “Payload Options” pane, a tester can load a wordlist of common sql injections, such as /usr/share/wordlists/wfuzz/injections/SQL.txt, or any other wordlists tester wishes to use. Pro users can add from a list.
Navigate back to the Positions subtab and click “Start Attack.” This opens a new window that populates with the injection payloads, HTTP status codes, errors, timeouts, and length, as depicted below:
There is a high chance of false positives and even false negatives, so testers should pay particular attention to the “Length” column to figure out which injection payloads appear to be successful. For example, notice at line 20 that the length of the response is larger than the other responses.
When inserting the payload manually into the field, it can be concluded that this part of the web application is vulnerable to error-based SQL injections because the web application responds with a SQL syntax error.
STEP 6: Practice
There are numerous free online sources to practice detecting and exploiting SQL injection attacks. Some noteworthy resources include:
- SQLi Labs: SQLi Labs is a collection of PHP files and a script to populate several MySQL databases. The purpose of these labs is for testers to hone their SQL injection skills both manually and automatically.
- WebGoat: WebGoat is a deliberately insecure application that allows interested developers to test vulnerabilities commonly found in Java-based applications that use common and popular open source components.
- OWASP Juice Shop: OWASP Juice Shop is probably the most modern and sophisticated insecure web application. It can be used in security trainings, awareness demos, CTFs and as a guinea pig for security tools. Juice Shop encompasses vulnerabilities from the entire OWASP Top Ten along with many other security flaws found in real-world applications.
- bWAPP Bee-Box: Bee-box is a custom Linux VM pre-installed with bWAPP. With bee-box, testers can explore all bWAPP vulnerabilities. Bee-box provides testers several ways to hack and deface the bWAPP website. It is even possible to hack the bee-box to get root access.
I have personally downloaded and used all the above resources. There is perhaps no better way to safely practice detecting and exploiting SQL injection vulnerabilities than the resources discussed above.
References:
Borso, S. (2019). The Penetration Tester’s Guide to Web Applications. Artech House
Kim, P. (2015). The Hacker Playbook 2. Secure Planet, LLC
Kim, P. (2018). The Hacker Playbook 3. Secure Planet, LLC
Marshal, J. (2018). Hands-Om Bug Hunting for Penetration Testers. Packt Publishing
Velu, V. K. & Beggs, R. (2019). Mastering Kali Linux for Advanced Penetration Testing. Packt Publishing
Featured image obtained from Clare, H. (2019). IBM and WANdisco team up on relational database tech. Dennis Publishing Limited 2020. Retrieved from: https://www.itpro.co.uk/hybrid-cloud/32693/ibm-and-wandisco-team-up-on-relational-database-tech
The Cybersecurity Man 