How to prevent sql injection in php

Simple SQL injection example

For example, A has a bank website. A web interface has been provided for bank customers to view their account numbers and balances. Your bank website uses URLs like http://example.com/get_account_details.php?account_id=102 to pull details from the database.

For example, the code for get_account_details.php looks like this:

$accountId = $_GET['account_id'];
$query = "SELECT accountNumber, balance FROM accounts WHERE accountId = $accountId";

The customer accountId is passed as account_id through the query string. Same as the Url above, if the user's account ID is 102 and it is passed in the query string. The Php script will create the query shown below.

$query = "SELECT accountNumber, balance FROM accounts WHERE accountId = 102";

accountId 102 Get the accountNumber and balance details and provide it to the customer.

Related recommendations: "php tutorial"

Let's assume another scenario where the smart customer has passed the account_id, just like the 0 OR 1=1 query string. What happens now? The PHP script will create the query shown below and execute it on the database.

$query = "SELECT accountNumber, balance FROM accounts WHERE accountId = 0 OR 1=1";

View the query created by the script and the results returned by the database. You can see that this query returns all accounts and available balances.

This is called SQL injection. This is a simple way, but there are many ways to perform SQL injection. Let's take a look at how to use the PHP MySQLi driver and PHP PDO driver to prevent SQL injection.

Using the PHP-MySQLi Driver

You can use the PHP-MySQLi Driver prepared statements to avoid these types of SQL injections.

PHP code to prevent SQL injection is as follows:

$accountId = $_GET['account_id'];  
if ($stmt = $mysqli->prepare('SELECT accountNumber, balance FROM accounts WHERE accountId = ?')) {   
    $stmt->bind_param("s", $accountId);  
    $stmt->execute();  
 $result = $stmt->get_result();  
 while ($row = $result->fetch_assoc()) {
 // do something here
 }  
    $stmt->close(); 
}

Use PHP-PDO driver

You can use the PHP-PDO driver prepare statement to avoid These types of SQL injections.

PHP’s code to solve the above SQL injection problem is as follows:

$accountId = $_GET['account_id'];  
if ($stmt = $pdo->prepare('SELECT accountNumber, balance FROM accounts WHERE accountId = :accountId')) {   
    $stmt->execute(array('name' => $name));  
 foreach ($stmt as $row) {
 // do something here
 }  
    $stmt->close(); 
}

The above is the detailed content of How to prevent sql injection in php. For more information, please follow other related articles on the PHP Chinese website!