SQL Injection Prevention PHP – Full Guidelines | Hacking Impossible

SQL Injection Prevention PHP - Full Guidelines Hacking Impossible

SQL Injection Prevention PHP – Introduction

Back in another important web development fact. Use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the DB server individually from any parameters. This way it’s not possible for an attacker to inject malicious SQL.
You basically have two options to achieve this SQL Injection Prevention PHP:

2. $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
4. $stmt->execute(array('name' => $name));
6. foreach ($stmt as $row) {
7. // do something with $row

Using MySQLi (for MySQL):

9. $stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
10. $stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'
12. $stmt->execute();
14. $result = $stmt->get_result();
15. while ($row = $result->fetch_assoc()) {
16. // do something with $row

If you are connecting to a DB apart from MySQL, there’s a driver-specific second possibility that you just will confer with (e.g. pg_prepare() and pg_execute() for PostgreSQL). PDO is the universal option.

SQL Injection Prevention PHP – Setting Up The Connection

Note that once using PDO to access MySQL info real ready statements aren’t utilized by default. To fix this you need to disable the emulation of prepared statements. An example of making a connection by PDO is:

$dbConnection = new PDO('mysql:dbname=dbtest;host=;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 won’t stop with a Fatal Error once one thing goes wrong. And it offers the developer the possibility to catch any error(s) that are thrown as PDOExceptions.
What is necessary, however, is that the initial setAttribute() line, that tells PDO to disable emulated prepared statements and use real prepared statements. This makes certain the statement and therefore the values are not parsed by PHP before sending it to the MySQL server (giving an attainable assaulter no likelihood to inject malicious SQL).
Although you may set the charset among the alternatives of the constructor, it’s important to note that ‘older’ versions of PHP (< 5.3.6) silently ignored the charset parameter in the DSN. Deeper Explanation of SQL Injection Prevention PHP is below.


What happens is that the SQL statement you pass to arrange is parsed and compiled by the DB server. By specifying parameters (either a ? or a named parameter like : name within the example above) you tell the info engine wherever you would like to filter on. Then once you call execute, the prepared statement is combined with the parameter values you specify.

The vital factor here is that the parameter values are combined with the compiled statement, not an SQL string. SQL Injection Prevention PHP works by tricking the script into including malicious strings once it creates SQL to send to the DB. So by sending the particular SQL individually from the parameters, you limit the risk of ending up with something you didn’t intend. Any parameters you send once employing a prepared statement can simply be treated as strings (although the DB engine could do some improvement so parameters could end up as numbers too, of course).

In the example on top of, if the $name variable contains ‘Sarah’; DELETE FROM employees the result would simply be a search for the string “‘Sarah’; DELETE FROM employees”, and you’ll not end up with an empty table.
Another advantage of using prepared statements is that if you execute the identical statements again and again within the same session it’ll only be parsed and compiled once, giving you some speed gains.
Oh, and since you asked regarding a way to do it for an insert, here’s an example (using PDO):

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');
$preparedStatement->execute(array('column' => $unsafeValue));

Can prepared statements be used for dynamic queries?
While you’ll still use prepared statements for the query parameters, the structure of the dynamic query itself can’t be parametrized and sure query options can’t be parametrized.

SQL Injection Prevention PHP – Conclusion

For these specific situations for “SQL Injection Prevention PHP”, the most effective factor to try and do is use a whitelist filter that restricts the attainable values.

// Value whitelist
// $dir can only be 'DESC' otherwise it will be 'ASC'
if (empty($dir) || $dir !== 'DESC') {
$dir = 'ASC';

While you are reading this many of you may have been looking for learning web development but have no guidelines and do not want to go to college or some training course, for those there is the way you can learn everything on your own at home being your own teacher. To have that guideline read How to be a Full-Stack Web Developer by own self.