How to Prevent SQL Injection in PHP

What Do I Need?

• Any Dedicated or Virtual Server
• Ubuntu
• Putty

What is a SQL Injection?

An SQL injection is a technique that attackers apply to insert SQL queries into input fields to then be processed by the underlying SQL database. These weaknesses are then able to be abused when entry forms allow user-generated SQL statements to query the database directly. To give you an example scenario, take a typical login form consisting of a user and email field and a password field. After the login info is submitted, it’s combined with an SQL query on your web server. In PHP, the command is written in the following way:

<?php
$query = "SELECT * FROM users WHERE username = '" . $_POST['username'] . 
"'";
$query .= " AND password = '" . $_POST['password'] . "'";
?>

It’s sent to the server to verify if it was given a valid username with a corresponding password. A username ‘xander’ with the ‘drake’ password would result in this command:

SELECT * FROM users WHERE username='xander' AND password='drake'

But if they put something like ‘xander’;–’, the query would look like this:

SELECT * FROM users WHERE username='xander'; -- ' AND password='drake'

In this scenario, the attacker is using SQL comment syntax. The remaining code after the double-dash (–) sequence won’t run. Meaning an SQL would be:

SELECT * FROM users WHERE username='xander';

It’ll then return user data that was entered in the password field. This move could allow the login screen to be bypassed. An attacker can also go further by adding another select condition, ‘OR 1=1’, that’ll result in the following query:

SELECT * FROM users WHERE username='xander' OR 1=1;

The query returns a non-empty dataset for any potential login with the entire ‘users’ table database. The hack above showed you a significant security flaw of any site, but it’s only a small example of what it could do. More advanced hacks will allow an attacker to run arbitrary statements, causing much bigger damage. This can lead to:

• extraction of private data, such as credit cards, passports, hospital records,
• enumeration of the authentication user details, allowing these logins to be used on other websites,
• a corrupted database, execution of OS commands, deleted or inserted data, and destroyed operations for the entire website
• a full system compromise

1. SQL Injection Prevention Techniques

With user input channels being the main vector for such attacks, the best approach is controlling and vetting user input to watch for attack patterns. Developers can also avoid vulnerabilities by applying the following main prevention methods.

2. Input Validation

The validation process is aimed at verifying whether or not the type of input submitted by a user is allowed. Input validation makes sure it’s the accepted type, length, format, and so on. Only the value which passes the validation can be processed. It helps counteract any commands inserted in the input string. In a way, it’s similar to looking to see who is knocking before opening the door.

3. Parameterized Queries

Parameterized queries are a means of pre-compiling an SQL statement so that you can then supply the parameters in order for the statement to be executed. This method makes it possible for the database to recognize the code and distinguish it from input data. The user input is automatically quoted and the supplied input won’t cause the change of the intent, so this coding style helps mitigate an SQL injection attack. It’s possible to use parameterized queries with the MySQLi extension, but PHP 5.1 presented a better approach when working with databases: PHP Data Objects (PDO). PDO adopts methods that simplify the use of parameterized queries. Additionally, it makes the code easier to read and more portable since it operates on several databases, not just MySQL.

4. Stored Procedures

Stored procedures (SP) require the developer to group one or more SQL statements into a logical unit to create an execution plan. Subsequent executions allow statements to be automatically parameterized. Simply put, it’s a type of code that can be stored for later and used many times. So, whenever you need to execute the query, instead of writing it over and over, you can just call the stored procedure.

5. Escaping

Always use character-escaping functions for user-supplied input provided by each database management system (DBMS). This is done to make sure the DBMS never confuses it with the SQL statement provided by the developer.

6. Avoiding Administrative Privileges

Don’t connect your application to the database using an account with root access. This should be done only if absolutely needed since the attackers could gain access to the whole system. Even the non-administrative accounts server could place risk on an application, even more so if the database server is used by multiple applications and databases. For that reason, it’s better to enforce least privilege on the database to defend the application against SQL injection. Ensure that each application has its own database credentials and that those credentials have the minimum rights the application needs. Instead of trying to determine which access rights you should take away, focus on identifying what access rights or elevated permissions your application needs. If a user only needs access to some parts, you could create a mode that strictly serves this function.

7. Web Application Firewall

One of the best practices to identify SQL injection attacks is having a web application firewall (WAF). A WAF operating in front of the web servers monitors the traffic which goes in and out of the web servers and identifies patterns that constitute a threat. Essentially, it’s a barrier put between the web application and the Internet.

A WAF operates via defined customizable web security rules. These sets of policies inform the WAF what weaknesses and traffic behavior it should search for. So, based on that information, a WAF will keep monitoring the applications and the GET and POST requests it receives to find and block malicious traffic.

The value of a WAF comes in part from the ease with which policy modification can be enforced. New policies can be added in no time, enabling rapid rule implementation and fast incident response.

Conclusion

Prevention techniques such as input validation, parameterized queries, stored procedures, and escaping work well with varying attack vectors. However, because of the large variation in the pattern of SQL injection attacks, they’re often unable to protect databases.

Therefore, if you want to cover all bases, you should apply the above-mentioned strategies in combination with a trusted WAF. The primary WAF benefit is that it provides protection for custom web applications that’d otherwise go unprotected.

• Want suggestions about Best dedicated servers hosting? Click here and read more.