SQL injection is one of the most common cybersecurity threats and as the name suggests, it is a form of injection attack.

Injection attacks, on the other hand, refers to any digital attack that allows an attacker to supply untrusted, malicious input to a program, essentially modifying the program’s purpose. So, this program can enter the database or the network unknowingly, while the injected script does the damage.

An SQL injection, more specifically, inserts malicious SQL programs into input fields. In turn, this statement will be executed by the underlying SQL database. This approach is made possible due to improper coding, creating vulnerabilities in the form of entry fields made available for user inputs.

In essence, the SQL injection can bypass the application’s security measures and retrieve the whole content of an entire SQL database. The attacker technically also can add, edit, and delete the whole records in this database.

All websites or web apps that use SQL databases are vulnerable to SQL injection, this includes MySQL, SQL Server, and Oracle among others. This is why SQL injection is among the top threats in website and application security. This is not only because of the severity of the potential damage, but also due to the frequency. In fact, SQL injection attacks represent more than 65% of all cybersecurity attacks related to web apps.

Types of SQL Injection

SQL injection attacks simply won’t happen when no vulnerabilities are present, and here are some of the most common vulnerabilities and the techniques to exploit them:

Classic SQL Injection (In-band SQL Injection)

The most common form of SQL injection attacks, these attacks happen when the vulnerabilities allow the attacker to use just one communication channel to make the injection and also to gather results (in-band):

1. Error-Based SQL Injection

This type of vulnerability is caused by database errors from the PHP application or a web page, that has been triggered by user inputs. When the SQL injection attack is underway, this technique exploits error messages to return full-query results while revealing confidential content from the database.

Another common practice is to use this technique to identify whether a PHP application is vulnerable and obtain information to launch further attacks (i.e. DDoS, data breach, etc.).

2. Union-based SQL Injection

This technique especially leverages the SQL Union operator to combine two or more results from Select statements into a single result. This combination is then returned as a part of the HTTP response.

Inferential SQL Injection or Blind SQL Injection

In an inferential SQL injection attack, no data is actually transferred via the PHP application or website, so the attacker would not be able to verify the result in the same channel (not in-band). This is why this type of attack is commonly called “Blind SQL Injection). The attacker must rebuild the structure by observing the PHP application response and the resulting behavior.

1. Time-Based SQL Injection

In this technique, the attacker instructs the database to sleep by exploiting vulnerabilities, even when the vulnerable SQL query doesn’t initially have any visible effect on the output of the page. If the page is not vulnerable, it will load as normal. However, if the page is vulnerable, it will take longer than usual to load. This enables hackers to extract data.

Time=based SQL injections are commonly used to check whether there are vulnerabilities on a PHP application or website, and often used together with Boolean-based techniques, as discussed below.

2. Boolean-Based SQL Injection

An SQL injection technique that forces the PHP application to return a different TRUE/FALSE result than the actual query. So, this type of attack manipulates the condition and logic of the query, tricking the database that the attacker possesses the correct credentials.

Depending on the result, the HTTP response will change or remain the same, and so the attacker can assess whether the payload used returned TRUE or FALSE. Even though no data from the database is returned. A boolean-based injection is typically slow, especially if it’s a large database.

Out of Band SLI Injection

Out of band SLI injection is pretty rare because it relies on the server’s ability to show the same response regardless of the user’s input and the database error, and the attacker must be able to control the HTTP requests or DNS server to retrieve the output. For example, in Oracle, we can use the UTL_HTTP package to send HTTP requests from SQL to a remote server controlled by the attacker.

Although rare, this is still an alternative when inferential techniques aren’t possible, for example, if the server responses aren’t very stable and so restructuring the result of inferential injection is difficult.

How To Prevent SQL Injections in PHP

Preventing vulnerabilities related to SQL injection is actually pretty simple, and the SQL codes should not include:

• Dynamic queries
• Possibility for users to provide user inputs

And we can use the following techniques to ensure these two factors:

1.Using Prepared Statements WIth Parameterized Queries

We can use prepared statements to eliminate the possibility of SQL injection in your PHP application. Again, we should avoid using dynamic queries, and parameterized queries are generally simpler to write and easier to understand than dynamic queries.

Parameterized queries can prevent SQL injection because they force the developer to first define all the SQL queries and you can pass each parameter to the query. This way, the database can properly differentiate between a legitimate SQL command and user input (which is the main source of SQL injection).

With prepared statements, we can ensure the attacker cannot change the actual intent of an SQL query, even when an injection does happen. When proper parameterization is implemented to all the SQL queries, all user input that is passed to the database is treated as data, and the database won’t confuse it as being a part of the SQL command.

As long as user-provided inputs and/or environment variables are not connected directly to the SQL statement, we can be sure almost 100% of the time that SQL injection won’t happen. There are several exceptions. For example, the attacker can store the payload in a stored procedure (which we will discuss below), we call it second-order SQL injection.

There are several approaches in writing parameterized queries depending on the programming language used. However, specific to PHP application, we can use PDO with strong parameterized queries, as we will discuss below.

PHP Data Objects (PDO)

Using PHP Data Objects (PDO) is actually a better way to access and work with databases rather than using MySQL extensions or other similar approaches. PDO offers two distinct advantages: it allows the code easier to read and more compact, and you can use it with various databases (not only MySQL). Also, it’s very easy and intuitive to use PDO in writing parameterized queries.

2. Protecting Stored Procedures

Stored procedures are subroutines that are stored in a database. A proper procedure has a name, SQL statement(s), and parameter list.

As discussed above, stored procedures are not 100% safe from second-order SQL injection. However, when implemented properly and safely, the stored procedure programming. That is, we can say that the stored procedure is safely implemented when it does not include any unsafe dynamic SQL queries. As mentioned, it’s very important not to include any unsafe dynamic queries in your SQL database.

If for one reason or another we have to use dynamic SQL, then the stored procedure must include a proper input validation and/or proper escaping (we will discuss SQL escaping further below).  This is to make sure that all user input made to the stored procedure can’t be used for SQL injection to the dynamic queries. It’s important to audit the stored procedures regularly for uses of sp_execute, execute, or exec.

In certain cases, stored procedures can provide increased vulnerabilities to SQL injection. Stored procedures require execute rights, a role that are not available by default, especially in older MS SQL servers. So, in some setups, all PHP applications and web apps run under db_owner rights to allow stored procedures. This will mean that if the server is breached, the attacker now has full rights to control the database (as owner).

3. Input Validation

There are cases where using parameterized queries and/or bind variables is simply impossible. For example, we can’t implement bind variables on the names of columns. In such cases, we can implement input validation to make sure unvalidated user input doesn’t end up in the query.

The general rule of thumb is that any time a user input can be converted into a non-string (numeric, date, boolean, enumerated, etc. ), we should implement input validation before it is appended to a query. However, input validation is generally recommended in all cases, even in the case when the queries have been properly parameterized or bind variables are implemented properly.

You might want to check this guide on how you can implement input validation with PHP.

4. Escaping User Input

Escaping user-supplied input is a fairly unreliable technique compared to the others listed above, and so generally this should only be used as last resort. It is usually recommended when implementing input validation is not possible or not cost-effective due to one reason or another (i.e. need to rewrite a lot of legacy codes).

As the name suggests, this technique is implemented to ‘escape’ user input before it’s appended to a query. Not only input escaping is not 100% effective, but it is also difficult to implement, so again, use this only as a last resort.

Each DBMS (Database Management System) usually supports one or more escaping method, which usually only applies to certain kinds of queries. We should escape all user input based on the proper escaping scheme for the specific database you are using, so the DBMS won’t confuse the input with legitimate SQL command written by the developer–avoiding SQL injection.

In PHP, we have two options to implement escaping, using PDO or MYSQLi for MySQL.

End Words

Preventing SQL injection is certainly better than curing it, and to summarize, here are what you should do to prevent SQL injection in PHP:

• ALL user input (if they exist) must be validated for a definite set of rules for type, length, and syntax
• Always give the least rights while giving administrative rights to specific users.
• Make sure that any user with given rights for specific application cannot access the application unless necessary
• Remove unused store procedures, and be extra careful in protecting and using stored procedures. Stored procedures are typically vulnerable to SQL injections.

Being careful when processing user input and querying the database is usually enough in preventing SQL injections.