Basic SQL Injection and Mitigation with Example

 

SQL injection is a code injection technique, used to attack data driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).

·         SQL Injection can be used in a range of ways to cause serious problems.

·         By levering SQL Injection, an attacker could bypass authentication, access, modify and delete data within a database.

For a moment, place yourself in the role of an attacker. Your goal is simple. You want to get any unexpected SQL statement executed by the database. You’re only looking to get something to work because that will reveal the fact that the application has a potential vulnerability. For example, consider the simple authentication form shown in Figure 1.

    

                                    Figure 1

Code for Figure 1

<form action="/login.php" method="POST">

<p>Username: <input type="text" name="username" /></p>

<p>Password: <input type="text" name="password" /></p>

<p><input type="submit" value="Log In" /></p>

</form>

You can already make a very educated guess about the type of SQL statement that this application might use to verify the access credentials. It will most likely be a SELECT statement. You can also make a guess about the naming convention used in the database table because it probably matches the simple names used in the HTML form. Because this form is for authentication, there is probably WHERE clause that uses

$_POST['username'] and $_POST['password'].

From all of this, you might predict the following:


<?php $sql = "SELECT count(*) FROM users WHERE 

              username = '{$_POST['username']}'AND 

              password = '...'"; ?>

Assuming this guess is correct, what can you do to manipulate this query? Imagine sending the following username:

akash' /*

SELECT count(*)FROM users WHERE username = 'akash' /*'AND password = '...'";

In this example, /* is used to begin a multi-line comment,

effectively terminating the query at that point. This has

been tested successfully with MySQL. A standard comment

in SQL begins with --, and it's trivial to try both.

This query suggests a successful authentication attempt as long as the akash account exists, regardless of the password. This particular attack is frequently used to steal accounts. Of course, any username can be used (admin is a popular target). Thus, by sending a malformed username, you can manage to log in without having a valid account.

Mitigation using Prepared Statements (Parameterized Queries)

There are a lot of ways to defend SQL injection. One of the primary defense techniques is “Prepared Statements (Parameterized Queries)”. This technique force the developer to define all the SQL code and then pass in each parameter to the query later. This style allows the database to differentiate between code and data, regardless of what user input is supplied.
Prepared statements ensure that an attacker is not able to change the intent of a query, even if SQL commands are inserted by an attacker. For example, if an attacker enters the userID of ABC or ‘1’=’1, the parameterized query would not be vulnerable and would instead look for a username which literally matched the entire string ABC or ‘1’=’1.
Working:

  1. Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled “?”).
    Example:

SELECT count(*)FROM users WHERE username = ? AND password = ?;

  1. Parse: The database parses, compiles, and performs query optimization on the SQL statement template, and stores the result without executing it.
  2. Execute: At a later time, the application binds the values to the parameters, and the database executes the statement. The application may execute the statement as many times as it wants with different values.

Implementation:

<?php 

$stmt = $dbConnection->prepare('SELECT count(*)FROM users WHERE username = ? AND password = ?');

  

$stmt->bind_param('ss', $username,$password);

  

$stmt->execute();

  

$result = $stmt->get_result();

  

echo $result;

?>

This is just a simple example of bypassing user login page whereas SQL Injection can provide an attacker with unauthorized access to sensitive data including, customer data, personally identifiable information (PII), trade secrets, intellectual property, and other sensitive information. There is also an SQL Injection Automation tool sqlmap that is used to perform all type of SQL injection.
So we have to apply Secure Coding so that system will be protected from being compromised.