To follow up on my previous post about user input in PHP I'm now going to discuss one of the biggest issue in PHP which is SQL Injection vulnerabilities.

SQL Injection is when an end user can run arbitrary SQL commands and even shell commands. There are various way to mitigate this issue, the best solution is to use PDO to interact with the database using parameterize SQL Queries.

Here is an example of some SQL Injection vulnerable code:

<?php
$db->query("SELECT * FROM tags WHERE name='" . $_GET['tag'] . "'");

Now how can I inject some SQL into this Query. Here are some examples of the value of $_GET['tag'] that will inject some SQL.

$_GET['tag'] = "';DROP TABLE users"; // This will escape from the orginal query and then drop the users table
$_GET['tag'] = "';SELECT * FROM users"; // This will get all the user records from the users table
$_GET['tag'] = "';\! rm -rf /'"; // Delete all files on the server

As you can see SQL Injection can cause some bad things.

There are 3 things you should do when setting up a database for your PHP application:

• Create a database user just for that application
• Only give that database user access to the database for the application
• NEVER USE THE ROOT DATABASE USER FOR YOUR APPLICATION

The best way to mitigate SQL Injection is to use PDO with parameterize SQL queries, like so.

<?php
$db = new PDO('host=localhost;dbname=test', 'user', 'pass');
$stmt = $db->prepare('SELECT * FROM tags WHERE name=?');
$stmt->execute(array($_GET['tag']));

$db = new PDO('host=localhost;dbname=test', 'user', 'pass');
$stmt = $db->prepare('SELECT * FROM tags WHERE name=:tag');
$stmt->bindParam(':tag', $_GET['tag'], PDO::PARAM_STR);
$stmt->execute();

See the PDO documentation here.