Follow

Welcome to TechYari!

Tutorials on webdesign, web development and more...

Prevent SQL Injection in PHP with PDO and MySqli



By  Vipin Dubey     11/15/2014    Labels:,,, 
Security is very essential to any web application. SQL injection is a technique where attacker tries to execute malicious scripts on application's database using several security flaws. The easiest way to do this is sending the SQL script via get or post request that do not have proper validation and security filtering on server.In this post we will look at how to prevent sql injection using two main techniques.

how to prevent sql injection in php


Lets assume we have a login page having two form fields, one for username and another for password. We will be using POST method to submit this data to the server. When user clicks on Sign in button all the data will be submitted to server and authenticate.php script will take care of validation.

Below is the HTML code for the typical login form:


<form action="authenticate.php" method="POST">  
    <input type="text" name="username" placeholder="Username" required autofocus/><br/>
    <input type="password" name="password" placeholder="Password" required/><br/>
    <input type="submit" value="Sign in"/>
</form>


There are several ways you can prevent SQL injection while submitting the data from login form to server:

1. Using PDO
2. Using MySqli
3. Escaping special characters

Let's talk about each one of them :

Prepared Statements with PDO and MySqli

You can use PDO to achieve this. Using PDO is considered the best option however it requires the PDO extension to be enabled on you server. Most of the servers running PHP 5.3 or later have this option enabled and if not you can check with you hosting provider and ask them to enable the extension.

Using PDO : Below is an example of using PDO to prevent SQL injection.



 <?
 // define database related variables
   $database = 'your_database_here';
   $host = 'localhost';
   $user = 'your_db_username';
   $pass = 'your_db_password';

   // try to conncet to database
   $dbh = new PDO("mysql:dbname={$database};host={$host};port={3306}", $user, $pass);

   if(!$dbh){

      echo "unable to connect to database";
   }

    // Create placeholder variables
 $username = "";
 $password = "";
 
 // Check if the values are set and assign to variables
 if(isset($_POST['username'])){
  $username = $_POST['username'];
 }
 if (isset($_POST['password'])) {
  $password = $_POST['password'];

 }
 
 // Check if user has entered some values
 if($username!="" && $password!=""){

      // Create query
   $q = 'SELECT * FROM users WHERE username=:username AND password=:password';
      // Prepare query using PDO
   $query = $dbh->prepare($q);
      // Execute query
   $query->execute(array(':username' => $username, ':password' => $password));


   if($query->rowCount() == 0){
    
    // Send back to login page with an error message

   }else{
    
    // Add user to session and take him to home page.
   }
 }else{

   // Back to login page with error message. 

 }

   

  ?> 

Using MySqli : Below is an example of using MySqli to prevent SQL injection.




 <?
 // define database related variables
   $database = 'your_database_here';
   $host = 'localhost';
   $user = 'your_db_username';
   $pass = 'your_db_password';

  $mysqli = new mysqli($host, $user, $pass, $database);
  
  if ($mysqli->connect_errno) {
    echo "Failed to connect to database: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }

    // Create placeholder variables
 $username = "";
 $password = "";
 
 // Check if the values are set and assign to variables
 if(isset($_POST['username'])){
  $username = $_POST['username'];
 }
 if (isset($_POST['password'])) {
  $password = $_POST['password'];

 }
 
 // Check if user has entered some values
 if($username!="" && $password!=""){
            
            // Prepare statement
   $stmt = $mysqli->prepare('SELECT * FROM users WHERE username=? AND password=?');
   if (!$stmt->bind_param("i", $username,$password)) {
        echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
   }

   // Execute statement
   if (!$stmt->execute()) {
        echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
   }
       
       // Get result 
            $result = $stmt->get_result(); // This method requies mysqlnd drives. pleas eamke sure it is available or else you will get errors.
            
            // Check number of rows returned from database.
            if($result->num_rows > 0){

             //fetch the data and login user

            }else{

             // Back to login page with error as username and password does not match.
            }

  }else{

         // Back to login page with error message. 
  }

 
  ?> 

We have seen both ways of submitting data which can save you from sql injection. We also talked about escaping special charatacters which can be used to prevent sql injection as well. Below is an example of how you can escape special characters while getting data from our login page.

Using  mysql_real_escape_string to escape special characters



  // Create placeholder variables
 $username = "";
 $password = "";
 
 // Check if the values are set and assign to variables
 if(isset($_POST['username'])){
  // check to see if there are any special characters, remove them and assign safe value to username variable
  $username = mysql_real_escape_string($_POST['username']);
 }
 if (isset($_POST['password'])) {
  // check to see if there are any special characters, remove them and assign safe value to password variable
  $password = mysql_real_escape_string($_POST['password']);

 }

That's all for today. If you have any queries or suggestions feel free to comment below. Cheers :)

About Vipin Dubey

A web developer, blogger, amateur designer and an open source enthusiast.

No comments:

Post a Comment


Contact Form

Name

Email *

Message *

Labels

Follow TechYari on

Translate

About TechYari

Tech Yari is a Technical Blog featuring articles on Web Development, Web Design, Search Engine Optimization, Social Media Marketting and Programming.