SQL Injections

Introduction:

- MySQL is an online database used worldwide around the globe. This can be used for simple tasks such as creating a shopping list to managing different levels of users depending on query handlers. Having a relational database we are able to create "links" between tables by using primary keys and foreign keys. These keys are what is used to make the connections possible.

- Having an unsecured database system can create a realm of vulnerabilities in a company’s security measures. With one successful injection, a user can break in and gain access to so much more behind the initial checkpoint especially if there isn't further validation.

- Remember these types of attacks use SQL statements to gain access, the easiest way to prevent these kinds of attacks are to set up properly sanitized database permissions such as validation and session tokens that expire after a set amount of time.

SQL Injections: The Bigger Picture

What are SQL queries:

SQL is a standardized language used to maintain and manipulate databases to create a custom query specific to what a user needs. These queries are used to execute commands such as data retrieval, data removal and updating what’s currently in the database. Different SQL elements implement these tasks, such as SELECT, WHERE, RETURN, and MATCHING. We can further filter the data with conditional statements or keywords if the results are to broad.

This is what a typical query may look like. In this example we get the items name and description from the table: Item.

                    
                        SELECT ItemName, ItemDescription
                        FROM Item
                        WHERE ItemNumber = ItemNumber

Looks promising right? Well from here, the web application builds a string query that is then sent to the database to retrieve your data.

                    
                        //Converted query for SQL to read
                        sql_query= "
                        SELECT ItemName, ItemDescription
                        FROM Item
                        WHERE ItemNumber = " & Request.QueryString("ItemID")

But you ask yourself how do we know which item we are referring to? Well this is where we can ask the user for input or have pre-made guided solutions to this problem as to not have a user return something they shouldn't have access to. We then make sure to match the item number given to the one already in the database. The final step is taking the provided users input such as itemid=999 to generate the final query.

                    
                        
                        SELECT ItemName, ItemDescription
                        FROM Item
                        //users input
                        WHERE ItemNumber = 999

As you probably guessed already it will return the data on the specific item only in this example and nothing else.

Item #	Name	Description
999	Tomato Sauce	You really need a description on tomato sauce? Really? it's just SAUCE in a CAN.

One thing to note is that we need to make sure each item in a table has a unique identifier so that we can reference it later. This also should not be editable as changing this identifier can cause issues down the line. Instead if we wanted to update or delete an item we can edit the other contents of a specific item in the table.

If you would like to learn more about query types and the many ways SQL can help you, you can check it out Here

Types of SQL Injections:

Typically we can categorize SQL injections into three categories:

In-band SQLi

The perpetrator employs a single communication channel both for initiating their attacks and for collecting the ensuing results. In-band SQL injection, due to its straightforward and effective nature, stands out as one of the prevalent forms of SQL injection attacks. This method can further be categorized into two sub-variations:

Error-based SQLi
Union-based SQLi

Inferential (Blind) SQLi

The malicious actor transmits data payloads to the server and scrutinizes the server's response and behavior to gain insights into its structure. This technique is referred to as blind SQL injection since the information isn't directly relayed from the website database to the attacker. Consequently, the attacker remains unaware of the attack details in an in-band fashion. Blind SQL injections rely on the response and behavioral patterns of the server, making them typically slower to execute but potentially just as harmful. Blind SQL injections can be classified as follows:

Boolean
Time Based

Out-of-band SQLi

This method of attack becomes feasible when specific features are activated on the database server employed by the web application. Out-of-band SQL injection primarily serves as an alternative to the in-band and inferential SQL injection techniques. Out-of-band SQL injection is executed in situations where the attacker cannot employ the same communication channel for both launching the attack and retrieving information. This is especially relevant when a server's performance is inadequate or unstable for conducting these actions simultaneously. These techniques rely on the server's capability to initiate DNS or HTTP requests for transmitting data to the attacker.

Prevention and Mitigation

There exist several effective methods for both preventing SQL injection (SQLI) attacks and safeguarding against them in case of occurrence. The initial step involves input validation, also known as sanitization. This practice entails crafting code capable of recognizing unauthorized user inputs. While input validation is a recommended best practice, it is not always a foolproof solution. In most cases, it's impractical to enumerate all legal and illegal inputs without generating numerous false positives, which can disrupt user experience and an application's functionality.

For this reason, web application firewalls (WAFs) are frequently employed to filter out SQL injection attacks and other online threats. WAFs rely on an extensive and constantly updated list of meticulously designed signatures to surgically identify and block malicious SQL queries. Typically, this list includes signatures for specific attack vectors and is regularly updated to introduce blocking rules for newly discovered vulnerabilities. Modern web application firewalls are often integrated with other security solutions. These integrations provide additional information to enhance their security capabilities. For instance, when a web application firewall encounters a suspicious but not overtly malicious input, it may cross-verify the input with IP data before deciding to block the request. It will only block the input if the IP address itself has a history of bad reputation.

Imperva's cloud-based WAF employs signature recognition, IP reputation, and other security techniques to detect and block SQL injections while minimizing false positives. The WAF's capabilities are further strengthened by IncapRules, a custom security rule engine that allows for fine-tuning default security settings and the creation of specific security policies.

SQL Injection Examples

To carry out SQL injection, an attacker seeks to manipulate a typical SQL query to exploit vulnerabilities in a database where input validation is lacking. Numerous techniques exist to execute this attack vector, and here are a few examples to illustrate how SQL injection functions.

For instance, the mentioned input designed to retrieve information for a particular product can be modified to appear as follows: http://www.estore.com/items/items.asp?itemid=999 or 1=1.

Consequently, the SQL query associated with this input is transformed to:

                    
                        
                        SELECT ItemName, ItemDescription
                        FROM Items
                        //validation of the OR statement returns true if one of the statements is true
                        WHERE ItemNumber = 999 OR 1=1

Given that the condition "1 = 1" is perpetually true, the query retrieves all product names and descriptions from the database, including those that might otherwise be restricted from your access.

Another injection, attackers can exploit improperly filtered characters to manipulate SQL commands, including the use of a semicolon to separate two fields.

For instance, with input like this: http://www.estore.com/items/iteams.asp?itemid=999; DROP TABLE USERS, the resulting SQL query would be:

                    
                        
                        SELECT ItemName, ItemDescription
                        FROM Items
                        //deletes table USERS connected to Items table
                        WHERE ItemNumber = 999; DROP TABLE USERS

The final result, deleting the USERS table completely.

An alternative method for manipulating SQL queries involves using a UNION SELECT statement. This statement merges two distinct SELECT queries to extract data from various database tables.

For our final example, the input http://www.estore.com/items/items.asp?itemid=999 UNION SELECT user-name, password FROM USERS produces the following SQL query:

                    
                        SELECT ItemName, ItemDescription
                        FROM Items
                        WHERE ItemID = '999' UNION SELECT Username, Password FROM Users;

By employing the UNION SELECT statement, this query unites the request for the name and description of item 999 with another request that retrieves names and passwords for all users in the database.

The Challenge

For this challenge you are tasked with infiltrating a database and trying to gain access to the data behind the curtain. Why not try out a few different methods. HINT: Watch the introduction video to get started

Mark module as complete:

Complete