SQL Injection Attacks

Introduction:

Developing and designing software is a complex and multi-faceted task; as such requires a good deal of planning. An important part of writing effective code is making it secure. Oversight on the part of the programmer may create vulnerabilities in the software which can be exploited by individuals with malicious intent. One such vulnerability that could be very damaging if gone unchecked is the SQL injection vulnerability. This article is based on what I have learnt from the University of Syracuse's SEED Lab for SQLi Attacks.

What is an SQL injection (SQLi) vulnerability?

An SQLi vulnerability is a design flaw in an application's coding caused by a lack of validating and sanitizing inputs. Validation is the detection of single quotation marks and sanitization is the doubling of (adding an additional single quotation mark to) said marks. Web-based applications commonly utilize databases in the back-end to store relevant data. SQL queries are used to retrieve data from the databases and are built by the application using php or other languages, often utilizing user input in the creation of these queries. The reason why unhandled single quotation marks in user input is dangerous, is because they can be used to modify the creation of SQL queries. Modified SQL queries can trick the database into illicitly releasing data, some of which may be privileged and sensitive, such as: passwords, social security numbers, credit card details, etc.

What is an SQL injection (SQLi) attack?

An SQLi attack is the exploitation of SQLi vulnerabilities by an individual to steal, modify or corrupt data stored in a database. SQLi attacks usually require some prior knowledge of how the application is built, such as: the language in which the application is developed, the database platform utilized by the application, etc. Blind SQLi attacks however are launched without any such knowledge, but are harder to execute.

What does an SQLi vulnerability look like?

SQLi vulnerabilities are found in the code of affected applications and as such it is helpful to have some code on hand to better understand this issue. The following code snippet is taken from the University of Syracuse's SEED Lab for SQLi attacks:

$conn = getDB();
$sql = "SELECT id, name, eid, salary, birth, ssn,
               phonenumber, address, email, nickname, Password
        FROM credential
        WHERE eid= ’$input_eid’ and password=’$input_pwd’";
$result = $conn->query($sql))
// The following is psuedo code
if(name==’admin’){
        return All employees information.
} else if(name!=NULL){
        return employee information.
} else {
authentication fails.
} 

Here we can see a SELECT query being built. Line number 5 contains the code: WHERE eid= '$input_eid' and password= '$input_pwd'". The text in bold, i.e '$input_eid', is vulnerable to SQLi attacks. The pseudo code mentioned, also points out how the code verifies valid inputs for user id and password: all employee records are released if input name='admin', some employee information is released if name!=null, no records are released if either of the prior conditions are satisfied. The goal of the attacker is now to find an input that will give them access to the database without any knowledge of the id or password of any authorized user, but how do they achieve this?

The secret lies in how the $input_eid can be manipulated. If we take a closer look we can see that $input_eid is in quotes:

WHERE eid= ’$input_eid’ and password=’$input_pwd’";

We have to somehow eliminate the quotes. The following is our SQLi code:

' OR 1=1 #

The leading single quote will close the first quote in '$input_eid'. OR 1=1 is always true and will trigger the if name!= null response as mentioned in the pseudo code earlier. Finally the # symbol will convert all text after it into a comment on that line.

If we wish to access the name== 'admin' response, we need only make a small change in our code:

' OR name='admin' #

Now that we have access to the database, we can use another vulnerability to modify values in the database. The following code snippet is also taken from the University of Syracuse's SEED Lab for SQLi attacks:

$conn = getDB();
$sql = "UPDATE credential SET nickname=’$nickname’,
                              email=’$email’,
                              address=’$address’,
                              phonenumber=’$phonenumber’,
                              Password=’$pwd’
        WHERE id= ’$input_id’ ";
$conn->query($sql))

Here the query being built is an UPDATE query. This time we use '$nickname' as our point of entry to modify a value. We can take a look at the previous code snippet to find something to change:

$sql = "SELECT id, name, eid, salary, birth, ssn,

Let's modify the salary. We do the following to achieve this:

', salary= <some_integer_value> #

The value in the salary column will now be changed to <some_integer_value> for the user_id that has been logged in with.

How do we prevent SQLi attacks?

SQLi attacks can be prevented using the following methods:

Using prepared statements: Prepared statements are those which bind user input to specific variables. Once the binding takes place, the inputs are treated only as values and not as code.

Using stored procedures: Stored procedures work similarly to prepared statements, but are stored in the database rather than coded into the application's code.

White-list input validation: In this method, we compare all inputs to table, column and row names. If something doesn't match-up, then we know something is wrong and an InputValidationError is thrown.

References: