What is SQL Injection?
SQL injection attacks are the most common security threats web developers have to face. Whenever you ask for user’s input you take a risk. You just can’t trust user’s input. Every data being entered could be dangerous if left unattended. SQL Injection is when a hacker tries to access or manipulate data being stored in the database by injecting a SQL query into a legitimate query run by the application. By using an SQL injection vulnerability, an attacker can use it to defeat the applications’ authentication system. SQL injection can also be used to add, modify or even delete the data being stored. Hacker can even steal the sensitive information of the users like email, address or even credit card numbers.
Example of SQL Injection
Let’s understand SQL Injection with an example. Let’s say you have a simple user login form that takes user name and password to decide if the user credentials being entered are correct or not. The form uses POST and variables are stored in $_POST[‘username’], $_POST[‘password’] and $_POST[‘submit’]. The PHP code that will manage the authentication will be as follows:
<?php
$username = $_POST[‘username’];
$userpass = $_POST[‘password’];
$query ="select * from users where user_name ='$user_name' and password='$user_pass'";
$result = mysqli_query($conn, $query) or die("could not run the query");
$num_rows= mysqli_num_rows($result);
if($num_rows==1){
echo "valid login";
}
else {
echo "invalid login";
}
The above SQL query has SQL Injection vulnerability.
If the user enters 1234′ or ‘1=1’ ‘ in password field, the above query will be updated as:
select * from users where user_name ='admin' and password='1234' or '1=1'''
As 1=1 would always execute as true, hacker will be able to login with this simple piece of code even when he/she doesn’t know the actual password.
How to prevent your code from SQL Injection Attacks?
There are four ways you can prevent your code from SQL injection attacks.
Prepared Statements
This is the easiest and most commonly used way to prevent SQL injection attacks in PHP code. Here is how you use prepared statements. In step one you prepare SQL statements with input variables being replaced with symbol ?. The database compiles it and stores the result without executing it. In next step you bind the parameters to their actual values.
$stmt = $mysqli->prepare("select * from users where username=? AND password=?");
$stmt->mysqli_bind_param("ss",$username,$password);
$stmt->execute();
$stmt->close();
In prepared statements, values are combined with the compiled statements and not an SQL statement. SQL injection works by mixing the code with malicious string when it creates SQL to send to database. By sending the actual SQL statement apart from the values, you reduce the risk of mixing code with malicious data that could lead to unwanted results. Using prepared statements also reduced the query parsing time. This enables execution of the same statement repeatedly with a different set of values.
Always escape string
Escaping strings helps in removing special characters for user in SQL statements. For example, the following code is being escaped.
<?php $username = mysqli_real_escape_string($conn,$_POST["user_name"]); $password = mysqli_real_escape_string($conn,$_POST["password"]); mysqli_close($conn); ?>
Use trim() and strip_tags()
Trim() removes extra white spaces from the beginning and end of a string where as strip_tags() is used for stripping HTML and PHP tags. Both of these would help to remove extra codes and spaces generally used by malicious users.
You can use both functions in one PHP statement.
<?php $username = strip_tags(trim($_POST["username"])); $password = strip_tags(trim($_POST["password"])); ?>
Using PHP Data Objects
PHP has its own strategy to prevent SQL Injection attacks. This is probably most effective way to deal with possible hacking attacks. PDO uses prepared statements and bind values at run time. For DPO code will be like following:
$stmt = db::con()->prepare("select * from users where user_name=? AND password=?");
$stmt->bindValue(1, $user_name, PDO::PARAM_STR);
$stmt->bindValue(2, $password, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
To bind multiple parameters, you can use named placeholders instead of? It is represented by: column_name.
$stmt = db::con()->prepare("select * from users WHERE user_name=:user_name AND password=:password");
$stmt->bindValue(':user_name', $user_name, PDO::PARAM_STR);
$stmt->bindValue(':password', $password, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
To avoid SQL Injection attacks, user input should be verified before it reaches to database. It is always better to check input at front end and back end as well. Make sure that user doesn’t access the pages or forms he is not supposed to. Removing unused stored procedures may also help in the prevention of SQL injections. Be careful while using stored procedures as they are easily exploited.
Related Articles
Calculate Average of Numbers in Arrays using C++
Comments