5 common mistakes made by PHP developers when writing SQL

There are several ways to connect to a MySQL database in PHP. The most common are the MySQL API, MySQLi API, and PDO API (PHP data objects). The latter two support more functions than the old MySQL API and are more secure.

If you are using the old “mysql_” functions, you should stop and learn the new PDO API. These old MySQL functions are obsolete and are no longer supported in PHP 7.x.

This is a bad practice:

$con = mysql_connect("localhost", "root", "mypass") or
die ("failed to connect: " . mysql_error());
mysql_select_db("tutorials");
$result = mysql_query("select * from tutorials");
echo "<h2>This is the list of themes:</h2>";
while ($row = mysql_fetch_array($result)) {
echo $row['name']."<br />";
}
mysql_close($con);

Best practice:

require_once('includes/conn.inc.php');
$sql= "SELECT name, age FROM employees WHERE company_id = 10";
$stmt = $pdo->query($sql);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['name'];
echo $row[age];

Do not shield the input from the client using the shielding functions.

Manual screening of query variables with mysql_real_escape_string is considered unsafe for two reasons:

• When you use this method regularly, you will surely skip it once. All an attacker needs are one loophole through which he can embed his code in your SQL queries.
• When using string variables, remember to use quotes, which is not very natural.
• Use prepared statements instead (see more information below).

Don’t think that prepare operators are always safe in PHP

The purpose of the prepared operators is to separate the query from the data so that the data could be correctly inserted into the query parameters without any manipulation options. In most cases, prepared operators are considered very safe to use, and this is considered to be the best practice for entering user parameters into queries.

Code example:

require_once('includes/conn.inc.php');
$sql= "SELECT * FROM employees";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
foreach($result as $row){
echo "
<li>{$row['employeeName']}</li>";
}

So where is the lack of ready-made applications in PHP? This is the case when you enter a user input into a request, but the prepared operators do not support this implementation.

For example, MySQL PDO does not support parameter input (using “?” filler) in the LIMIT specifier. In addition, the custom input cannot be inserted as table names or columns in a query. If you use prepared operators in these cases, you should carefully sanitize the data manually or, better yet, use a library that will do it for you and has already been tested.

Do not think that ORM platforms are not subject to SQL injection attacks

The use of ORM can be great. This suggests that it does not mean that it is not vulnerable to SQL injection attacks. It’s true that it’s harder to embed code in a query generated by ORM, but that doesn’t mean that a programmer can’t make a mistake that opens a loophole.

In most cases, this attack can be performed by combining user input into an ORM query without using trained operators. Yes, ORM platforms such as Doctrine provide the ability to use trained operators, so use them. Never merge strings into a query, be it an ORM query or a raw SQL query.

By the way, it is not enough to follow the rules – be sure to check the use and implementation of ORM after encoding to make sure it is not open to any SQL injection.

Bad practice

In the following code example, you can see that the parameter that was entered by the user is combined with Doctrine DQL, which exposes the application to SQL injection.

<?.php

// INSECURE
$dql = "SELECT u
FROM MyProject\Entity\User u
WHERE u.status = '" . $_GET['status'] . "'
ORDER BY " . $_GET['orderField'] . " ASC";

Bad practice

As recommended for non-ORM users, the use of trained operators is recommended when using the ORM platform (e.g. Doctrine).

<?.php

$orderFieldWhitelist = array('email', 'username');
$orderField = "email";

if (in_array($_GET['orderField'], $orderFieldWhitelist)) {
$orderField = $_GET['orderField'];
}

$dql = "SELECT u
FROM MyProject\Entity\User u
WHERE u.status = ?1
ORDER BY u." . $orderField . " ASC";

$query = $entityManager->createQuery($dql);
$query->setParameter(1, $_GET['status']);

Do not underestimate the power of encoding symbols

According to OWASP (Open Security Application Project Project), the absence of utf8mb4 will open up different types of attacks (you can learn more about encoding circumvention). In addition, you should regularly use utf8mb4 in PHP and MySQL for better and more standard multilingual support.

Example code

$dsn = 'mysql:host=example.com;dbname=testdb;port=3306;charset=utf8mb4';

Synthesis

We looked at 5 very common errors that many PHP developers make. Some of them are due to lack of knowledge (new platforms are available all the time, make sure you know about them).

Some bugs happen because of a lack of experience (read, read, and read more to make sure you use the most secure and best APIs). Good luck with your next project!