In this tutorial, we will discuss about SQL injection issue, explaining what it is, describe SQLi types, and showing ways to prevent SQL injection in PHP.
What is SQL Injection (SQLi)
As injection word define that something insert, SQL injection is a technique that mostly used by hackers to inject sql query in your web application to access your database.
Basically SQL injection might be occur at the time when you ask user for input. For example you have an form where you ask user to input username/id, then attacker pass an SQL statement in input field instead of username/id.
In many cases, an hacker can modify and delete your data, which impact your application behavior. A successful SQL injection can lead to unauthorized access of your application data, like access credit card details, user information and passwords. So this will become worst situation for web application.
Types of SQL Injection (SQLi)
SQL injection is classified into three major categories.
1. In Band SQL injection:
To understand In-band SQL injection, you have to check its both types. One is Error Based SQLi and second is UNION based SQLi.
Error based SQLi
In Error based SQLi, target to get information about website database structure. On your website weak urls, where hackers just put ‘ or %27 after the parameter number or string and execute it.
Like that http://example.com/productdetails.php?id=1′
After that a SQL error shows, via this they can understand your database structure and there is a chance that your website is get injectible.
UNION Based SQL injection
In UNION Based SQL injection, hackers try to combine two or more SELECT sql queries and get single result. Which returned as a part of HTTP response.
2. Inferential SQLi (Blind SQLi):
This is also just as dangerous an any other SQL injection types. In this attacker did not get direct response as in-band. That why its called “blind SQL injection”.
Basically attackers try to reconstruct your database structure by sending payloads. This is divided in two types.
Time-based Blind SQLi
In this type, hackers sending an SQL query, which force database to wait for certain time before result showing. The respond time will indicate that the query result is True or False. On the basis of this Boolean result, it allow an hacker to conclude.
Boolean based Blind SQLi
In this technique attacker sending an SQL query, which forces the application to return different response, whether the query return a True or False response. On the basis of response, the http response content will change, or remain same.
3. Out-of-band SQLi
OOb SQL injection is a advanced SQLi, it can be capturing by either HTTP protocol or DNS. Basically it depend upon the function available in your database system like file operation function load_file() or connection make function DBMS_LDAP.INIT, UTL_HTTP.request.
Ways to Prevent SQL Injection in PHP
User input data being the main vector for such SQL injection. We are going to explain many ways to prevent SQLi injection in PHP like validate input data, use parameterized queries and escaping function.
Input Validation:
Input validation doesn’t mean that you validate user-entered input or not, it’s is something more. Validate input data type before executing the SQL database query. Like in the below example, we need to check whether the input ‘id’ value is numeric or not.
|
1 2 3 4 5 6 |
$id = $_GET['id']; if ( is_numeric($id) == true) { // execute SQL query here } |
Parameterized Query:
Using Parameterized queries means of pre-compiling a SQL statement before executing it. This coding style helps database to distinguish the code and the input data.
User entered input data automatically quoted and will not be a reason for data changed. The best approach is used to PDO data objects.
|
1 2 3 4 5 6 7 8 9 10 11 |
<?php $id = $_GET['id']; //preparing the query $sql = "SELECT email FROM students WHERE id = :id"; $query = $conn->prepare($sql); $query->bindParam(':id', $id); $query->execute(); //getting the response $response = $query->rowCount(); print($response); ?> |
In WordPress
|
1 2 3 |
$sql_query = $wpdb->prepare( "SELECT * FROM users WHERE `first_name` = %s", $_GET['first_name']); $response = $wpdb->get_results( $sql_query ); |
If Using PostgreSQL
|
1 2 3 |
$result = pg_prepare($dbconn, "sql_query", 'SELECT * FROM users WHERE first_name = $1'); $result = pg_execute($dbconn, "sql_query", array($_GET['first_name'])); |
Using Escaping Function:
Use mysql_real_escape_string() character-escaping function for user entered input, this will make sure that DBMS easily identify the SQL statement provided by the coder.
|
1 2 3 4 5 |
$db_conn = mysqli_connect("localhost", "dbuser", "dbpassword", "database_name"); $username = mysqli_real_escape_string($db_conn, $_POST['username']); $password = mysqli_real_escape_string($db_conn , $_POST['password']); $query = "SELECT * FROM students WHERE username = '" . $username. "' AND password = '" . $password . "'"; |
Basically, every method is own way to access the database. Via using above mentioned tips you can prevent the SQL injection in PHP. Just look for it and try it.
