How to prevent SQL injection in PHP

This article will introduce you to SQL injection in PHP and how to prevent SQL injection using PHP-MySQLi and PHP-PDO drivers. Let’s look at the specific content below.

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.How to prevent SQL injection in PHP?account_id=102 to pull details from the database.

For example, the code for get_account_details.How to prevent SQL injection in PHP is as follows.

$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.

We 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’s 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 PHP-PDO driver prepare statement to avoid these types of SQL injection.

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

This article has ended here. For more exciting content, you can pay attention to other related columns of the PHP Chinese website Tutorial! ! !

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!