From our previous article we read about code injection, so from today’s article we are gonna know about SQL Injection also known as SQLi.

Lets get started 😃

SQL injections occur when an attacker inserts SQL query via user input and execute malicious SQL statements. It is the most common and dangerous vulnerability of web applications. A successful SQL injection attack can view confidential data, delete sensitive data and sometimes gain administrative access to the database which can be fatal.

For an attacker to try SQL injection, the targeted web application must use an SQL based database and directly include user input within a SQL statement.

Now lets see a simple example of SQL Injection.

1
2
3
4
5
6
7
8
# Define POST variables
uname = request.POST['username']
passwd = request.POST['password']

# SQL query vulnerable to SQLi
sql = “SELECT id FROM users WHERE username=’” + uname + “’ AND password=’” + passwd + “’”
# Execute the SQL statement
database.execute(sql)

The above script is used to authenticate users to a web application. It takes username and password as user inputs to authenticate a user. For an example an attacker might input malicious data to alter the SQL statement and gain access to unauthorized data.

A simple example would be to set the password field to password’ OR ‘1’=’1

This would result the SQL statement to execute as,

1
SELECT id FROM users WHERE username=’username’ AND password=’password’ OR '1'='1’  

And since the statement ‘1’=’1’ is always true, the query results in authorizing the attacker. And in most cases the first user account in the database is the administrative account which will give Administrative login to the attacker.

So how to prevent SQLi?

1.Parameterized Statements :

Parameterized statements make sure that the parameters (i.e. inputs) passed into SQL statements are treated in a safe manner.

1
2
3
4
5
6
7
8
9
10
11
12
// Construct the SQL statement we want to run, specifying the parameter.
$sql = $dbConnection->prepare('SELECT * FROM users WHERE username = ?');

// Run the query, passing the 'username' parameter value...
$sql->bind_param('s', $username); 

// 's' specifies the variable type => 'string'
$sql->execute();
$result = $sql->get_result();
while ($row = $result->fetch_assoc()) {
    // do something with $row
}

In the above example the parameterized string and the parameters are passed to the database separately, which allows the driver to correctly interpret them.

2. Object Relational Mapping :

Use Object Relational Mapping (ORM) frameworks to make the translation of SQL result sets into code objects more seamless.

Eg: Ruby on Rails’ Active Record framework

1
2
3
4
5
def current_user(username)
# The 'User' object is an Active Record object, that has find          methods 
# auto-magically generated by Rails.
User.find_by_email(username)
end

3. Escaping Inputs :

Ensure proper escaping of special string characters in input parameters such as‘ and “.

Escaping symbol characters is a simple way to protect against most SQL injection attacks, and many languages have standard functions such as in PHP to achieve this.

4. Sanitizing Inputs :

Check for suspicious inputs and reject them. In our example hack, the user supplied a password as password’ or ‘1’=’1, which looks pretty suspicious as a password choice.

So from this I hope you can understand how important it is to protect your web application from SQL injection attacks.