How to Prevent SQL Injection in PHP

Part 1 of 2:

Understanding SQL Injection

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' "; 
```
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' "; 
```
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

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(); }

Note: The variable $mysqli is the mySQLi Connection Object.

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(); }

Note: The variable $mysqli is the mySQLi Connection Object.

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(); }

Note: The variable $mysqli is the mySQLi Connection Object.

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(); }