SQL Injection

1/16

This material was developed with funding from the National Science Foundation under Grant # DUE 1601612

Next

Restart

Back

2/16

We have all used vending machines to satisfy a craving for a snack. Vending machines function by allowing you to see the snacks inside through a clear window and the snacks are arranged by clearly labeled column and row identifiers. This organization makes it very easy for a user to select the correct snack from the machine by entering the correct column and row identifier for that particular snack. The result is that the vending machine returns the correct snack.

Query a request for data or information from a database table or combination of tables.

Close 

4/16

SQL programming language Structured Query Language (SQL) is a standard computer language for relational database management and data manipulation. SQL is used to query, insert, update and modify data.

Websites are often programmed in a programming language called PHP. The PHP will use the SQL programming language to query (ask a question of) the database. When the user enters data into a web form, the request goes to the webserver that hosts the page. It then constructs a SQL query to request particular content from the database and the database returns the correct piece(s) of data from the database. This is similar to what occurs when you choose a snack from a vending machine by entering its column and row identifiers.

PHP a script language and interpreter that is freely available and used primarily on Linux Web servers.

5/16

Consider a vending machine which contains snacks which are organized by columns and row identifiers but where we don’t know what they are. This is similar to a database on a website where we don’t know what the column and row headings are. If we want to learn about the organization of the vending machine we may try various column and row identifiers to begin to identify which ones are being used by the machine. An incorrect identifier entered by the user won’t make sense to the vending machine and an error is returned, for example by not delivering a snack.

If a vending machine is poorly designed, the user may not receive the snack they desired. While errors like these can be a frustrating, the problem is so much larger with web forms. Websites that aren’t properly programmed allow malicious users to exploit the poor web design/PHP code. This exploitation is called a SQL injection. A SQL injection is the use of special characters as control structures to delete, change, update, or otherwise alter a database.

SQL Injection a computer attack in which malicious code is embedded in a poorly-designed application and then passed to the backend database. The malicious data then produces database query results or actions that should never have been executed.

7/16

When you want to exploit a poorly designed vending machine, it is helpful to understand what organization structure is being used because this will allow you to target your attack to that specific system. For example, once you know what organizational structure is being used, you can use that information to get the exact snack you want or perhaps even multiple snacks.

8/16

Database a collection of data organized for rapid search and retrieval by a computer. 

3/16

But what if instead of being able to see every item in a vending machine, you could only guess at what items might be within it because the items are obscured and you don’t know how the items are organized to be able to use the snack’s identifier to retrieve it. This type of vending machine is a bit like a user interacting with a database connected to a web form. Web forms are what users use to interact with a database on the web server which hosts the website.

Webform a web form or HTML form on a web page allows a user to enter data that is sent to a server for processing.

Control Structures in a program, a control structure determines the order in which statements are executed. 

Similarly, a SQL query containing special characters inputted by a user will not make sense to the database and an error is returned. An example of a special character that is concerning is a single or double quote. In programming languages these special characters are often used as control structures, meaning they tell the program something about the code. In properly programmed websites the website uses these special characters as characters versus control structures.

6/16

SQL Injection Code

Similarly, it is helpful to understand what database management system is running on the webserver because this will allow you to target your attack to that specific system. For example, you can try running searches that only work on particular systems to determine what system is running. This is called a blind SQL injection. Once you have this information, you can use SQL injection to output data to you from within the database that was not intended to be outputted to the user; for example, usernames and passwords.

Blind SQL Injection a type of SQL Injection attack that asks the database true or false questions and determines the answer based on the applications response.

usernames and passwords

9/16

A sample of a vulnerable website PHP/MySQL “login” might look like this:

10/16

$db = new mysqli('localhost', 'root', 'passwd', 'base'); $result = $db->query('SELECT * FROM users WHERE user="'.$_GET['user'].'" AND pass= "'.$_GET['password'].'"');

This is vulnerable because an an attacker may submit certain code instruction such as ” OR 1 -- as the username and whatever as password. The variables would then contain these values:

$_GET['user'] = " OR 1 -- $_GET['password'] = whatever

The resulting SQL query would become:

SELECT * FROM users WHERE user="" OR 1 -- AND pass="whatever"

Everything after -- (which indicates the start of a comment in SQL) will be ignored. Meaning the query to be executed would look like this:

SELECT * FROM users WHERE user="" OR 1

Essentially the query now says “Grab everything (SELECT *) from the user list (FROM users) where the username matches nothing (WHERE user=””) or 1 (which will be interpreted asTrue (OR 1))“. Since the latter statement will always result in True, the right hand of the statement will successfully eliminate the left hand statement and the condition will always be true. The result of that query would be the same as this one:

SELECT * FROM users

This query will return all data there is about all the users in the database, including their usernames and passwords.

15/16

So how do we prevent a website from being vulnerable to a SQL injection? A SQL Injection needs two elements to function: a relational database that uses SQL, and a user input which is directly used in a SQL query. Therefore, it is necessary to modify the vulnerable website code to escape all bad characters that might allow a user to break a SQL statement.

Wherever possible, run code with the least possible user privileges.

Enforce identifier length limits.

If an identifier needs to be generated based on a user input, avoid concatenating the user input or using sequences to construct the generated identifier name.

Using these principles you should review your code, run static code analysis, test your code using fuzzing tools, and generate test cases for SQL injection and run them on your own code.

Do not allow arbitrary query parameters to be exposed.

Prevent control characters in user inputted identifier names.

16/16

The main principles to employ in designing your code to limit your exposure from SQL injection attacks are to:

Use bind arguments rather than user generated identifier names whenever possible.

Keep error messages from divulging any metadata information (such as column names, table names, and so on).

11/16

12/16

13/16

14/16