What is SQL Injection (SQLi)

SQL injection or SQLi is a form of attack that manipulates databases using malicious SQL code. In other words, these statements exploit databases to access critical company data, private customer data, user lists and other critical information that should not be displayed.

More specifically, these statements can bypass authentication and authorization to access information on the entire SQL database of a web page or web application.

The SQL injection can significantly impact a business. From unauthorized viewing of user lists to modification and even deletion of records in the databases to the attacker gaining administrative rights of a database to other actions with far-reaching effects on a business.

Websites or web applications using an SQL database such as MySQL, SQL Server, etc., are prone to an SQL injection. SQL injection attack is among the most common threats to web application security. Loss of customer information and personal details such as phone numbers, credit card details, and addresses due to a SQLi attack can lead to loss of customer trust.

What are SQL Queries?

The SQL query is among the standard languages used in running databases for applications. It is used to access and manipulate databases to create customizable data views for each user. As such, SQL queries help execute commands, including data retrieval, record removal, and updates. SQL queries help retrieve data, perform calculations, automate tasks and summarize data, among other things.

Types of SQL Injections

There are three main categories of SQL injection: In-band SQLi, Out-of-band SQLi and Inferential SQLi.

In-band SQLi 

This is a common type of SQLi attack. Also known as classic SQLi, this technique relies on a single channel of communication to launch attacks and get results. Due to In-band SQLi’s simplicity, exploiting it is easy. In-band SQLi is further categorized into two: 

Error-based SQL Injection

As the name suggests, error-based SQL injection relies on error messages from the database server to gather information about database structure. When an attacker sends malicious queries to the database, it results in errors. If the errors are not genetic enough, they can give attackers useful tips to further their agenda.

Of course, errors play a crucial role during the development stage of a web application. However, you should log them to a file with restricted access and disable them on a live site to ensure attackers do not use them to emulate the entire database.

Union-based SQLi

This in-band SQL injection technique uses the UNION SQL operator (which combines multiple select statements from the database) to get a single HTTP response. Attackers can use the data in this report to lodge an attack.

Inferential or Blind SQLi

Compared to in-band SQLi, inferential SQL injection is not easy to exploit. However, that does not make it less dangerous than other types of SQL Injections.

Since no data is transferred through the web application in an inferential SQLi attack, the attacker does not see the outcome of the attack. This explains why the attacks are also known as blind SQLi attacks. Instead, the attacker will need to look at the response and behavioral patterns of the server to reconstruct the database. Inferential SQL Injection is classified into two: 

Boolean-based Blind SQLi

This type of inferential SQLi sends an SQL query to the database to prompt the application to return a result which will vary depending on whether the query is true or false.

Besides, the information within the HTTP response will remain the same or change depending on the result. The attacker will then conclude that the attack is true or false even if no data is returned from the database.

Time-based Blind SQLi

The attacker sends an SQL query to the database that prompts the database to wait for some seconds to respond. Depending on the wait time, the attacker can know whether the query results are true or false.

More specifically, an HTTP response will be returned immediately or after a delay depending on the result. This way, an attacker can conclude whether the message used returned a true or false, even without any data from the database.

Out-of-band SQLi

Unlike other SQL injection attacks, out-of-band SQLi can only be performed if certain features on the database are enabled. As such, it is not as common as other SQLis and is mainly used as an alternative to the inferential and in-band SQLi techniques.

Attackers will perform out-of-band SQLi if they cannot use the same channel to launch the attack and get results or when the server is too slow. This technique depends on the server’s ability to create DNS or HTTP requests to deliver data to an attacker.

How Does a SQL Injection Work?

A SQL injection targets vulnerabilities within the web page or web application. That means an attacker must find vulnerable user inputs in a web application to make an SQL injection attack.

Typically, a dynamic SQL statement comprises a predetermined set of parameters where the complete statement can only be generated once a user fills in their details. When the user enters their username and password, the statement becomes complete, and a query is sent to the server to gather the user’s info from the database.

An attack happens when a vulnerability exists within a dynamic SQL statement. In such a case, an attacker can enter complex scripts into the forms, thus interfering with the preexisting parameters. That way, the meaning of the complete statement is completely altered.

Examples of SQL Injection

As you can see, an SQL injection exploits a standard SQL query by taking advantage of vulnerabilities in the application’s database. To get a better glimpse of an SQL injection, let us look at the below example of a SQL database query for an eCommerce application.

Select itemname, item description

From items

Where itemnumber = item number

If an attacker wants to access unauthorized item names and descriptions in the database, they can enter a Uniform Resource Locator (URL). This can be something similar to this: http://www.ecommercesite.com/products.asp?productid=999 or 1=1. The corresponding SQL query will look something like this:

Select ItemName, ProductDescription

FROM Products

WHERE ProductNumber = 999 OR 1=1

An attacker will take advantage of incorrectly filtered characters (looking like this http://www.ecommercesite.com/products/products.asp?productid=999; DROP TABLE) to delete an entire database and generate this SQL query:

SELECT ProductName, ProductDescription

FROM Products

WHERE ProductNumber = 999; DROP TABLE USERS

This way, they can delete or drop an entire user database. That said, you may want to visit testphp.vulnweb.com to understand how developer errors and bad configuration can make your website vulnerable to SQLi attacks.

Ways to Prevent an SQL Injection

SQL injections attacks continue to be a dangerous threat to web administrators. Fortunately, website owners can do plenty of things to mitigate the danger.

Typically, preventing SQL injection is more of ensuring that none of the fields are vulnerable to invalid application execution and inputs. However, since it is practically impossible to check every application and page on the website, security analysts have derived ways to ensure your database is well-protected. That said, here are the steps to take to reduce the risk of a SQL injection attack:

Validate User Inputs

The most effective way to prevent SQL injection attacks is to validate user inputs. This involves identifying the essential SQL statements, establishing a list of all valid SQL statements, and separating them from invalidated statements.

In other words, the validation process helps verify whether the type of input submitted by a user is allowed or not. It makes sure that the input is in the accepted type, length, format, among other things. It is a way of ensuring that only the commands that pass the validation can be processed.

You should not only apply validation to fields that allow users to input fields; you should also validate the following situations:

• Make a whitelist for structured data such as name, age, zip code, and income to ensure strong input validation
• Determine which value is returned for a fixed set of values such as radio button, drop-down list, etc. As a rule of thumb, ensure that the input data matches the options provided.

Data Sanitization

Another great way to mitigate SQL attacks involves sanitizing data by limiting special characters. The truth is that attackers can take advantage of unique character sequences to manipulate a database. Data sanitization is also a sure way to avoid string concatenation.

You can achieve this using a configuration such as MySQL’s mysql_real_escape_string(). This prevents dangerous characters like a single quote from being part of an SQL query. The best way to avoid these unauthorized queries is by using prepared statements.

Parameterized Queries

Sometimes input validation and data sanitization will not solve your problem. In such a case, you may consider using prepared statements with parameterized queries to write all database queries. Parameterized queries allow the database to recognize the code and distinguish it from the input data.

Although dynamic SQL coding technique provides more flexible application development, it can also make SQLi vulnerabilities be accepted as code instructions. The database will view malicious SQL statement inputs as data rather than a potential command when using standard SQL.

While you can use parameterized queries with the MySQL extension, PHP 5.1 offers a better approach when working with databases (PHP Data Objects (PDO).

Stored Procedures

Unlike prepared statements, stored procedures reside in the database. It involves grouping one or more SQL statements into a logical unit to build an execution strategy. In other words, stored procedures refer to a type of code that you can store for later use.

When using this technique for SQL mitigation, you don’t need to write it repeatedly—you just need to call the stored procedure from the web application. However, stored procedures might not offer enough protection if dynamic SQL generation is used. So, you might need different approaches for optimal security.

Continuous Scanning and Penetration Testing

Continuous scanning and testing will help discover and identify vulnerabilities in applications and databases exploitable using SQL injection. Like other cybersecurity threats, it is important to stay up-to-date with the recent news and apply patches as soon as possible. The idea is to ensure that all web application software components, including frameworks, database server software, plug-ins, etc., are up to date.

Establish Appropriate Privilege

You cannot underestimate the power that SQL database holds for an organization. That is why you need to establish strict access policies. For example, if a website only requires the use of INSERT statements, you should restrict other privileges, including DELETE, UPDATE, etc.

Also, ensure that your database is accessed with admin-level privileges whenever necessary. This way, your data will be safe when conducting the general activity and limit attackers’ ability to access your data if the less-privileged credential is compromised.

Limit Extended URLs

SQLi attackers also send excessive long URLs to cause server failure when logging a complete request. For instance, Microsoft IIS is set to process over 4096 bytes long. If the web server fails, the attackers can perform queries undetected, thus causing havoc in your database. The best approach to avoid this is to set a limit for extended URLs.

No Shared Databases

Shared databases or user accounts can also put your website at risk of SQL injection. While shared databases or user accounts by multiple websites and web applications can make things easier for administrators, they are a recipe for disaster.

The catch is to limit linked servers’ access to only mission-based data and have minimal access to the target server. Also, ensure that linked servers have unique logins from other processes on the target server.

Regular Monitoring of SQL Statements

It is also crucial to continually monitor all SQL database statements, including stored procedures and prepared statements. Regular monitoring will give insights into how SQL statements function and make identifying vulnerabilities or rogue SQL statements easier. You can then delete unnecessary stored procedures, prepared statements, accounts, etc.

Regular Auditing

The need to perform regular audits of your database and website or application security cannot be overstated. Auditing logs for suspicious activity, user privileges, and variable binding terms is paramount.

Besides checking for malicious behavior, you need to conduct penetration tests to determine how your defenses respond to SQLi attacks and other potential dangers. Some threats to look out for include insecure passwords, retired software, unpatched vulnerabilities, cross-site scripting, and injections.

Wrapping Up

That is it. Now you know what SQL injection is and how to prevent it in your organization. The best approach to mitigate SQLi attacks is to view all user-submitted data as potential threats. By taking the above steps, you will deny attackers any leverage over SQL vulnerabilities and stay safe.