SQLi – PHP Secure Coding

Method #1
Escaping special characters in a string for use in an SQL statement

<?php
  $name = mysql_real_escape_string( $_POST[‘name’] );
  $pwd  = mysql_real_escape_string( $_POST[‘pwd’] );
 
  $str_sql = "SELECT * from `tbl_users` WHERE " .
             "usr_name=’" . $name . "’ AND " .
             "usr_pwd=’" . $pwd . "’";
 
  $result = mysql_query( $str_sql ) or die ( mysql_error() );
?>

Method #2
Using prepared statements and parameterized queries:

Case #1
While connecting to database

<?php
  $pdo = new PDO('mysql:dbname=db;host=127.0.0.1;charset=utf8', 'username', 'password');

  $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>

Case #2
While retrieving data

<?php
  $stmt = $pdo->prepare('SELECT * FROM tables WHERE name = :name');
  $stmt->execute(array(':name' => $name));
  foreach ($stmt as $row) {
      echo $row[0];
  }
?>

Case #3
While inserting

<?php
   $preparedStatement = $pdo->prepare('INSERT INTO table (column) VALUES (:column)');
   $preparedStatement->execute(array(':column' => $unsafeValue));
?>

This technique can also be applied in case of update and delete.

References
http://php.net/manual/en/security.database.sql-injection.php
http://php.net/manual/en/function.mysql-real-escape-string.php
http://php.net/manual/en/book.pdo.php