June 5, 2020 0

What Is SQL Injection? 8 Tips on How to Prevent SQL Injection Attacks

in Cyber Security

(3 votes, average: 5.00 out of 5)

Loading...

Akamai’s State of the Internet report that analyzed data between November 2017 and March 2019 indicates that SQL injection now represents nearly two thirds (65.1%) of all web application attacks

One of the most widespread attacks on web servers aims to attack their backend databases to reveal sensitive information such as customer details, company data, etc. Because the stakes are so high, regardless of whether you’re an application owner or a regular user, addressing questions like “what is SQL injection?” or “what are some of the most commonly used SQL injection types?” will hopefully move us forward in the right direction to better understanding SQL injection attacks are, how they work, and how to stop them.

So, in this article, we’ll discuss how to prevent SQL injection attacks once we have a better understanding of what constitutes this high-risk vulnerability.

What Is SQL Injection and How Does It Work?

SQL (structured query language, pronounced “sequel” or “S-Q-L,” depending on the user) injection attacks regularly show up on the OWASP vulnerabilities list that reveals the top 10 web application security risks in the industry. SQLi attacks, as they’re also known, rely on inadequate input validation checks that can be circumvented to execute a crafted query as a SQL command on the site’s backend database.

User-supplied data (a malicious string submitted as a search query) can be passed onto the backend database server. Without proper input validation checks, the query may get executed on the server. Apart from data security issues, attackers can exploit this vulnerability to delete tables and wreak havoc, severely disrupting your entire business operation. From verbose error messages that can inadvertently disclose helpful details to a potential attacker to sensitive data stored on the server getting leaked, SQL injection flaws can lead to undesirable and disastrous outcomes.

Let’s take a moment to understand what a typical SQL query looks like and how the database processes it. This will help you better understand what SQL injection is and how it works. For this example, imagine that there’s an insecure e-commerce application with no defenses in place against SQL injection attacks. When a user attempts to log in, their browser requests the URL below:

https://insecure-app.com/login

The SQL query that runs on the backend database to log in the user after the credentials are submitted is shown below:

SELECT * FROM Users WHERE username=‘admin’ AND password=’adminpass’;

The asterisk symbol (*) indicates that all the columns from the table “Users” will display if the login is successful. But now consider that an attacker tries to manipulate the database to log in as the admin user without knowing the password and enters the username as admin’–:

SELECT * FROM Users WHERE username=‘admin’--’ AND password=’passw0rd’;

Using line comments (–) to trick the database into disregarding the remaining query to avoid filling out mandatory parameters or to dodge syntax errors is a common trick deployed in SQL injection attacks. The problem with the above query is that the database comments out the “AND” statement, which leads to the password parameter being ignored. An input parameter in a SQL statement acts like a placeholder to which data can be passed at runtime.

So, how do we detect this vulnerability in our applications? Besides relying on manual testing, a common testing tool called sqlmap can be used to identify and exploit six types of SQL injection vulnerabilities. Some other tools can also be used for detection, including:

• Leviathan (a mass audit toolkit),
• Whitewidow (an open-source automated SQL vulnerability scanner), and
• Zeus (an open-source reconnaissance and vulnerability assessment tool).

Types of SQL Injection Attacks

When it comes to SQL injection types, SQLi attacks can be broadly categorized into three classes:

In-Band SQLi

One of the most common SQL injection types is in-band SQLi, where the data shows up on the same channel that’s used to inject the malicious code. Error-based and union-based SQL injection attacks are two of the most frequently used in-band SQL injection attack techniques. In error based SQLi attacks, a verbose error (retrieved data) displays right on the web page in response to a malformed or unexpected query (the malicious user input). This is an example of an in-band SQL injection attack.

Union-based SQLi involves using the UNION SQL operator to combine the results of two or more SELECT statements into a single query and can be employed to extract data from multiple tables within the database.

Out-of-Band SQLi

Out-of-Band SQLi is a less common form of SQL injection type. This is where the attacker is unable to extract the data using the same channel via which he launched the attack. The response to the attack gets delivered via other channels, like email, or it relies on the ability of the application’s database server to make DNS or HTTP requests to a server controlled by the attacker.

For example, xp_cmdshell can be utilized by users with DBA permissions to execute commands on a remote database host. Once the query gets executed, attackers can email the results or find a way to transfer the required information to themselves.

Inferential SQLi

In this type of SQL injection, also known as blind SQLi, there’s no real transfer of data. However, attackers can observe the way in which the application responds to payloads, enabling them to gain insights into whether the query is executed or how the database processes the requests. Although reconstructing the logic of the original query is much easier with verbose errors, an attacker who’s able to successfully execute a blind SQL injection attack on the application can reverse engineer the logic to arrive at the original query.

A time-based blind SQLi attack, another frequently used method, involves examining the delay in response to understand if the query gets executed. For example:

https://insecure-app.com/attack.php?id=1’ and sleep(10)--

On sending the above request, there is no delay in page loading. If the comment used is tweaked, as shown below, and there is a delayed response, it denotes that the query was executed. The attacker now knows the right syntax for commenting out parts of a query as used by the application. Once they have this bit of knowledge, they can continue attacking the application using this information in future techniques.

https://insecure-app.com/attack.php?id=1’ and sleep(10)#

So, now that we know what a SQL injection is and are familiar with the different SQL injection types of attacks, it’s time to explore ways to prevent them.

How to Prevent SQL Injection Attacks

Apart from using web application firewalls (like ModSecurity, or NAXSI on NGINX) to filter out malicious or dangerous requests, when we talk about how to prevent SQL injection attacks, the most important precautionary measures possibly are the use of parameterized query and proper input validation checks. However, they aren’t the only preventive controls that you can adopt.

In addition to discussing these tools, let’s also take a look at a few other tips that can help you prevent SQL injection attacks:

1. Don’t Trust Any User-Supplied Input

User input channels are the primary access points that are exploited to execute SQLi attacks, and it’s here that input validation steps in. Strict context-dependent validation checks (which verify that the type, length, format, allowed characters, etc. match expected values) applied early on in the data flow ensure only inputs that meet a specific set of criteria are permitted. Apart from using semantic validation that is context-based, enforcing syntactic validation to confirm the correct syntax is also considered useful for structured data.

Additionally, though not as effective as input validation, consider escaping all user-controllable data based on the supported character escaping scheme.

2. Refrain From Using Dynamic SQL Queries Whenever Possible

Dynamic queries used in an insecure manner lead to SQL injection vulnerabilities since the SQL code and the issuing logic are built while it’s processing the user input. However, when using a parameterized query, the SQL logic is defined before the user input gets passed as parameters. As a result, the user input can’t alter the logic and is strictly passed as a parameter based upon its defined data type.

Another option is using stored procedures that are implemented in a secure manner without any unsafe dynamic SQL generation. Stored procedures are SQL statements with parameters (that are automatically parameterized) and are saved in the database to be called during execution instead of writing it repeatedly. Nevertheless, stored procedures without proper input validation checks and execution rights can increase the risk potential of an application rather than reduce it.

3. Use Accounts With Least Privileges to Restrict Access in Case of a Breach

Limiting access and minimizing privileges for every account can restrict the risks associated with unauthorized access. This can be done by:

• Assessing the level of access rights required by each account, and the specific tables or portions of it, to which it needs access.
• Creating different DB users for different applications.
• Avoiding administrative privileges.
• Creating views to limit access to underlying tables.
• Assigning only read access where feasible.
• Carefully considering the impact before granting create or delete access to any database account.

4. Rely More on Whitelists Since Most Persistent Attackers Can Find a Way Around Blacklists

We’ve discussed input validation earlier, and there are two ways to approach it — by using blacklists or whitelists. Blacklists are deployed to block known malicious characters, whereas with whitelisting the user input is vetted against a list of permitted characters. These two approaches are also implemented to block traffic from suspicious IP addresses or to limit access to the addresses included in a whitelist.

Due to evolving attack techniques that can subvert filters, whitelists are generally considered a more effective approach when it comes to security.

5. Use Appropriate Technology and Frameworks When Developing Your Application

Most development frameworks in use today come equipped with built-in SQL injection protection mechanisms that aren’t incorporated in older development technologies. Using modern frameworks and environments to build your application can safeguard you from such attacks from the get-go.

For example, use the object relational mapping (ORM) framework to reduce risks, or rely on PHP data objects (PDO) instead of MySQLi in PHP. However, not all of these are foolproof, and more often than not, deploying a combination of techniques is appropriate. The Sequelize ORM being vulnerable to SQLi underscores how the code generated by an ORM can sometimes be vulnerable to injection attacks.

6. Conduct Regular Vulnerability Scans and Code Reviews to Detect Potential Second-Order Attacks

Using automated vulnerability scanning tools and manual web application security assessments will thoroughly investigate your application for any SQL injection or other potential weaknesses that may lead to a security incident. When a user input gets injected into the database but doesn’t trigger an immediate response until activated by a future event, it’s called a second-order SQL injection attack.

So, how do you prevent these types of attacks? Secure code reviews can be done to rule out the possibility of second-order SQL injection attacks.

7. Install Updates and Patches Regularly to Your Applications

Running updated software and installing patches regularly to your applications — and even to the underlying systems on which it runs — protects you against known vulnerabilities. A security patch or an update is released when a bug that was found or reported in previous versions is fixed by developers. Unless these patches are applied, the application continues to be at risk of being breached by exploiting security bugs that could have been avoided with a simple update.

8. Organize Security Awareness Workshops to Educate Your Staff

If you’re serious about security, you probably already have security awareness that goes above and beyond conducting annual or biannual workshops as a part of your organization’s culture. Persuading your employees to be cognizant of the fact that cyber attacks are on the rise is crucial. This helps every member of an organization feel responsible for maintaining security on a regular basis.

Awareness workshops are a platform where general security topics or specific questions — like what is SQL injection, and how to prevent SQL injection attacks — can be addressed to keep your staff updated on the kinds of security incidents that are prevalent. However, security requires a transformation in attitude to avoid negligent practices. Until that happens, mandatory training and workshops can only take you so far.

In Conclusion

SQL injection attacks have graced the OWASP top 10 list since it was first published in 2003. It’s been around for a while now and continues to be relevant in part due to misplaced priority when it comes to implementing security controls. This relevancy likely won’t change any time soon since attackers continue to find new ways to evade existing controls.

Hopefully, we’ve been able to address your queries around what is SQL injection and SQL injection types. We’ve also provided you with multiple suggestions for how to prevent SQL injection attacks by using a combination of parameterized queries, whitelists, input validation, and applying restrictions through database controls (like the LIMIT SQL clause to prevent mass disclosure in case of a successful breach). Stay tuned for future posts relating to SQL injection attacks by subscribing to our blog.

• #sql injection