What is SQL injection and how to prevent them in your web application
In top ten PHP Security Vulnerabilities list, SQL injection hits the number one position. In SQL Injection, an attacker executes or pass malicious SQL statements through which they can either steal your data or completely destroy it. This data is passed through the browser to your web application. It occurs mainly if your input data is not validated properly.
To understand, how unvalidated data can cause such a big problem, let’s understand this through example.
Suppose, You have written a code for login module. In login module, A user enters a username and password. Based on username and password your script allows a user to log in.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<?php // Database connection $db = new PDO('mysql:host=localhost;dbname=login','root','aba#$12^'); // Taking username and password $username = $_POST['username']; $password = $_POST['password']; $query = "select count(user_id) from user where username='".$username."' and password='".$password."'"; $result = $db->query($query); |
In this PHP code, we are taking username and password then querying for DB record . The username and password is passed through post request.
Scenario 1 :
If someone enters a valid username and password. This query will be select * from user where username=’raj’ and password=’123′. Now the query looks ok and gives the correct result.
Scenario 2:
Now let’s take an another case what’s happen if someone passes a malicious input parameter. In this script, The input parameter is not validated so it can create an issue.
Example of SQl Injection through GET Request
Let’s take another example. In many websites, The content listing is done through the parameter passed in a URL like http://www.example.com?city=1. All the content on this page is displayed based on city parameter. If the city parameter is not validated in a code then it is also susceptible to SQL injection and XSS attack.
|
1 2 3 |
$city = $_GET['city']; $query = "select * from data where city=".$city; |
How to Prevent SQL Injection In PHP
There are a couple of methods to prevent SQL Injection in PHP.
1. Always validate the input data, never trust your users. Use mysql_real_escape_string function to escape special string. As of PHP 5.4, MySql API is deprecated and removed from PHP 7.
|
1 2 |
$username = mysql_real_escape_string($_POST['username']); $pass = mysql_real_escape_string($_POST['password']); |
This method escapes a special character to prevent SQL Injection.
2. Use prepared statement and parameterized queries. It protects your web application from SQL injection as well as speed up the queries.
i) Prepared using PDO (PHP Data Object)
|
1 2 3 |
$sql = "SELECT * FROM users WHERE username= ? AND password = ? "; $query = $conn->prepare($sql); $query->execute(array($username, $password)); |
PHP PDO Tutorial to Connect MySql Database
ii) Prepared statement through MySqli
|
1 2 3 4 5 |
$sql = "SELECT * FROM users WHERE username= ? AND password = ? "; $query = $conn->prepare($sql); $query->bind_param('ss', $userame, $password); $query->execute(); |
MySqli Tutorial to Connect MySql Database
Conclusion
To prevent your application from security vulnerability, always validate user Input.
