This document explains how to prevent, in several ways, SQLi attack. We can patch this lines as examples below, if we found vulnerable PHP sentences during attack-defense CTF.

Of course, it depends on the way they use PHP and there’s no warranty to secure the code , but it’s an approach about that.

Unsafe example:

$vuln_var = $_POST['userinput'];
 mysql_query("INSERT INTO table (column) VALUES ('" . $vuln_var . "')");

This is unsafe because the user can do value’); DROP TABLE table;– so the query will be:

INSERT INTO table (column) VALUES('`**`value'); DROP TABLE table;--`**`')

Solution: use prepared statements and parameterized queries. Our database parse separately the parameters sent to.

1. Using PDO

$db = new PDO('mysql:host=localhost;dbname=', '', 'PASSWORD');
 $sql = $pdo->prepare('SELECT * FROM table WHERE column = :column');
 $sql->execute(array('column' => $column_value));

2. Using MySQLi
 $sql = $dbConnection->prepare('SELECT * FROM table WHERE column = ?');
 $sql>bind_param('s', $name);
 $sql->execute();

An important thing when we use PDO is that we must set an attribute that force to not use emulate in prepared statements because prepared statements are not used by default (!). To fix it, add this lines:
// use real prepared statements

 $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

In the unsafe example above we are using mysql extension instead mysqli or pdo, where we can minimize impact of the SQLi attack using mysql_real_escape_string() function.

 $vuln_var = $_POST['userinput'];
 $safe_min = mysql_real_escape_string($_POST["userinput"]);
 mysql_query("INSERT INTO table (column) VALUES ('" . $safe_min . "')");

References:

SQL Injection Cheat Sheet
SQL Injection
Information security
Security Principles
Data validation