SQL Injection. How to prevent it from happening in PHP.

SQL injection as a matter of security can be approached from multiple angles. One must first be sure that the targeted application has a confidential database structure, since this is the main thing that makes SQL injection possible.

Also, it’s important to notice that SQL injection is not something that happens arbitrary or from time to time, it takes place on most known websites. In figure 1 are illustrated the results of a study realized by Ponemon Institute about the intensity at which SQL injection takes place on most websites (year 2013):

(Source: http://securityaffairs.co/wordpress/24094/cyber-crime/ponemon-sql-injection-attacks.html)

Also, given the high frequency of the SQL injection attacks in the past year, it was also summarized what shape the attacks take during their lifetime:

(Source: http://securityaffairs.co/wordpress/24094/cyber-crime/ponemon-sql-injection-attacks.html)

The method of a SQL injection is simple. Given a form on a website that sends the data from the input to the database in a SQL query, the data can be tailored in such a way that the query results in a totally different outcome.

For example, the most common SQL injection would look like this:

-- Usual query
INSERT INTO employees(name, surname) VALUES ($name, $surname);

-- Tailored data: $name = ‘ ”Test” ’, $surname = ‘ “test” ); DROP TABLE employees; --’
-- Resulting in the final query
INSERT INTO employees(name,surname) VALUES(“Test”, “test”); DROP TABLE employees; -- );

Running the above code would delete all data in the table ‘employees’. This is however the most common of the SQL injections and most websites have specific protection against it. There are however many more subtle SQL injection mechanisms. For example, showing the mysql result of a query (like the list of employees) can be tailored to select other fields from the database and show them:

-- Usual query
SELECT name, surname FROM employees WHERE department = $dept;

-- Tailored data: $dept = ‘ “WEB”; SELECT username, password FROM users; --’
-- Resulting in the final query
SELECT name, surname FROM employees WHERE department = “WEB”; SELECT username, password FROM users; -- );

The above injection is harder to identify (since it causes no data loss) but allows for unrestricted access onto the website.

Fortunately, there are multiple ways to protect against SQL injection when developing in PHP. There are different levels of doing this:

1. The best way would be to use prepared statements and parameterized queries, like so:
   Using PDO:

   $query= $pdo->prepare('SELECT * FROM employees WHERE name = :name'); 
   $query->execute(array('name'=> $name));
   

   Using MySQLi:

   $query= $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
   $query->bind_param('s', $name); 
   $query->execute(); 
   $result= $query->get_result();
   

2. Another way is to develop using a framework and use the methods provided by that framework. It’s only second best if the framework does not handle sql building well. However, most frameworks do not have this issue (examples: CakePHP, Yii, Symfony).

3. Third way is to escape the input data, but this is generally considered incomplete and should only be used if it’s the only security available at that specific moment:

   $unsafe_variable= $_POST["user-input"];
   $safe_variable= mysql_real_escape_string($unsafe_variable);
   

 

Note: In all examples above and in all SQL injections in general, it is necessary for the attacker to know the structure of the database. So it’s therefore always good to have your tables start with a prefix (like “appname_”) and use custom structures instead of those vastly known.

Finally, there’s another very important peculiarity of what does Cialis that brings it so high above its alternatives. It is the only med that is available in two versions – one intended for use on as-needed basis and one intended for daily use. As you might know, Viagra and Levitra only come in the latter of these two forms and should be consumed shortly before expected sexual activity to ensure best effect. Daily Cialis, in its turn, contains low doses of Tadalafil, which allows to build its concentration up in your system gradually over time and maintain it on acceptable levels, which, consequently, makes it possible for you to enjoy sex at any moment without having to time it.