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

May be interested

  • How to Create a Secure Session Management System in PHP and MySQLPhoto of How to Create a Secure Session Management System in PHP and MySQL
    this guide will show you how you can store your sessions securely in a mysql database. we will also encrypt all session data that goes into the database, which means if anyone manages to hack into the database all session data is encrypted...
  • How to Create a Table in MySQLPhoto of How to Create a Table in MySQL
    tables make up the structure of your mysql databases. tables contain the information that is entered into the database, and can be created to suit basically any data storage need. creating a table only takes a few minutes, especially if...
  • How to Install the MySQL Database Server on Your Windows PCPhoto of How to Install the MySQL Database Server on Your Windows PC
    this wikihow teaches you how to install the mysql server program on a windows 10 computer. in order to install mysql on a windows computer, you must first have python 2.7 (not python 3+) installed. open the python download page. go to...
  • How to Send Sql Queries to Mysql from the Command LinePhoto of How to Send Sql Queries to Mysql from the Command Line
    a simple text-based program called mysql should have been part of your mysql installation. it lets you send sql queries directly to the mysql server and output the results in text format. it is a quick and easy way to test your mysql...
  • 5 best SQL query optimization software to speed up MySQLPhoto of 5 best SQL query optimization software to speed up MySQL
    the sql query optimizer analyzes many options for a given query, estimates the cost of each of these options, and finally, selects the lowest cost option.
  • TRUNCATE TABLE statement in SQLPhoto of TRUNCATE TABLE statement in SQL
    the truncate table statement is used to completely delete records from an existing table in sql.