Php pdo and sql injection

Prevent SQL Injection in PHP

Do you know that 42% of the attacks faced by public systems are SQL injection attacks? Prevent SQL injection to avoid the most occurring security vulnerability in a public facing system.

SQL injection is the higher most threat faced by any software system. All you need is just parameterised queries to prevent SQL injection and make your application safer.

Quick example

How to prevent SQL injection in PHP using MySQLi.

// use prepared statement to prevent SQL injection $preparedStatement = $dbConnection->prepare('SELECT * FROM animals WHERE name = ?'); $preparedStatement->bind_param('s', $name); $preparedStatement->execute(); $result = $preparedStatement->get_result(); while ($row = $result->fetch_assoc()) < // Process $row >

What is SQL injection attack?

SQL injection is a software security vulnerability that allows a user to intercept the SQL query execution and execute queries of their choice.

SQL injection prevention is essential, because this security vulnerability will allow the user to perform unintentional CRUD,

In the worst case scenario, the attacking user can take control of the complete software infrastructure using sql injection. Prevent SQL injection to avoid a serious business loss.

When a software security audit is conducted, prevent SQL injection should be the first and foremost item in the checklist. Lethargic approach in SQL injection prevention may lead to data breaches, loss of sensitive data like bank and credit card information, etc.

SQL injection may lead to prolonged unauthorized access to software systems. It will lead to serious financial and personal information loss.

Preventing SQL injection in PHP is even straightforward. The PHP frameworks that we use for database interaction like PDO, MySQLi provide sophisticated mechanisms.

How to detect SQL injection vulnerabilities

SQL injection is the first and foremost serious security threat to a software. To prevent SQL injection we need not invest huge effort, as it can be easily identified and fixed.

There are security scanning tools available that can detect and report SQL injection vulnerability. SQL injection can also easily be detected using manual code reviews and functional tests.

Following are some of the sample test cases. Input the below and check for the application behaviour. If it returns any error and results in an unexpected condition, then you have a task at hand to prevent sql injection.

• Input single quote character ‘ and submit.
• Input a boolean expression that evaluates to true or false. Example are 1=1 or 1=2.

These above two are simple cases. That will detect a SQL injection vulnerability.

How to prevent SQL injection

To prevent SQL injection, it does not require any huge framework implementation or costly processes. All we need to do is avoid concatenation of user input in the SQL queries.

When you get user input via a form, as a security precaution, always filter and sanitize user input using PHP functions before usage.

To prevent SQL injection, use parameterised queries, that should be sufficient in majority of the cases.

Example for SQL injection

The below SQL query takes a parameter. User’s input is directly appended with the query.

// pseudo code describing SQL injection vulnerability String sqlQuery = "SELECT * FROM animals WHERE type = '"+ $userInput + "'"; Statement sqlStatement = dbConnection.createStatement(); ResultSet result = sqlStatement.executeQuery(sqlQuery); 

In the above scenario, ‘userInput’ is directly appended to the SQL query. For example, the user can give an input that can terminate the SQL query and continue with his own sub-query.

"'dummy'; DELETE FROM animals" 

If the user inputs the above. The string concatenation in the SQL query makes this an opportunity for SQL injection.

First part of the query is abruptly finished with just a search for value ‘dummy’. Then the second SQL query gets executed which deletes all the records from the ‘animals’ database table.

This second SQL query performs malicious activity via SQL injection. Sot the golden rule is, to prevent SQL injection, use Prepared Statement.

Prepared statement to prevent SQL injection

Prepared statement or parameterized statement allows to declare a SQL query in a template form. Then substitute the parameters using values and execute the query.

In addition to preventing SQL injection, prepared statement also gives better performance. The substitution and execution can be repeated multiple times which improves the efficiency of the SQL query execution. This will result in gain of speed where the same SQL query is executed multiple times.

// prevent SQL injection using prepared statement PreparedStatement preparedStatement = dbConnection.prepareStatement("SELECT * FROM animals WHERE type = ?"); preparedStatement.setString(1, $userInput); ResultSet sqlResultSet = preparedStatement.executeQuery(); 

The above pseudo code explains how the prepared statement can be used to prevent sql injection. PHP’s MySQLi and PDO supports Prepared Statements and it is easy to use.

So always make it a practice to not to append dynamic values to a query, just via string concatenation. To prevent SQL injection, any part of the SQL query should not be constructed using dynamic string concatenation.

Prevent SQL injection in PHP using PDO

PDO is the ‘PHP data objects’, an extension for PHP to accessing databases. PDO is available with PHP by default. Refer CRUD using PHP PDO for a full fledged PDO based PHP example.

// prevent SQL injection in PHP using PDO prepared statement $sqlStatement = $pdoConnection->prepare('SELECT * FROM animals WHERE type = :type'); $sqlStatement->execute([ 'type' => $type ]); foreach ($sqlStatement as $resultRow) < // process $resultRow >

When you create a PHP PDO database connection, remember to set the attribute ATTR_EMULATE_PREPARES to value false. Because, by default it is set to true.

This instructs PHP PDO to disable emulated prepared statements. This is to ensure that the values are not parsed before sending to the MySQL database server.

$pdoConnection = new PDO('mysql:dbname=animaldb;host=127.0.0.1;charset=utf8', 'dbusername', 'dbpassword'); $pdoConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); 

In the above code “:type” is the placeholder in the SQL template. This is key to prevent SQL injection.

By using this template substitution method, we are not parsing the value in the PHP side. We just pass the value “$type” as substitution value to the database server.

It is substituted by the database engine and the query is evaluated and executed. This way we prevent SQL injection.

PHP PDO insert example

Following code snippet explains how to prevent SQL injection using PHP PDO during database insert operation.

// example to prevent SQL injection using PHP PDO insert $preparedStatement = $pdoConnection->prepare('INSERT INTO table (column) VALUES (:columnname)'); $preparedStatement->execute([ 'columnname' => $userInput ]); 

Prevent SQL injection in PHP using MySQLi

MySQLi is the ‘MySQL Improved’ extension to access the MySQL database in PHP. It is the improved version of the now obsolete MySQL extension. Refer CRUD with MySQLi using prepared statement for a full fledged example.

// prevent SQL injection in PHP using MySQLi prepared statement $preparedStatement = $dbConnection->prepare('SELECT * FROM animals WHERE type = ?'); // 's' specifies the variable data type as a 'string' $preparedStatement->bind_param('s', $type); $preparedStatement->execute(); $result = $preparedStatement->get_result(); while ($row = $result->fetch_assoc()) < // Process $row >

PHP database util

Following is a database utility that can be used as a generic class for accessing database. It is written for MySQLi extension and can be easily adapted for PHP PDO. The way it is designed is using Prepared Statement and so implicitly it used to prevent SQL injection in PHP.

In the article PHP login script with session, I have demonstrated how to use this DataSource PHP class. Login is a critical component in any website. So while building a website, you can use this PHP database utility class and prevent SQL injection.

conn = $this->getConnection(); > /** * If connection object is needed use this method and get access to it. * Otherwise, use the below methods for insert / update / etc. * * @return \mysqli */ public function getConnection() < $conn = new \mysqli(self::HOST, self::USERNAME, self::PASSWORD, self::DATABASENAME); if (mysqli_connect_errno()) < trigger_error("Problem with connecting to database."); >$conn->set_charset("utf8"); return $conn; > /** * To get database results * @param string $query * @param string $paramType * @param array $paramArray * @return array */ public function select($query, $paramType="", $paramArray=array()) < $stmt = $this->conn->prepare($query); if(!empty($paramType) && !empty($paramArray)) < $this->bindQueryParams($stmt, $paramType, $paramArray); > $stmt->execute(); $result = $stmt->get_result(); if ($result->num_rows > 0) < while ($row = $result->fetch_assoc()) < $resultset[] = $row; >> if (! empty($resultset)) < return $resultset; >> /** * To insert * @param string $query * @param string $paramType * @param array $paramArray * @return int */ public function insert($query, $paramType, $paramArray) < print $query; $stmt = $this->conn->prepare($query); $this->bindQueryParams($stmt, $paramType, $paramArray); $stmt->execute(); $insertId = $stmt->insert_id; return $insertId; > /** * To execute query * @param string $query * @param string $paramType * @param array $paramArray */ public function execute($query, $paramType="", $paramArray=array()) < $stmt = $this->conn->prepare($query); if(!empty($paramType) && !empty($paramArray)) < $this->bindQueryParams($stmt, $paramType="", $paramArray=array()); > $stmt->execute(); > /** * 1. Prepares parameter binding * 2. Bind prameters to the sql statement * @param string $stmt * @param string $paramType * @param array $paramArray */ public function bindQueryParams($stmt, $paramType, $paramArray=array()) < $paramValueReference[] = & $paramType; for ($i = 0; $i < count($paramArray); $i ++) < $paramValueReference[] = & $paramArray[$i]; >call_user_func_array(array( $stmt, 'bind_param' ), $paramValueReference); > /** * To get database results * @param string $query * @param string $paramType * @param array $paramArray * @return array */ public function numRows($query, $paramType="", $paramArray=array()) < $stmt = $this->conn->prepare($query); if(!empty($paramType) && !empty($paramArray)) < $this->bindQueryParams($stmt, $paramType, $paramArray); > $stmt->execute(); $stmt->store_result(); $recordCount = $stmt->num_rows; return $recordCount; > > 

Related attacks to SQL injection

• SQL Injection Bypassing WAF
• Blind SQL Injection
• Code Injection
• Double Encoding
• ORM Injection

We will see about each of these in the upcoming articles one by one.

Further reading

Источник