PHP - prevent SQL injection in PHP

What is SQL injection ?

 Many web developers are unaware of how SQL queries can be tampered with, and assume that an SQL query is a trusted command. It means that SQL queries are able to circumvent access controls, thereby bypassing standard authentication and authorization checks, and sometimes SQL queries even may allow access to host operating system level commands.

 Direct SQL Command Injection is a technique where an attacker creates or alters existing SQL commands to expose hidden data, or to override valuable ones, or even to execute dangerous system level commands on the database host. This is accomplished by the application taking user input and combining it with static parameters to build an SQL query. SQL Injection flaws are introduced when software developers create dynamic database queries that include user supplied input.

To avoid SQL injection flaws is simple. Developers need to either: a) stop writing dynamic queries; and/or b) prevent user supplied input which contains malicious SQL from affecting the logic of the executed query. This article provides a set of simple techniques for preventing SQL Injection vulnerabilities by avoiding these two problems. These techniques can be used with practically any kind of programming language with any type of database. There are other types of databases, like XML databases, which can have similar problems (e.g., XPath and XQuery injection) and these techniques can be used to protect them as well.

Primary Defenses:

 #1: Use of Prepared Statements (Parameterized Queries)
 #2: Escaping all User Supplied Input

 Here will show simple example of unsafe query :

 $SQL = "SELECT * FROM members WHERE email = '$email' ";

The SQL this time has a WHERE clause added. The WHERE clause is used when you want to limit the results to only records that you need. After the word "WHERE", you type a column name from your database (email, in our case). You then have an equals sign, followed by the value you want to check. The value we want to check is coming from the variable called $email. This is surrounded with single quotes. When an email address is entered in the text box on our form, this value goes straight into the variable without any checks.

When you type that extra single quote on the end, that will be added to the SQL. This is then run on the database. Because it's a stray single quote, you'll get a syntax error. It's this syntax error that an attacker is looking for. Next, the attacker will try to add some SQL to yours. Try this. In the email address textbox, type the following. Type it exactly as it is, with the single quotes:

hi' OR 'x'='x

When you click the Submit button, you should find that there are no errors, and that the username, password and email address are printed out! The attacker is trying to find out whether or not the SQL can be manipulated. If the answer is yes, further attacks will be launched. Can the table and field names be guessed? Can a username and password be guessed? It's this kind of attack that you want to thwart. Try this last one. Enter the following into the email address box:

' OR ''='

Using defense method #1

Php introduce many extensions to communicate with your database . Use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

 Using PDO:

$stmt = $pdo->prepare('SELECT * FROM Client WHERE name = :name');
$stmt->execute(array('name' => $name));
foreach ($stmt as $row) {
//row
}

Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared statements. An example of creating a connection

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

In the above example the error mode isn't strictly necessary, but it is advised to add it. This way the script will not stop with a Fatal Error when something goes wrong. And gives the developer the chance to catch any error(s) which are thrown as PDOExceptions. What is mandatory however is the first setAttribute() line, which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it to the MySQL server (giving a possible attacker no chance to inject malicious SQL).

Using Mysqli

$stmt = $dbConnection->prepare('SELECT * FROM ClientsWHERE name = ?');
$stmt->bind_param('s', $name);

$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // row

}

Using defense method #2

You've got two options - escaping the special characters in your unsafe_variable, or using a parameterized query. Both would protect you from SQL injection. The parameterized query is considered the better practice, but escaping characters in your variable will require fewer changes. We'll do the simpler string escaping one first.

$unsafe_variable = $_POST["user-input"];
$safe_variable = mysql_real_escape_string($unsafe_variable);

mysql_query("INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

Good Luck