How i prevent SQL Injection in my PHP code

#php #mysql #programming #webdev

SQL injection is a code injection technique used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution. This is what Wikipedia can say about SQL injection.

My small definition to SQL injection is an old school technique for attackers to inject a query inside a query to perform unauthorized action against database usually SQL based ones.

Today, I'm not going to give such examples of SQL injection, but one example could be enough to understand how can a developer protect his application from SQL injection.

Normally this will not take too long, because there only one answer to do that,
you should use prepared statements.

Prepared statements are send to and parsed by database server separately from any parameters, This way it is impossible for an attacker to inject malicious SQL.

Basically, SQL injection uses your parameters to in inject malicious SQL queries, as example, let's say you are working on a library website, and you have to get each book by it id to show later in your page, so, maybe you're having something like http://localhost/library/book.php?id=1, and then you fetch your book as simple as that:

<?php

$id = $_GET['id'];

$query = "SELECT * FROM books WHERE id = ".$id ;
//in normal case your query became "SELECT * FROM books WHERE id = 1"
// execute your query and get data

So, you parameter id leaves a security hole in your website, an attackers can easily add SQL query to your parameter id just like that:
localhost/book.php?id=1; UPDATE users SET password = 'anass' WHERE idUser = 1
Then your code:

<?php

$id = $_GET['id'];

$query = "SELECT * FROM books WHERE id = ".$id ;
//this case your query became "SELECT * FROM books WHERE id = 1; UPDATE users SET password = 'anass' WHERE idUser = 1"

This will execute two queries at time, result in changing your administrator password in your application, normally parameters are helping as to do so, the idea behind preventing SQL injection is to separate our query from parameters, so we can tell our database engine that we wanting to execute such query using these parameters.
For sure your website is not ready for production, just leave it in localhost and keep reading.

How we can tell database engine, what is the query, and what are parameters,

first,
Let's connect to our MySQL database,
For sure, commanded way is to use PDO, because if you want to switch to any database engine, we won't rewrite every thing,
Then, should prepared statement, and pass the skeleton of our query:

//use our database details to connect
$pdo = new PDO('mysql:dbname=db;host=localhost;charset=utf8', 'root', '1234');

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

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

The first line, is to connect to our MySQL database named db and hosted in our locale environment localhost, username is root and password 1234.
The second line is the most important, tells PDO to use real prepared statements rather than emulated by PHP, if it set to true preparing will be done by PHP and this is not our choice.
Finally, the third line set error mode to exceptions, PDO will throw PDOException if there is an error.

Now move on to create our first prepared query,
from the previous example, we need to get a book by it id,

<?php

$query = $pdo->prepare("SELECT * FROM books WHERE id = ?";

Simple enough
Alright

We need now to pass the parameter, by using bindParam method,

$query->bindParam($id);

Then use execute to execute our query:

$resultes = $query->execute();

Hope this clarify how prepared statements work, and encourage you to use it,
If you have any suggestions let me know in comments, and next time i will show you how to make your own secure Query Builder class to use it in any of your projects.