SQL Injection 101: Database & SQL Basics Every Hacker Needs to Know

The key to becoming a competent white hat is knowing how the technology that you are trying to exploit actually works. SQL injection is one of the most common methods of attack used today and also one of the easiest to learn. In order to understand how this attack works, you need to have a solid grasp of ... you've guessed it ... SQL.

This series will start at the bottom with the fundamentals of SQL and SQL injection, moving on to database identification and more advanced techniques, and finally closing with methods used to prevent and mitigate these types of attacks.

What Is SQL Injection?

SQL, which stands for structured query language, is a standard language used for retrieving and manipulating data in a relational database management system (RDBMS). Data is accessed through the use of queries, which allow information to be created, read, updated, and deleted (you will often see web applications referred to as CRUD apps for this reason).

Databases can be used in a variety of situations, but one of the most common configurations is a database serving as the backend to a web application. When certain actions are performed, such as logging in or searching, queries are sent from the web app to the database.

Injection is allowed to occur when input fields are not properly sanitized or escaped. An attacker can enter malicious SQL commands in order to access data that should otherwise be out of sight. SQL injection is generally considered to be high impact because it allows attackers to retrieve sensitive information, tamper with data, destroy data, or even escalate privileges and issue OS commands on the server.

Generally, any kind of input on a webpage is potentially vulnerable to SQL injection because that is where it interacts with the database. Authentication forms where the user logs in with a username and password are the most common types of input that are exploited but search forms, contact forms, and file uploads are all potential targets for injection.

In the first part of this series, we will explore the basics of SQL in order to better understand the types of attacks that are performed.

Anatomy of a Database

Data contained in a relational database is stored in objects called tables. These tables are the virtual representation of relations between different elements, which consist of rows and columns. Rows, called records, contain data for every single entry in the database. Fields, which are the columns of the table, represent a specific piece of information for every record. This is better illustrated in the following Users table:

This table contains three records and four fields; each user in the database is given an ID, Name, Username, and Password. In reality, tables are much larger than this and can contain millions of records, and the database itself can hold just as many tables. You can see how valuable SQL injection is to an attacker with this much data ripe for the picking.

Data Types & Operators

In order to understand the data that we are working with, we need to know the different types of data utilized in SQL. Although exact data types vary between different database systems, in most cases, they are similar enough to tell what they are; usually, they are categorized by text, number, and date.

Operators allow us to manipulate and interact with data in SQL. There are five main categories of operators: arithmetic, bitwise, comparison, compound, and logical. Most of these are similar to other programming languages, but a few caveats do exist.

Statements & Syntax

SQL statements are the code that is passed to the database in order to retrieve or modify data. Let's take a look at the following query and break it down:

SELECT * FROM Users WHERE Name='John Smith';

The first part of this statement (SELECT * FROM Users) selects all of the fields from the Users table. The WHERE clause specifies that we only want to see information from the record where "John Smith" is in the Name field.

SQL uses single quotes for strings, although most database systems will permit double quotes, and the semicolon marks the end of the statement. It should be noted that keywords, like SELECT and WHERE, are case insensitive.

Comments in SQL can be written as single line or multiline:

--this is a single line comment

SELECT * FROM Users;

/*this is a

multi-line comment*/

SQL injection exploits the way comments are handled by rendering certain parts of the query unnecessary. The following injection bypasses the need to input a password:

SELECT * FROM Users WHERE Username='' or 1=1-- AND Password='';

This will return all the records from the database since an empty string or 1=1 always evaluates to true, and the double dashes (--) comment out the Password field.

There are also keywords that exist to make arranging data easier such as MIN and MAX, BETWEEN, and ORDER BY.

Let's look at the following table called LoginSessions:

Suppose we wanted to know who was logged in the longest and when — the query below would return the ID, username, and the shortest and longest session between two dates:

SELECT ID, Username, MIN(SessionLength), MAX(SessionLength)

FROM LoginSessions

WHERE LoginDate BETWEEN '2018-01-01' AND '2018-05-01'

GROUP BY ID

ORDER BY MAX(SessionLength) DESC;

GROUP BY consolidates the rows BY ID, and ORDER BY MAX(SessionLength) DESC shows us the user with the longest session first (descending as opposed to ascending).

Other useful statements include INSERT INTO, which inserts new records into a table, UPDATE, which updates existing records in a table, and DELETE, which is used to delete records in a table. These types of queries can be useful for SQL injection when you want to do more than just retrieve information from the database.

An attacker could insert a new record indicating that they had bought something from an online store, for example, and claim that they never received the product and be compensated for it. If they really wanted to cause maximum damage, the DROP statement could be used. DROP TABLE will remove an existing table in the database, while DROP DATABASE will remove the entire database itself.

Joins & Unions

Joins are used to combine rows from different tables when there is a field related between them. Here is our Users table from before:

And a new table called Logins:

The following SQL statement returns the date Bob Jones last logged in:

SELECT Users.Username, Logins.Date FROM Users

INNER JOIN Logins ON Users.ID = Logins.ID;

An INNER JOIN, or simply JOIN, like the above will return records that have matching data in both tables. A LEFT JOIN returns all records from the left table as well as records that match in the right table, while a RIGHT JOIN returns all records from the right table as well as matched record in the left table. A FULL JOIN will return all records that match in either the left or right table. Although joins aren't required for SQL injection, they can prove very useful when extracting information across tables after you've found your way in.

UNION is used to combine distinct data from two or more SELECT statements. Each SELECT statement must have the same data types, the same number of columns, and they must be in the same order. UNION ALL does the same thing, but it won't eliminate duplicate rows in cases where the same data exist in both of the unioned tables. Let's look at an example using the Users and Admins tables:

Previous SlideNext Slide

The following SQL statement would provide passwords from both users and admins:

SELECT Password FROM Users

UNION

SELECT Password FROM Admins;

Obviously, this wouldn't be a normal query that is run against the database, but if we were allowed to inject this, we could obtain the admin passwords or any other information that isn't usually accessible.

Stay Tuned for More on SQL Injection Basics

Now that we have a better understanding of the fundamentals of SQL, we can use that knowledge to perform more advanced SQL injection. In the following part of this series, we will explore methods to reveal more information about a database in order to narrow down attack vectors.

• Follow Null Byte on Twitter, Flipboard, and YouTube
• Follow WonderHowTo on Facebook, Twitter, Pinterest, and Flipboard

Cover image by Caspar Rubin/Unsplash; Screenshots by drd_/Null Byte