PHP,Joomla,WordPress & Linux based server management, error resolutions and fixing problems.

Saturday, October 6, 2012

Best way to Avoid MySQL injection in PHP

SQL Injection, a commonly method used by majority of hackers and which is the cause for about 70% of the hack's all over the cyber warfare. Mostly hacker uses this (mysql injection) vulnerability to exploit the website and gain access to the server thus through Admin panel or obtain the sensitive information including Credit Cards etc as well.
How to prevent mysql injection in PHP?
SQL Injections are Common nowadays!

So here i am going to explain you the best practices you should do while writing code!
First of all keep it in mind, never ever trust a user's input, you are on to your own! So it is the very first thing to keep in mind that whatever a user sends, intentionally or unintentionally, if it is not properly sanitized and exploits a vulnerability which could be possibly a MySQL injection or even a XSS attack, could let hackers give a way to intrude.

Bullet Points:

  • When you are going to embed strings in HTML for example when you "print" or when you "echo"  you should default to escape the string using htmlspecialchars(); .
  • Never trust user's input, always sanitize the user input by first sanitizing it properly and before performing any SQL query, this can be achieved by mysql_real_escape_string($parameter).Where $parameter is the input you are about to filter.
  • Disable Magic Quotes.
  • Use prepared statements and parameterized queries, always filter the user inputs properly,thoroughly and strictly.
  • Don't through mysql_error() to the user,don't even let them know something happened out there,most of the people use it like, die(mysql_error()); . Instead of just throwing this error straight away to the user you can write your custom messages, prepare some log files or even create a mail() function so you get notified whenever someone tries to pull something off the routine.
  • Log,Log,Log, always log the errors and warning messages, turn off error reporting on live websites and enable only when debugging or during development process or testing phases.
  • Be Self-Aware, never give up, always try up-to-date security precautions, create difficult random passwords for database, encrypt all the user's information in the database and set a value of 666 to the files which contains configuration for mySQL access.
  • Keep your server patched and updated, use only latest software's, if you are on a shared hosting server, and find something which is not fine, notify the Administrator.
  • If you are using some software's like WordPress,Joomla,Drupal or any other free and open-source or even other software's,keep them updates, most of these products have built-in update notifiers in the Admin panel's and most of the time they update themselves with a single click.

Best way to Avoid MySQL Injections.

You basically have two options to achieve this:
  1. Using PDO:
    $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
    $stmt->execute(array(':name' => $name));
    foreach ($stmt as $row) {
        // do something with $row
  2. Using mysqli:
    $stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
    $stmt->bind_param('s', $name);
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        // do something with $row


Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared statements. An example of creating a connection using 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 will not stop with a Fatal Error when something goes wrong. And gives the developer the chance to catch any error(s) (which are throwed as PDOExceptions.
What is mandatory however is the setAttribute() line, which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it the the MySQL server (giving a possible attacker no chance to inject malicious SQL).
Although you can set the charset in the options of the constructor it's important to note that 'older' versions of PHP (< 5.3.6) silently ignored the charset parameter in the DSN.


What happens is that the SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :name in the example above) you tell the database engine where you want to filter on. Then when you call execute the prepared statement is combined with the parameter values you specify.
The important thing here is that the parameter values are combined with the compiled statement, not a SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters you limit the risk of ending up with something you didn't intend. Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course). In the example above, 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 will not end up with an empty table.
Another benefit with using prepared statements is that if you execute the same statement many times in the same session it will only be parsed and compiled once, giving you some speed gains.
Oh, and since you asked about how 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));

Any comments are welcome :) Share your practices with me as well.

♥ Happy Coding ♥
, , ,