Prevent Sql Injection in PHP

Hi there! Today In this post I will mention few of the methods which is usually used in php application to prevent SQL injections.
It is said, “Never Trust Your User’s Input”. Preventing and cleaning user input in your php code is always a good and recommended practice.

1) Using mysql_real_escape_string() function:

Use if mysql_real_escape_string() is always a good practice into your code, but we can make it even better, consider following Code:

A BAD PRACTICE:

Example:

<?php

$uname = $_POST['username'];
$query = "SELECT password FROM tbl_user WHERE user_name = $uname "; // very hazardous, your system may prone to SQL injection
$res = mysql_query($query);

?>

A GOOD PRACTICE:
Example I:

<?php

$uname = mysql_real_escape_string($_POST['username']); //use of mysql_real_escape_string()
$query = "SELECT password FROM tbl_user WHERE user_name = '$uname' "; // use of single quotes for STRING $uname
$res = mysql_query($query);

?>

Example II:

<?php

$usrid = mysql_real_escape_string($_POST['userid']); //use of mysql_real_escape_string()
$usrid = (int)$usrid ; // cast id as int
$query = "SELECT username, password FROM tbl_user WHERE id = $usrid ";
$res = mysql_query($query);

?>

Also, if possible set your default charset to UTF8 using mysql_set_charset() function, it will be an another good approach.

2) PDO Prepared statements:

Following code will be quiet safe for pdo:


<?php
$stmt = $dbh->prepare("SELECT * FROM tbl_user where name = ?");
if ($stmt->execute(array($_POST['name']))) {
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
}
?>

3) Using mysqli prepared statement:

<?php

$city = $_POST['cname'];
/* create a prepared statement */
if ($stmt = $mysqli->prepare("SELECT * FROM tbl_city WHERE cty_name=?")) {

    /* bind parameters for markers */
    $stmt->bind_param("s", $city);

    /* execute query */
    $stmt->execute();

    /* bind result variables */
    $stmt->bind_result($district);
    $stmt->fetch();
    $stmt->close();
}

/* close connection */
$mysqli->close();
?>