HomeProgramSQL

How to Prevent SQL Injection in PHP

Part 1 of 2:

Understanding SQL Injection

  1. SQL Injection is a type of vulnerability in applications that use a SQL database. The vulnerability arises when a user input is used in a SQL Statement: 
    $name = $_GET['username']; $query = "SELECT password FROM tbl_user WHERE name = '$name' ";
  2. The value a user enters into the URL variable username will be assigned to the variable $name. It's then placed directly into the SQL statement, making it possible for the user to edit the SQL statement. 
    $name = "admin' OR 1=1 -- "; $query = "SELECT password FROM tbl_user WHERE name = '$name' ";
  3. The SQL database will then receive the SQL statement as follows: 
    SELECT password FROM tbl_users WHERE name = 'admin' OR 1=1 -- '
    1. This is valid SQL, but instead of returning one password for the user, the statement will return all the passwords in the table tbl_user. This is not something you want in your web applications.
Part 2 of 2:

Using mySQLi to Create Prepared Statements

  1. How to Prevent SQL Injection in PHP Picture 1
    Create the mySQLi SELECT Query. Use the code below to SELECT data from a table using mySQLi Prepared Statements. 
    $name = $_GET['username']; if ($stmt = $mysqli->prepare("SELECT password FROM tbl_users WHERE name=?")) { // Bind a variable to the parameter as a string.  $stmt->bind_param("s", $name); // Execute the statement. $stmt->execute(); // Get the variables from the query. $stmt->bind_result($pass); // Fetch the data. $stmt->fetch(); // Display the data. printf("Password for user %s is %sn", $name, $pass); // Close the prepared statement. $stmt->close(); }
    1. Note: The variable $mysqli is the mySQLi Connection Object.
  2. How to Prevent SQL Injection in PHP Picture 2
    Create the mySQLi INSERT Query. Use the code below to INSERT data into a table using mySQLi Prepared Statements. 
    $name = $_GET['username']; $password = $_GET['password']; if ($stmt = $mysqli->prepare("INSERT INTO tbl_users (name, password) VALUES (?, ?)")) { // Bind the variables to the parameter as strings.  $stmt->bind_param("ss", $name, $password); // Execute the statement. $stmt->execute(); // Close the prepared statement. $stmt->close(); }
    1. Note: The variable $mysqli is the mySQLi Connection Object.
  3. How to Prevent SQL Injection in PHP Picture 3
    Create the mySQLi UPDATE Query. Use the code below to UPDATE data in a table using mySQLi Prepared Statements. 
    $name = $_GET['username']; $password = $_GET['password']; if ($stmt = $mysqli->prepare("UPDATE tbl_users SET password = ? WHERE name = ?")) { // Bind the variables to the parameter as strings.  $stmt->bind_param("ss", $password, $name); // Execute the statement. $stmt->execute(); // Close the prepared statement. $stmt->close(); }
    1. Note: The variable $mysqli is the mySQLi Connection Object.
  4. How to Prevent SQL Injection in PHP Picture 4
    Create the mySQLi DELETE Query. The below script is how to DELETE data from a table using mySQLi Prepared Statements. 
    $name = $_GET['username']; $password = $_GET['password']; if ($stmt = $mysqli->prepare("DELETE FROM tbl_users WHERE name = ?")) { // Bind the variable to the parameter as a string.  $stmt->bind_param("s", $name); // Execute the statement. $stmt->execute(); // Close the prepared statement. $stmt->close(); }
    1. Note: The variable $mysqli is the mySQLi Connection Object.
5 ★ | 2 Vote
passwordstatement

You should read it

May be interested

SQL