AVOID SQL INJECTION IN PHP With MySQLi and PREPARED Statement

To avoid sql injection in php with MySQLi Prepared statement.

prepared statements in mysqli can easily help to prevent sql injection in php.
There are basically three reasons why you should seriously consider writing prepared statements to execute your queries.

             I.      Prepared statements are more secure.

          II.      Prepared statements have better performance.

       III.      Prepared statements are more convenient to write.

Here is little introduction to mysqli queries and how to use prepared statements use

---

First let us see how to connect to database using MySQLi 

/*    Create a new mysqli object with database connection parameters   */

       $mysqli = new mysql('localhost', 'username', 'password', 'db');

       if(mysqli_connect_errno())

      {

            echo "Connection Failed: " . mysqli_connect_errno();

            exit();

                  } 

---

Now let us see Prepared Statement Example

/* Create a prepared statement */

     if($stmt = $mysqli -> prepare("SELECT priv FROM testUsers WHERE username=?

     AND password=?")) {

 /* Bind parameters  s - string, b - boolean, i - int, etc */

     $stmt -> bind_param("ss", $user, $pass);

 /* Execute it */

     $stmt -> execute();

 /* Bind results */

     $stmt -> bind_result($result);

 /* Fetch the value */

     $stmt -> fetch();

      echo $user . "'s level of priviledges is " . $result;

 /* Close statement */

      $stmt -> close();

             }

---

Here first we need to create stmt object before using the prepared statement

// Create statement object

   $stmt = $db->stmt_init();

And then call prepared statement using that stmt object

// Create Prepared statement

    $stmt->prepare();

---

Stmt prepared statement can directly call at time of prepare statement

// Create prepared statement

$stmt = $db->prepare();

---

In prepared statement query, need to pass the ? instead of passing the values. as shown in below ex.

               SELECT column1, column2 FROM table WHERE value=?

---

Now we have to bind the values to that “?” using following bind_param

in bind_param you need to pass value types at first in double quotes.

ex. $stmt->bind_param("s",$name);

$stmt->bind_param("ss",$name,$pass);

different value types are :

i    for integer value

s    for string value

b    for blob values

d    for double values

---

Now after binding the result need to execute the query

 /* Execute it */

     $stmt -> execute();

---

To fetch the result u need to call fetch

 /* Fetch the value */

     $stmt -> fetch();

---

After executing query as you are retrieving the result need to store the result.

 /* Store the Value */

     $stmt -> store_result();

Storing the result means to store the result in memory for retrieval.

---

After fetching the result need to free the memory

 /* Free the memory */

     $stmt -> free_result();

---

And need to close the statement

 /* Close stmt statement */

      $stmt -> close();

---

---

Prepared Statement Examples


Insert Example

$today = date('Y-m-d h:i:s');      // To create date time stamp
if($stmt1 = $db->prepare("INSERT INTO feedback VALUES(?, ?, ?, ?, ?, ?)"))
{
$null = NULL;
$stmt1->bind_param("isssss",$null,$name,$email,$sub,$comment,$today);
$stmt1->execute();
                        $stmt1->close();
}

Explanation: Prepared statement Insert query is passed and the values which are to be passed are marked with place holder sign. In bind_param() first we have passed the bind type as a string. Then execute it using execute(); Afte execution close the statement.

Select Example

                $searchJob = "SELECT * FROM jobpost WHERE `JobPost_Id` = ?";
                if($stmt1=$db->prepare($searchJob))
                {
                        $stmt1->bind_param("i",$jobid);
                 $stmt1->execute();
                 $stmt1->store_result();
                 $stmt1->bind_result($jobid, $jobtitle, $vacancy, $industryid, $salary,  $description);
                $stmt1->fetch();

                }

               $stmt1->free_result();

               $stmt1->close();

Explanation: First execute the query and store the result into memory and bind that stored result into respective field and remember that all the values should be included in bind_result if you are selecting them with * in select statement the maintain order of field as in database. After binding that filed with fetch() fetch out all the data. At the end free the result so you can save memory and also don't forget to close the statement object.

Update Example

$setActive = "UPDATE employer_detail SET `status` = ? WHERE `empEmail_Id` = ?";
if($stmt1 = $db->prepare($setActive))
{
$statusActive = 1;
$empEmailId = "mukund.topiwala@gmail.com";
$stmt1->bind_param("is", $statusActive, $empEmailId );
$stmt1->execute();
$stmt1->close();
}

Explanation: Here Query stored in Variable and passed it to prepare statement. If the query structure is correct and db object is correct it can be executed. In the bind param first we have passed the bind type in string format and then bind parameter variables. Then we have executed it. At the end we have closed the statement object.