How to Prevent SQL Injection in PHP applications

How to Prevent SQL Injection in PHP applications

SQL injection is a technology used to control database queries, which often results in loss of confidentiality. In some cases

SELECT '
 

Attackers can take down the server, and code injection (including SQL, LDAP, operating system commands, and XPath injection technology) remains in the top 10 of OWASP vulnerabilities for many years.

More people share their knowledge about application security. Unfortunately, most of the things circulating on the Internet (especially old blog posts and high search engine rankings) are outdated. Although it is unintentional misleading, it causes a great threat.

How to Prevent SQL Injection

Pre-processing statements are also called parameterized queries. For example:

$stmt = $pdo->prepare('SELECT * FROM blog_posts WHERE YEAR(created) = ? AND MONTH(created) = ?');if (!$stmt->execute([$_GET['year'], $_GET['month']])) {    header("Location: /blog/"); exit;}$posts = $stmt->fetchAll(\PDO::FETCH_ASSOC);

?? In PHP applications, the pre-processing statement filters out any possibility of SQL injection. Whatever it is, it must first be passed to the $ _ GET variable. SQL query statements cannot be changed by attackers (unless you enable PDO: ATTR_EMULATE_PREPARES, which means that you have not actually used preprocessing statements)

Pre-processing statements solve the fundamental problem of application security: The operation commands and data are processed separately by sending a completely independent package. This is a bit similar to the problem that causes stack overflow.

As long as you do not use SQL statements to connect user-provided variables and environment variables (and make sure you do not use emulated prepares), you do not have to worry about cross-SQL injection.

Important

The pre-processing statement ensures the interaction between the WEB application and the database service (even if the two are not on the same machine, they are connected through TLS ). Attackers may also store a payload in the field, which is quite dangerous. For example, a stored procedure is called high-order SQL injection.

In this case, we recommend that you do not write a stored procedure, which will create a high-order SQL injection point.

About output Filtering

Someone should have seen this cartoon about SQL injection attacks. It is often referenced in some security meetings, especially in articles for newcomers. This cartoon reminds us to improve our awareness of dangerous user input in database queries. However, the advice in the cartoon is to filter out database input and understand related issues, we know that this is only a compromise.

It is best to forget to filter input.

Although the incoming data streams can be overwritten before the data is sent to the database to prevent attacks, this process is difficult to control.

Unless you want to spend time researching to fully master all Unicode format applications, you 'd better not try to filter your input.

In addition, changing your input data stream may cause data corruption. Especially when you are processing original binary files (images, encrypted information.

The pre-processing statement can prevent SQL injection.

Randall Munroe, author of XKCD, is a smart person. If this cartoon was not created until today, it will probably look like this.

The input should still be verified

Data verification and filtering are not the same thing. Preprocessing statements can prevent SQL injection, but they won't help you get rid of bad data. In most cases, filter_var () is used ()

$email = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL);if (empty($email)) {        throw new \InvalidArgumentException('Invalid email address');}

Column and table identifier

When columns and table identifiers are part of a query statement, you cannot use parameters to represent them. If the application you are developing requires a dynamic query structure, use the whitelist.

A whitelist is an application logic policy that allows only a few trusted values. Relatively speaking, blacklists only prohibit known malicious input.

In most cases, using a whitelist is safer than using a blacklist!

$qs = 'SELECT * FROM photos WHERE album = ?';// Use switch-case for an explicit whitelistswitch ($_POST['orderby']) {        case 'name':        case 'exifdate':        case 'uploaded':               // These strings are trusted and expected       $qs .= ' ORDER BY '.$_POST['orderby'];       if (!empty($_POST['asc'])) {           $qs .= ' ASC';       } else {                      $qs .= ' DESC';       }        default:           // Some other value was passed. Let's just order by photo ID in descending order.       $qs .= ' ORDER BY photoid DESC';}$stmt = $db->prepare($qs)    ;if ($stmt->execute([$_POST['album_id']])) {        $photos = $stmt->fetchAll(\PDO::FETCH_ASSOC); }

Is it troublesome to use preprocessing statements?

When developers encounter preprocessing statements for the first time, they are frustrated by the need to write a large amount of redundant code (extraction, execution, retrieval, extraction, execution, and retrieval ;.... Bored)

As a result, EasyDB [https://github.com/paragonie/easydb#is born.

How to Use EasyDB

There are two methods.

You can use EasyDB to include your PDO

If you are familiar with PDO construction, you can use the \ ParagonIE \ EasyDB \ Factory: create () parameter to replace

// First method:$pdo = new \PDO('mysql;host=localhost;dbname=something', 'username', 'putastrongpasswordhere');$db = \ParagonIE\EasyDB\EasyDB($pdo, 'mysql');// Second method:$db = \ParagonIE\EasyDB\Factory::create('mysql;host=localhost;dbname=something', 'username', 'putastrongpasswordhere');

If you have an EasyDB object, you can use it to quickly develop secure database applications.

Pre-processing statement: Security database query

$data = $db->safeQuery(        'SELECT * FROM transactions WHERE type = ? AND amount >= ? AND date >= ?',     [        $_POST['ttype'],                $_POST['minimum'],                $_POST['since']    ]);

Select multiple rows from a database table

$rows = $db->run(     'SELECT * FROM comments WHERE blogpostid = ? ORDER BY created ASC',          $_GET['blogpostid']);     foreach ($rows as $row) {         $template_engine->render('comment', $row);}

Select a row from the database table

$userData = $db->row(    "SELECT * FROM users WHERE userid = ?",        $_GET['userid']);

Insert a new row into the database table

$db->insert('comments', [    'blogpostid' => $_POST['blogpost'],        'userid' => $_SESSION['user'],        'comment' => $_POST['body'],        'parent' => isset($_POST['replyTo']) ? $_POST['replyTo'] : null    ]);

Escape identifier in dynamic query (column/table/view name)

$qs = 'SELECT * FROM some_table';$and = false;if (!empty($where)) {        $qs .= ' WHERE ';        foreach (\array_keys($where) as $column) {            if (!\in_array($column, $whiteListOfColumnNames)) {                continue;        }            if ($and) {                $qs .= ' AND ';        }            $qs .= $db->escapeIdentifier($column).' = ?';            $and = true;    }}$qs .= ' ORDER BY rowid DESC';// And then to fetch some data$data = $db->run($qs, \array_values($where);

Warning: The escapeIdentifier () method means that the input should not be escaped.