Invalid Prepared Statements In The Identifier By albro

Can prepared statements be used to protect identifiers? Can they be used in any case? Today I want to talk about the weakness of prepared statements.

The inability of prepared statements

In the previous post, we got acquainted with simple and beginner examples. Although those simple examples are widely used in the real world, we will also write more advanced queries. Queries that are more dynamic and complex and include identifiers or any more complex structure such as arrays.

Unfortunately, there is no way to protect identifiers against SQL injection attacks except for the same manual formatting:

$field = "`".str_replace("`","``",$field)."`";
$sql   = "SELECT * FROM t ORDER BY $field";
$data  = $db->query($sql)->fetchAll();

For arrays, we also do this in order to create a query:

$ids = array(1,2,3);
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in) AND category=?";
$stm = $db->prepare($sql);
$ids[] = $category; //adding another member to array
$stm->execute($ids);
$data = $stm->fetchAll();

In fact, it can be said that to format identifiers in MySQL, you must follow the following two rules:

1. Place the identifiers between two ` (this symbol is called backtick).
2. Escape backticks by doubling them.

For example, look at this query:

$table = "`".str_replace("`","``",$table)."`";

This code first finds the sign ` in the name of the table and then doubles it (i.e. ``) then puts the whole string inside two ` signs. After such formatting, it is completely safe to enter $table in the query. These rules are different for other databases, but the thing you should keep in mind is that separators alone are not enough and they must be escaped as well.

In fact, you should try to check dynamic identifiers with a list of allowed values:

$orders  = ["name","price","qty"]; //Field name
$key     = array_search($_GET['sort'],$orders); // Check if such a name exists. 
$orderby = $orders[$key]; //اIf it does not exist, the first one will be selected automatically
$query   = "SELECT * FROM `table` ORDER BY $orderby"; //Our quantity is safe

As I have explained in the code itself as a comment, instead of blindly receiving any value from the user, this code measures it with predetermined values to see if such a value is allowed at all. This method is called whitelisting.

I can do the same for the INSERT and UPDATE statements because MySQL supports the SET statement for both:

$data = ['name' => 'foo', 'submit' => 'submit']; // data for insert
$allowed = ["name", "surname", "email"]; // allowed fields
$values = [];
$set = "";
foreach ($allowed as $field) {
    if (isset($data[$field])) {
        $set.="`".str_replace("`", "``", $field)."`". "=:$field, ";
        $values[$field] = $data[$field];
    }
}
$set = substr($set, 0, -2);

In fact, the statement above creates the proper order for the SET operator, which only has fields and placeholders allowed, as in the following example:

`name`=:foo

To deal with these problems, various libraries are provided on GitHub that you can use. Some of these libraries are:

• Dibi
• DBSimple

You can choose your libraries based on your needs and use them in your projects by referring to the pages of these libraries in GitHub. The way these libraries work in simple language is that they bring placeholders along with their type. For example, %s means string or %d means digit. If you go to their page, you will easily understand how they work. All of them have explained how to work with the library on their page.

Prepared statements are very useful, but they are not enough everywhere, as you can see, they were not the answer in the case of identifiers. There are two general cases in which prepared statements are either impractical or insufficient:

• We have no way to use prepared statement in SQL keywords.
• When we have a list of dynamic identifiers that we receive from the user, but there are also fields that the user does not have permission to access. One of the usual cases is that we receive the key and value values from the $_POST array, and although we can format both, there are fields such as admin and permissions that should only be set by the site admin.

To solve both cases, we have to act in the above-mentioned way, i.e. whitelisting. In this method, you write the dynamic parameters in advance in your script, and all allowed values must be selected from them. For example, for an order, we can write like this:

$orders  = array("name","price","qty");
$key     = array_search($_GET['sort'],$orders));
$orderby = $orders[$key];
$query   = "SELECT * FROM `table` ORDER BY $orderby";

And for SQL keywords, we have no other way than whitelisting, because no formatting is possible for them, so:

$dir = $_GET['dir'] == 'DESC' ? 'DESC' : 'ASC'; 
$sql = "SELECT * FROM t ORDER BY field $dir";

For example, the SafeMysql library provides two functions for whitelisting; One in the form of key=>value arrays and the other for keywords alone.

 

[Hive: @albro]