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.

login form

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 &nbsp; :</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.