SQL Injection is the hacking technique which attempts to pass SQL commands through a web application for execution by the backend database.
I am going to explain SQL injection prevention with an example of a login page.
Possibilities when a user login:-
1) A valid user enters a valid username and password then he login successfully.
$user_name=$_POST['dname'];//user name is amit
$user_password=$_POST['dpass'];//password is 12345
$query="select * from login_details where user_name='amit' and user_password='12345' ";
2) An invalid user enters username or password then an error message is displayed i.e. “Invalid username or password, please try again!!!.”
3) A smart user enters such a value which allow him to bypass login form without knowing the actual username or password.
$user_name=amit;//user name is amit
$user_password=123'or'1'='1;//password is 123'or'1'='1
$query=" select user_name,user_password from login_details where user_name='amit' and user_password='123'or'1'='1' ";
As the inputs of the login form are not properly sanitised, the use of the single quotes has turned the WHERE
SQL command into a two-component clause.
The '1'='1'
part guarantees to be true regardless of what the first part contains.
SQL Injection Prevention:
SQL injection can be prevented by using prepared statement.Follow the below steps to use prepared statement:-
Step-1: Create a prepared statement using prepare() which take SQL query as a parameter in SQL query we use? as a placeholder rather than passing the variable as values to prevent SQL injection.
// Prepare the query
$stmt = $conn->prepare("SELECT user_name,user_password FROM login_details WHERE user_name=? and user_password= ?");
Step-2: Now we bind values to? using bind_param() it takes two parameters , in first we pass value types in double quotes and in second we pass variables.
// Bind parameters s – string, b – boolean, i – int, etc
mysqli_stmt_bind_param( “ss”, $user_name, $user_password);
Step-3: After binding values we need to execute the query using execute().
// Execute SQL
$stmt->execute();
Step-4: Now we bind the result using
bind_result( $name, $password);
Step-5: As we are retrieving data so now we fetch data using
fetch().
// Fetching data
while ($row = $stmt->fetch()) {
echo"<b>User name:</b>" . $name . "<br/>";
echo "<b>Password:</b>" . $password;
}
Step-6:Close the statement by close().
// Close the statement
$stmt->close();
PHP File: sql_injection_prevent.php
<?php
// Connect to database
$server_name = "localhost";
$user_name = "root";
$password = "";
$db = "login";
$conn = mysqli_connect($server_name, $user_name, $password, $db);
// Checking if login button is clicked or not
if (isset($_POST['dsubmit'])) {
// Assigning values to variables fetched from URL
$user_name = $_POST['dname'];
$user_password = $_POST['dpass'];
// Prepare the query
$stmt = $conn->prepare("SELECT user_name,user_password FROM login_details WHERE user_name=? and user_password= ?");
// Bind parameters s - string, b - boolean, i - int, etc
$stmt->bind_param("ss", $user_name, $user_password);
// Execute SQL
$stmt->execute();
// Store result
$stmt->store_result();
// Bind the result
$stmt->bind_result($name, $password);
if ($stmt->num_rows == 1) {
// Fetching data
while ($row = $stmt->fetch()) {
echo" <div class="output"> ";
echo"<b>User name:</b>" . $name . "<br/>";
echo "<b>Password:</b>" . $password;
echo"</div>";
}
} else {
echo "invalid username or password!!!";
}
// Close the statement
$stmt->close();
}
// Close the connection
mysqli_close($conn);
?>
Given below code creates an HTML login form.
<html>
<head>
<title>Sql-Injection Prevention</title>
<link href="sql_injection.css" rel="stylesheet" type="text/css"/>
</head>
<body>
<h1>Login..</h1>
<form action="sql_injection_prevent.php" method="post">
<div class="form">
<b> User Name:</b> <input type="text" name="dname" value=""><br/><br/>
<b> Password :</b> <input type="text" name="dpass" value=""><br/><br/>
<center><input type="submit" name="dsubmit" value="login"></center><br/>
</div>
</form>
</body>
</html>
CSS File: sql_injection_prevent.css
Styling HTML elements.
form{
border:2px double gray;
width:400px;
height:180px;
background-color:lightgoldenrodyellow ;
margin- left:80px;
}
h1{
margin-top:40px;
margin-left:100px;
color: crimson;
}
.form{
margin-top:50px;
position:relative;
padding-left:40px;
}
.msg{
margin- top:300px;
margin-left:120px ;
position:absolute;
color: red;
}
Conclusion:
Prepare statement offers more security as instead of using things like $_GET[‘username’], we have used ?’s. These ?’s separate the SQL logic from the data. The ?’s are placeholders until the next line where we bind our parameters to the $user_name and $user_password.
Get more related information here –
One Reply to “Sql Injection Prevention In PHP With MySQLi”
Hello Neeraj,
Great article and great website too. I have read a couple of articles and liked them all; very informative.
My question regarding this article is how will the hacker add that additional 1 = 1 condition in the query as the server file is inaccessible?