DEV Community

loading...

How i prevent SQL Injection in my PHP code

Anass Boutaline
Full-stack Web Developer, Software engineer
Updated on ・3 min read

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
Enter fullscreen mode Exit fullscreen mode

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"

Enter fullscreen mode Exit fullscreen mode

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);

Enter fullscreen mode Exit fullscreen mode

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 = ?";

Enter fullscreen mode Exit fullscreen mode

Simple enough
Alright

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

$query->bindParam($id);

Enter fullscreen mode Exit fullscreen mode

Then use execute to execute our query:

$resultes = $query->execute();
Enter fullscreen mode Exit fullscreen mode

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.

Discussion (2)

Collapse
vishalraj82 profile image
Vishal Raj

@mjprogramation For those who don't use PDO (very unfortunate) can use msyqli_real_escape_string

Collapse
butalin profile image
Anass Boutaline Author

Mysqli also has prepare methode, they can do something like $conn->prepare("my prepared query?")