How to prevent SQL injection

A SQL injection attack consists of insertion or “injection” of a SQL query via the input data from the client to the application i.e web browser. A successful SQL injection exploit can read sensitive data from the database, modify database data , execute administration operations on the database.

Suppose, We are visiting a page to view a student information with id = 11 , the page URL is something like student.php?id=11

Behind the scene this runs a query like this

SELECT * FROM students WHERE id = $id

If we hit a URL student.php?id=11 OR 1=1 this will run the query because this will run a query like this

SELECT * FROM students WHERE id = 11 OR 1 = 1

and this statement is TRUE and this will be executed in the database. Hackers will be able to inject malicious code and run it on the database.

To prevent SQL injection, we will have to sanitize the variable received from the user. There are may different ways to do this. One of the easiest ways is to use prepare method with PDO in PHP.

$stmt = $pdo->prepare('SELECT * FROM students WHERE id = :id');
$stmt->bindParam(':id', id);

$stmt->execute();

prepare method sanitizes the query and SQL injection does not work. It’s a very simple trick, isn’t it ?

Remember , Never assign unnecessary permission to a MySQL user. For better security you can install WAF(Web Application Firewall ) and configure it correctly, this will prevent the web app from different types of attacks.