SQL injection attacks are one of the top threats to the security of websites and web applications. In fact, the SQL injection threat has been number one on OWASP’s Top 10 list since it was publicly disclosed more than 10 years ago.
SQL injection attacks have caused historic havoc. They have been used to breach the World Health Organization (WTO) database and steal data from internal staff, and also to attack U.S. government agencies, including the U.S. Army and NASA. SQL injection has also been used to steal the personal data of close to 157,000 TalkTalk customers and this is only the tip of the iceberg.
In the same way that SQL injection attacks are relatively straightforward and anyone with some SQL knowledge or access to tools could launch them, their prevention and protection strategies are also straightforward.
In this post, we’ll go through the process of an SQL injection, the types of SQL injections and why they are exploitable. We’ll also highlight the prevention and protection mechanisms for all types of SQL injections.
The SQL Injection: What Is It and How Does It Work?
An SQL (Structured Query Language) injection is a type of cyberattack that aims to substitute a legitimate website’s input, such as a username, password, or URL, with an SQL statement that injects code. Hackers perform an SQL injection, by entering fake SQL statements into a website input form view bits and pieces of information or alter a database table. In other words, hackers use unauthorized SQL code to “piggyback” on an authorized path (an input form) to reach and interfere with a site’s SQL database.
Where Is the Vulnerability?
A site with an SQL injection vulnerability has forms of entry, such as input application forms or URL strings, that are unable to differentiate between a user’s input and the native commands in the site’s code.Text fields, such as the username or password areas on login pages that allows a user or hacker to interact with the backend SQL database are at risk. 
For example, when a user inputs a username and password, the information is compared with the website’s database using SQL statements. As shown in the screenshot below, a SELECT statement is the one used to select data from a database and return results to a result table. This statement could be exploited to steal data. 
Refer to the screenshot above. When someone types a username in the text input, the SELECT statement will search in the database’s table users by utilizing this input from the user which according to the example code above, relies on a variable (“ + txtUserId;).If there is no protection or way to prevent a user from entering invalid inputs, the user could enter calculated SQL statements. For example, an unknown visitor could enter a “crafted” input in the username field, such as “105 OR 1=1”.
With this crafted input, the SQL statement would change, but still be valid.
Normally, the variable (+ txtUserId, as stated above) would be replaced with a normal UserId, such as “Diego”.However, with this crafted input, something else happens. The invalid input “105 OR 1=1” was designed by the hacker to intentionally create a brand new query. For example, OR 1=1 means “always TRUE”. It makes the database return all row values from the Users table. Although calculated statements like these can be dangerous, there are far sneakier ways hackers can steal, gain control, and even destroy entire databases. 
For example, by entering something like “or 1=1–” in the password field, a new SQL statement is created that could return all password values for a Users table. 
Why Certain Types of SQL Injections Are Exploitable
Although SQL injections are generally easy to execute, hackers tend to be really persistent, especially if they want to get something out of an SQL injection. SQL injections can be classified based on the server’s response and the channel extraction method. Based on the server’s response, an SQL injection can be either performed by error or blind (inferential), and based on the communication channel, an SQL injection can be either in-band or out-of-band.
A. In-band Error-based SQL injection
An in-band SQL injection occurs when a hacker uses the same communication channel to launch the attack and collect results. In the in-band error-based SQL injection, the hacker attempts to find and exploit visible error results given by the database server, to retrieve information, such as table names and contents. This intention behind this type of SQLi attack is used to learn as much as possible about the structure of the database.
An error-based SQL injection is exploitable due to:
- User-supplied data (input) is not filtered by the web application.
- Dynamic SQL queries (non-parameterized calls).
Unsanitized inputs can lead to error-based feedback that provide too many useful clues for the attacker.
B. In-band UNION-based SQL injection
An in-band UNION-based SQL injection is another type of in-band SQL injection in which a hacker uses the same communication channel to attack and gather results. This SQLi is based on the UNION SQL operator, which extends the results provided by the original query. For example, it could combine the results of two (or more) SELECT statements into a single result.
A UNION-based SQLi is exploitable due to:
- User-supplied data (input) is not sanitized by the web application.
- Dynamic SQL queries (non-parameterized calls).
C. Blind Attacks (Inferential SQL injection)
As the name suggests, hackers who perform blind SQL injection attacks have no idea what is on the other end. The application or website is still vulnerable to SQL injection, but the HTTP response does not come with clues about the SQL query or database error. The hacker can launch this type of attack by blindly reconstructing the database structure with the response and behavior of the server. A blind SQL injection can be Boolean or Time-based.
A blind SQLi is exploitable due to:
- Lack of parameterized queries or prepared statements.
- Dynamic SQL queries using their procedures.
D. Out-of-Band (OOB) SQLi
This type of SQLi occurs when an attacker exploits two different channels, one for launching the attack and the other for collecting data. This attack uses an OOB channel, such as DNS or HTTP protocol to exfiltrate data.
An OOB SQLi is exploitable due to:
- Lack of sanitized or validated inputs from web applications.
- Lack of (or poor) network security measures that allow the listening database server to initiate outbound requests (HTTP or DNS) to the public.
- The right permissions to run the necessary function that starts the outbound request.
How to Prevent an SQL Injection
An SQL injection is one of the easiest to perform and most effective forms of cyber attacks. On the other hand, it is also relatively easy to defend against an SQL injection. The best way to prevent an SQL injection attack is to separate data from queries and commands using prepared statements.
How to defend against an SQL injection
- User Prepared Statements
- Validate User Inputs
- Enforce Least Privilege
- Perform Penetration Testing
- Set Up a a WAF (Web Application Firewall)
A. Use Prepared Statements with Parameterized Queries
Prepared statements guarantee that user inputs passed to SQL statements are safe. The idea is that a user’s input cannot directly influence the SQL commands that control and access an SQL database. The database will treat all inputs (including malicious SQL statements) as ordinary inputted data and not as commands. In a prepared statement, the variables in a query are always separated from the rest of the query. In other words, when a developer defines the code for SQL queries (prepared statements) the user input and code are separated.
Other Practices
- Parameterize stored procedures.
- Avoid dynamic SQL or at least parameterize them.
- Prepared statements also help escape to evade user-supplied inputs. Configure user inputs to a specific function to ensure that characters, such as the single quote (‘), are not passed along to an SQL query as instructions.
B. Validate User Inputs
Another way to defend a website’s front line against SQL injections is to validate user inputs. With this method, whatever a user inputs to the web application, there is always a validation (filtering) process allowing/disallowing inputs.To validate user inputs, identify and define the essential SQL statements. Establish an SQL statement whitelist that helps filter out all unvalidated statements and accepts valid inputs. Additionally, user data input can also be defined by context. For example input fields can be filtered for email addresses by allowing only the characters used in the email, such as the @ symbol or phone number inputs, which can be filtered by only allowing numbers.
C. Enforce Least Privilege
As a second line of defense, the level of access and user’s privileges to a database should be limited. It is important to restrict access rights to the specific users, accounts, or computer processes that need to perform a specific activity on the database. For example, if a website or application only needs to use SELECT statements for the database, there should not be any user privileges to use INSERT or DELETE. Enforcing the least privileged account strategy will help reduce the level of damage in case a user account gets compromised. If a hacker steals a user’s account information, they will also have limited access to the database.
D. Perform Penetration Testing
A great way to prevent an SQL injection is to know the website’s flaws and to perform regular assessments of the website’s defenses by auditing suspicious activities, user privileges, and using pen-testing tools.SQL injection hackers and even script kiddies are beginning to use automation for time-consuming and repetitive tasks. Examples are Havij, an automated SQL Injection tool, that helps penetration testers (or bad actors) to find and exploit SQL Injection vulnerabilities. Another is SQLMap, an open-source penetration tool used to automatically scan, detect, and perform SQL injections and take control of databases.
E. Set Up a WAF
Use a Web Application Firewall (WAF) to protect web applications with access to databases. A WAF can filter out malicious traffic data by using a set of security rules to monitor and filter suspicious traffic behavior.CloudBric’s Cloud-Based Smart Web Application Protection (Cloudbric WAF+) is one of the most comprehensive WAF solutions against SQL injections, and other common threats. Cloudbric WAF+ uses a patented logic (including pattern matching, semantics, and heuristic analysis) and a ruleset to automate attack detection and mitigation. 
Final Words
SQL injections pose a great threat to web apps and sites. Hackers can steal, modify, and even destroy entire databases. The best way to prevent an SQL injection is to parameterize statements. This ensures that all parameters (like inputs) that are passed into SQL statements are treated safely. Another way to defend against these threats is to validate or filter all user inputs. To stay ahead of the game, we also recommend enforcing your user privileges and access rights, using an advanced WAF, and always pen-testing the website’s defenses.