This tutorial will help you to prevent SQL inject in PHP. In this tutorial first, check a basic example of SQL injection process. How can users steal data from your website using SQL injection? This tutorial also includes methods to prevent SQL injection using PHP-MySQLi and PHP-PDO drivers.
Simple SQL Injection Example
For example, A have a website for Bank. You have provided a web interface to bank customers to view their account number and balance. Your Bank website uses URL like http://example.com/get_account_details.php?account_id=102 to fetch details from the database.
For example get_account_details.php have code something like below.
1 2 | $accountId = $_GET['account_id']; $query = "SELECT accountNumber, balance FROM accounts WHERE accountId = $accountId"; |
Customers accountId is passed through query string as account_id. Like above Url, if a user’s account id 102 and it passed in the query string. The Php script will create a query like below.
1 | $query = "SELECT accountNumber, balance FROM accounts WHERE accountId = 102"; |
The accountNumber and balance details are fetched for accountId 102 and provided to customers as showing in above screenshot.
Let’s, assume another scenario – An over smart customer has passed account_id as 0 OR 1=1 in query string. What will be happened now? The PHP script will create a query like below and executed on the database.
1 | $query = "SELECT accountNumber, balance FROM accounts WHERE accountId = 0 OR 1=1"; |
Look at the query created by script and result returned by the database. You can see that this query returned all accounts number and the available balance.
This is called SQL Injection. This is the simple scenario, there can be a number of methods to do SQL injections. Below tutorial will help you to prevent SQL injection using PHP MySQLi driver and PHP PDO driver.
#1. Using PHP-MySQLi Driver
You can use PHP-MySQLi driver prepared statements to avoid these type of SQL injections. Use below PHP code which will prevent SQL injection.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | $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(); } |
#2. Using PHP-PDO Driver
You can use PHP-PDO driver prepare statements to avoid these type of SQL injections. Use below PHP code which will resolve above SQL injections.
1 2 3 4 5 6 7 8 9 10 11 12 | $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(); } |
3 Comments
I am sure this will come quite handy. It is definitely necessary to review your code and follow PHP coding standards to prevent leaving any SQL injection vulnerabilities in your code. Most commonly, contact forms have SQL injection vulnerabilities in their code, like an example given here: https://www.cloudways.com/blog/protect-php-website-sql-injection/
In the example mentioned in the code using following code can prevent sql injection attacks
$userName=BlockSQLInjection($_POST[‘userName’]);
$password=BlockSQLInjection($_POST[‘password’]);
Actually, the solution here is much more simple:
$accountId = (int)$_GET[‘account_id’]; // Added strict type conversion
This will guarantee $accountId as a number no matter what
Thanks Alex for sort solution.