SQL Injection: Part 2

Shivam Batra February 05, 2021

Second-Order SQL Injections

Brief Recap

In our previous post, we learned about first-order SQL injections and ways to prevent such attacks through the white-box approach. We learned 2 white-box techniques Sanitize User Input and Use of prepared statements. For detailed information please refer SQLINJECTION -PART 1

In this post, we'll discuss second-order SQL injections.

Introduction To Second-Order SQL Injections

The concept behind first and second-order injection is the same but in first-order injections, we learned that, as soon as the injection is done, the attacker gets instant results mostly on the same page but in the case of second-order injections, the attacker performs the injection, the information is stored in the database and is indirectly used for extraction of useful information from our database.

Working Of Second Order SQL Injection

Suppose we have a sign-up system of a corporate company that stores email, desired username and password of an employee.

Sign Up creation:

<!DOCTYPE html>
<html>
<head>    
   <title> CREATING NEW USER </title>
</head>
<style>
</style>
<body>
<form action="register.php"
 method="post">
<div align="middle">
<h1> REGISTRATION FORM </h1>
<table width="600" border="5" 
cellspacing="2" cellpadding="6" padding="50">
<tr>
 <td>EMAIL:</td>
    <td> <input type="email"
 name="email"></td>
  </tr>
  <tr>
    <td>USERNAME:</td>
    <td><input type="text
 name="user_name"></td>
  </tr>
    <td>Password:</td>
    <td><input type="password"
 name="passwo"></td>
  <tr>
    <td>&nbsp;</td>
    <td><input button type="submit"></td>
  </tr>
</table>
</div>
</form>
</body>
</html>

The above code creates a sign-up form that accepts the corresponding information from an employee.

<?php
$email=$_POST["email"];
$user_name=$_POST["user_name"];
$passwo=$_POST["passwo"];
$servername = "localhost";
$username = "root";
$password = "databasepass";
$dbname = "blog
// Create connection
$conn = new mysqli($servername,
 $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed:
  $conn->connect_error);}
 $sql = "INSERT INTO users_blog2
 (email,user_name,passwo)
VALUES ('$email','$user_name','$passwo')";
$sql.  "INSERT INTO users_blog2 (email,user_name,passwo)
VALUES ('$email','$user_name','$passwo')";
$sql.  "INSERT INTO users_blog2 (email, user_name,passwo)VALUES ('$email', '$user_name','$passwo')";
if ($conn->multi_query($sql) === TRUE){ 
$last_id=$conn->insert_id;    
echo "New record created successfully Recorded id is: ".$last_id." ".$user_name;
} else { 
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close(); 
?>

This code connects the sign-up form created before to our database, the values entered by an employee are saved to our database.

Login System

<!DOCTYPE html>
<html>
<head>
    <title>LOGIN</title>
</head>
<style>
</style>
<body>
<form action="process_checking.php" method="post">
<div align="middle">
<h1> LOGIN </h1>
<table width="600" border="5" cellspacing="2" cellpadding="6" padding="50">
  <tr>
    <td>USERNAME:</td>
    <td><input type="text" name="user_name">
    </td>
  </tr>
    <td>Password:</td>
    <td><input type="password" name="passwo"></td>
  <tr>
    <td><a href=/localhost/phpmyadmin/phplessons/2ndsqlinj/updatepass.php></td>
    <td><input button type=\"submit\"></td>
  </tr>
</table>
</div>
</form>
</body>
</html>

This code is a login-form that accepts only usernames and passwords created in the above sign-up form.

<html>
<body>
<?php
$uname=$_POST["user_name"];
$user_password=$_POST["passwo"];
$servername = "localhost";
$username = "root";
$password = "databasepass";
$dbname = "blog2";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
$linkadd='http://localhost/phpmyadmin/phplessons/2ndsqlinj/updatepass.php';
$result = $conn->query("select * from users_blog2 where user_name='$uname'and passwo='$user_password'\");
$row =  $result->fetch_assoc();
if($row['user_name']==$uname && $row['passwo']==$user_password){
echo "<a href='".$linkadd."'>click to update password</a>";
}
$conn->close(); 
?>
</body>
</html>

The above code compares the username and password entered by the user to the entries in our database. The variable 'linkadd' is used to direct the user to the password updation page, Password updation is as follows:

Password Reset

<!DOCTYPE html>
<html>
<head>
    <title>LOGIN</title>
</head>
<body>
<form action="resetpass.php" method="post">
<div align="middle">
<h1> PASSWORD RESET</h1>
<table width="600" border="5" cellspacing="2" cellpadding="6" padding="50">
  <tr>
    <td>USERNAME:</td>
    <td><input type="text" name="user_name"></td>
  </tr>
    <td>Password:</td>
    <td><input type="password" name="passwo"></td>
  <tr>
    <td>&nbsp;</td>
    <td><input button type="submit"></td>
  </tr>
</table>
</div>
</form>
</body>
</html>

The above code accepts for username and password from the employee.

<?php
$uname=$_POST["user_name"];
$updated_password=$_POST["passwo"];
$servername = "localhost";
$username = "root";
$password = "databasepass";
$dbname = "blog2";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error){    
  die("Connection failed: " . $conn->connect_error);
}
$result = $conn->query("update users_blog2 set passwo='$updated_password' where user_name='$uname' ");
$result2 = $conn->query("select * from users_blog2 where user_name='$uname'and passwo='$updated_password' ");
$row =  $result2->fetch_assoc();
if($row['user_name']==$uname && $row['passwo']==$updated_password){
  echo "password changed for"." ".$uname;
}
$conn->close(); 
?>

The above code accepts username and password from the password updation page and then compare the information provided to the information stored in our database. If the username and password are correct it accepts the username and new password

Hence we have seen how our login system with password updation is created.

The codes provided have been checked multiple times and are working up to the mark, some snapshots of the above-discussed pages are:


THE DATABASE


THE SIGN-UP PAGE


THE LOGIN PAGE


UPDATE PASSWORD PAGE

INJECTION

Example:

SELECT * FROM users WHERE firstname='shreyas'; -- ' AND email='1111' ;

In the above query the statement after '- -' is commented out. We will use this weak point in MySQL to perform the injection.

Suppose the attacker is aware of the username of an employee named Ram with the username Ram12 but the password to this username is unknown to him.










Snapshot of the old database

Step1- Sign up as a new user with username Ram12"-- any password and email id of personal choice (let it be 1234, [email protected] respectively).



In the image is given below we can see entirely two different accounts are created.


Step2- Now user Ram12\" -- can login and go for password updation.

Login:


Password Updation:


The Magical Part:

When the new password is updated for Ram12\"-- we notice that password for Ram12 is updated instead of Ram12\"--, this is because the query after -- is commented out and not executed.

The Final Database:


Thus the injection is successful.

Crux

Thus we have seen a simple application of second-order SQL injections. The vulnerable part is injected into the database without instant results. Then the stored information is further used to do the needful.

The password belonging to Ram12 was updated. The reason behind this is, when the following query is used(let new password be- men):

"update users_blog2 set passwo='$updated_password' where user_name='$uname'"

It works in the following manner:

"update users_blog2 set passwo='$updated_password' where user_name=Ram12\"--\"

here , the stuff after -- is commented out and it behaves like this:

"update users_blog2 set passwo='$updated_password' where user_name=Ram12\"

Thus the password of Ram12 is changed instead of Ram12\" --

There is one more method under this category, which uses triggers in SQL but the concept is the same. The link to the same is this

To view the demo provided in the above link requires a flash player.

Protection

This post was primarily focused on the second-order SQL injection procedure but still coming to easy yet effective ways to overcome this vulnerability is the use of certain functions like :

  1. $data = trim($data);

  2. $data = stripslashes($data);

  3. $data = htmlspecialchars($data);

  4. mysql_real_escape_string()

  5. stmt=bind_param('sss',$firstname,$lastname,$email)

  6. $db->prepare(\"INSERT INTO users ($firstname,$lastname,$email) values(?,?,?)\");

The proper use of these white-box approach based methods are discussed in the previous post, the link has been provided here

Conclusion

To conclude, we realize that protection is better than cure since prevention is easy and cost-effective but the curing process can prove to be very complex and expensive. As far as the injection is concerned there can be multiple ways of hacking a database, but the approach is more or less the same, i.e weak points of a SQL query and loopholes in database security. Keeping that in mind we shall discuss more security of our database in our future posts rather than attacking techniques. 

Comments and criticism from readers will be highly appreciated and incorporated in the further/same post.

P.S- I would like to thank Lakshay for helping me through the post.





0 Comments