comment 0

How to prevent SQL-injection on PHP/MySQL?

This is a very old vulnerability, but it surprises me how many developer doesn’t know it exists or doesn’t even bother coding to prevent it.

How does SQL-injection work?

SQL injection is simply just SQL statement not properly formated. The root cause of this is the SQL statement mixed with malicious data.

See example below.

// data
$data = 'mike';
// SQL statement
$sql = 'SELECT * FROM people WHERE name="'.$data.'"';

The example above doesn’t have any problems at all. But what if we modify $data with malicious SQL code?

// malicious data
$data = '"; DROP table people; --';
// SQL statement
$sql = 'SELECT * FROM people WHERE name="'.$data.'"';

The final query statement if you mix this will be:

SELECT * FROM people WHERE name=""; DROP table people; --"

This particular statement will immediately drop the table people once executed.


There are couple of solutions to prevent SQL-injection in PHP.

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 queries.

You can select from this two options to achieve this.

First is by using PDO. Please see example below

// prepare statement
$stmt = $pdo->prepare('SELECT * FROM `table` WHERE `column` = :value');
// execute with parameters
$stmt->execute(array('value' => $value));

// loop the result
foreach ($stmt as $row) {
// do something with $row

Second is by using mysqli

// prepare statement
$stmt = $dbConnection->prepare('SELECT * FROM `table` WHERE `column` = ?');
// bind parameters
$stmt->bind_param('s', $value);
// execute
// get results
$result = $stmt->get_result();
// loop results
while ($row = $result->fetch_assoc()) {
// do something with $row

These two options are the safest solution. Some of you maybe using addslashes or mysql_real_escape_string. Escaping is not that safe, though it might help on most cases. Please read and

Leave a Reply

Your email address will not be published. Required fields are marked *