For this chapter and the next, the topics we will cover will mostly be theoretical. However, we suggest that you read the SQL technical documentation. Here, we have provided, for reference, the MySQL, Oracle, and Microsoft SQL Server documentation:

• https://dev.mysql.com/doc/refman/8.0/en/
• https://docs.oracle.com/en/database/oracle/oracle-database/index.html
• https://docs.microsoft.com/en-us/sql/sql-server/?view=sql-server-ver15

One of the most common ways to keep data memorized in computer systems is by relying on databases. Databases can be seen as large software containers that can hold lots of information in a structured and accessible way, in order to optimize how to store data and access their operations.

Depending on the approach and model used, the way in which this is achieved can vary in terms of implementation. One of the most common ways is to use the relational model, which is based on relational algebra, for which data is a collected as a series of records that describe the relationships that exist among objects. SQL is a query language that is based on such concepts, and it is widely adopted in many database systems. This section will deal with these topics in depth by first explaining database management systems, relational databases, and SQL.

Database management systems and relational databases

The implementation of a database, as we mentioned earlier, relies on an underlying system, or a database nanagement system (DBMS). A DBMS is basically a piece of software responsible for storing, accessing, manipulating and, in general, managing data through a specific definition of the collected and managed information.

For the purpose of this book, we will now divide database systems into two large families to better understand the differences between them. We can distinguish between database models in terms of relational databases and non-relational databases due to the relevance of the relational model in data management.

Relational databases

Relational databases have been widely considered as a standard due to their many advantages. Data is collected in tables, in which rows represent objects, memorized as records, and columns represent their attributes. The name is derived from the way in which data can be correlated and connected, that is, through relations based on common attributes among tables. Thus, the concept of relational algebra becomes relevant as it describes the way in which, through a structured procedural language, data tables can be managed. SQL is the most popular representative of this model as it takes advantage of most of the concepts of relational algebra, thus providing a model that is easy to use by anyone without any coding experience, while maintaining its overall efficiency:

Figure 1.1 – A simple relational schema made up of three tables describing cars and owners, with the IDs (unique) put into a relationship

Non-relational databases

No-rel, which stands for non-relational, databases are a family of DBMS models considered as an alternative to the relational model and are usually much more prominent among database systems. Originally, the term NoSQL was used to define this family of systems, but it was considered misleading: some of the first attempts at building non-relational databases actually used some concepts of the relational model. No-rel databases include many models, some of which are as follows:

• Network databases model the data as connected nodes in a network:

Figure 1.2 – A simple network schema to represent ownership relations between owners and cars

• Graph-based databases highlight the connections among data using a graph-like navigable structure:

Figure 1.3 – The same ownership relation as in the relational example, this time represented in a graph-based model schema

• Object-oriented databases model data as objects, in a similar fashion as in programming languages such as Java:

Figure 1.4 – The ownership relationship represented in an object-oriented model schema

• Document-based databases describe data within documents containing key-value pairs, specify the way in which data is memorized and managed, and provide a flexible approach that does not rely on a defined schema. Document-based models can usually include embedded objects as collections within a single key, as shown in the following image:

Figure 1.5 – A document-based model schema that can represent the ownership relationship

Despite the name, SQL injection, in some form or another, might affect all existing database models. We will now focus on relational databases and SQL.

SQL – Structured Query Language

SQL stands for Structured Query Language, and it is the main tool used to access, navigate, and manage a relational database. SQL provides a well-structured language that is easy to understand, thanks to its natural language-like commands and the clarity of the operations it executes corresponding to specific language strings, which will be described in the following sections.

SQL has many different implementations, depending on the system it resides on, with some slight differences, some of which will be explained in the next chapter in more detail, as they are directly relevant for the SQL injection attack. Let's take a look at the most popular SQL implementations.

MySQL

MySQL is an open source version of SQL that's used in many web application frameworks and famous websites. It is considered one of the main representatives of SQL technologies, as well as an overall well-performing implementation.

MySQL is considered probably the best implementation in terms of open source SQL engines, and it is often taken as a reference for SQL syntax in general.

Here, we will list some peculiarities to remember about MySQL.

There is more than one way to insert comments in terms of character sequences:

• #
• /*comment*/
• -- (This requires a blank space followed by any character in order to be interpreted as a comment. In practical tests, we use the combination -- -.)
• ;%00 (%00 is the null character, here shown in URL encoding. This is an unofficial method for inserting comments as it's not shown in the official documentation.)
• ` (Reverse single quote, another unofficial method.)

In general, MySQL systems have two default databases that are always present in the schema:

• mysql (only available to privileged users)
• information_schema (only available from MySQL version 5 onward)

MySQL supports functions and variables such as VERSION() and @@VERSION to retrieve MySQL server versioning.

SQLite

SQLite provides a different approach by presenting an implementation that is directly embedded in the application code, without the client-server architecture being used. While it is recommended for lightweight applications, such as mobile apps, it may have some shortcomings due to some intrinsic simplifications.

The main peculiarity about SQLite is that it stores information within a SQLite database file, without requiring the client-server infrastructure. Thus, being standalone, it's best not to use it for sensitive operations, such as authentication, or, in general, storing sensitive information: anyone with access to the filesystem can easily get a full view of the database.

Oracle Database

Oracle Database, often referred to as just Oracle, is one of the main proprietary SQL systems. Despite being born as a SQL relational DBMS, it started supporting different models over time. Thus, it is considered a multi-model DBMS.

In terms of proprietary database systems, Oracle is the most popular model among enterprises thanks to its wide compatibility with many technologies, programming languages, and database models.

Like MySQL, Oracle Database also has some peculiarities you need to remember in terms of database structure and syntax.

Unlike other database systems, Oracle Database supports only one way to insert comments in terms of character sequences: --.

Oracle Database systems also have two default databases:

• SYSTEM
• SYSAUX

Microsoft SQL Server

Microsoft SQL Server is one of the most common solutions in the enterprise world. It is a SQL DBMS optimized for running on the Windows Server OS, which is one of the most widely adopted server operating systems.

Microsoft SQL Server (MSSQL) also has its own share of peculiarities.

MSSQL supports three ways to insert comments in terms of character sequences:

• /*comment*/
• --
• %00

MSSQL systems have many default databases that are always present in the schema:

• pubs
• model
• msdb
• tempdb
• northwind
• information_schema (from MSSQL Server 2000 onward)

MSSQL allows the use of the @@VERSION command for retrieving the database system version.

SQL is, in general, a high-performance language capable of querying structured data. Queries follow a specific readable syntax and allow users and database managers to perform various operations, ranging from creating and deleting tables to extracting data according to specific conditions. The following section focuses on showing the basic SQL syntax and capabilities, setting language implementation differences aside for the moment, while also examining the logic behind the commands mentioned.

As mentioned earlier, SQL is an easy to use and understand language capable of many different types of operations. Like all languages, it is based on interpreting command strings that are inserted with an expected syntax, with specific statements corresponding to one and only possible operation. SQL's main statements can be of many types. Let's take a look at the most important ones:

• SELECT statement: SELECT is the most common SQL command. Its purpose is to allow the database to be searched, showing the specified attributes from the records that satisfy (optionally) a specific condition; for example:

  SELECT color, shape FROM objects 

  This statement shows the color and shape attributes of all the records from the objects table. SQL also allows for a wildcard – in this case, the character * – to make general selections:

  SELECT * FROM objects 

• This statement will return all the records from objects table, showing all the attributes. This search can also be refined by adding a WHERE clause, which specifies a condition:

  SELECT color, shape FROM objects WHERE color='blue' 

  This statement will only show records with the value blue within the color field:

Figure 1.6 – A SELECT query in SQL, with a view of its result

Other clauses can be utilized within a SELECT statement:

- DISTINCT clause: Used to return results without duplicates (SELECT DISTINCT color FROM objects)

- ORDER BY clause: Used to order results based on an attribute (SELECT * FROM objects ORDER BY color ASC for ascending order, or DESC for descending order)

Having a clear understanding about how the SELECT statement works is very important for mastering SQL injection. Being the most common statement, the abuse of its structure is the prime enabler of a SQL injection attack, allowing for other SQL commands to be inserted within an apparently harmless instruction. We will see further details of this in the next chapter.

• INSERT statement: The INSERT statement is used to add data to a database in a very simple way, that is, by specifying the values to insert into the attributes of choice. Let's take a look at an example:

  INSERT INTO objects (shape, color) VALUES (square, blue)

  The preceding statement adds new data to the database through the square and blue attribute values for the shape and color attributes, respectively:

Figure 1.7 – An INSERT query in SQL, with a view of its result

Alternatively, you can add data without specifying the attributes (INSERT INTO objects VALUES (square, blue)), but bear in mind that since unspecified attributes are set to null, this option is only recommended if entire records are inserted in the correct attribute order. The INSERT statement could also be used maliciously, for example, to insert account credentials that could be used by attackers to gain access to a system.

• CREATE and DROP statements: The CREATE and DROP statements are, as their names suggest, made to create or delete entire SQL tables or even databases. DROP has a very simple syntax, because it just deletes all the records in a specified table or database (DROP TABLE objects, DROP DATABASE db), while CREATE can be used in various ways, depending on the objective. Creating a database is similar as in the DROP statement, that is, just using CREATE DATABASE db, while tables obviously need specific information for the attribute's creation. Referring to the objects example, we could use the following code:

  CREATE TABLE objects (objID int, shape varchar(32), color varchar(32)) 

  This statement will create a table named objects with objID as an integer and shape and colors as strings with their maximum lengths set to 32 characters.

• ALTER statements: The ALTER statement is used to modify general information regarding databases or tables:

  ALTER DATABASE dbname1 MODIFY NAME=dbname2 

  The preceding statement will rename the dbname1 database dbname2. The following statement will also add, to an existing table (objects), a new string field named newcolumn, which will store a maximum of 32 characters:

  ALTER TABLE objects ADD newcolumn varchar(32) 

  ALTER TABLE can also be used to drop entire attributes (ALTER TABLE objects DROP newcolumn) or modify columns to change their data type. The ALTER statement is not typically used in SQL injection since the DROP statement is often preferred for disabling application functionalities.

The statements listed here only comprise a subset of what is possible in SQL, but in order to better understand the main issue of this book, these should provide a sufficient basis for what we will cover next.

Besides statements, SQL also provides a wide range of clauses that can be used to integrate commands to refine data searches and include constraints in the operations on the database. We have already seen the WHERE, DISTINCT, and ORDER BY clauses for the SELECT statement. More advanced examples will be examined in the following chapters.

As we've seen, SQL allows us to perform a very large set of instructions, making interacting with the whole database possible at many different levels. We can do this by modifying its structure too. With such a powerful language that can be used to perform any sort of operation on a database, it is natural to start wondering, what could go wrong? With a vast array of possible statements and operations, of course, a malicious attacker could have a wide selection of tools that could be used to damage databases, stored data, and applications using such data, in different ways. One simple instruction, such as DROP DATABASE <database name>, for example, could entirely compromise the functionality of an application that relies on databases to query data or even authentication data (that is, usernames and passwords).

For this reason, SQL code is never, at least directly, conceived to be interacted with inside an application. Instead, it is the application that, given user input, prepares the SQL code needed to be sent to the database to extract (or modify) the data requested.

However, there are ways for potential attackers to abuse SQL syntax and insert arbitrary instructions. This way of attacking is, in general, called code injection, and involves inserting code of a language recognized by a computer or a system into existing code, making it possible to perform otherwise not envisioned tasks.

Being a simple (yet very powerful) language, injecting code within SQL statements is relatively easy and can also produce quite damaging results, varying from granting authenticated access to anybody to utterly destroying a web application relying on databases. The preceding example is just one of many destructive commands that could be injected.

The main issue behind the use of SQL is that code is evaluated by the application while it's running: if no controls are in place, the program itself, which has already started, does not evaluate the statements in terms of content or correctness. A malicious attacker could exploit this by inserting arbitrary commands within user-provided input, such as in authentication forms or string fields that are evaluated by the application by inserting those within running code.

In the following section, we will see how this is possible in a vulnerable application.

The main problem that leads to code injection – and obviously SQL injection too – is the way programming (and query) languages themselves inherently work.

Since commands are just strings of characters that are interpreted as code, and user input is made of text, we could, in principle, insert code syntax within user input. If not correctly validated and simply accepted without us applying any control, this injected code could result in the execution of arbitrary commands that have been manually inserted by a malicious user.

This is because a naïve string reader does not make any distinction between text and code as it is essentially binary data coded as text – the same is done from the standpoint of a computer program or an application. Usually, in order to inject specific instructions or code objects, specific characters are used to trick the parser – the software component in charge of reading the text input – into interpreting the inserted code as unintended commands. Traditionally, the most trivial way to inject code is by inserting the line termination character – the semicolon in most programming languages – so that, besides the intended operation, the new one is considered as an entirely different instruction. Other characters can be used to manipulate the application's behavior, such as the comment separator, which is used to exclude altogether parts of code following the instruction.

SQL is no exception to this: many techniques used in code injection also apply to SQL. In fact, this vulnerability was discovered over 20 years ago by commands being injected into SQL queries, resulting in unintended operations. We will see specific forms of this exploitation in later chapters, all of which can be used to cause damage to applications or to help the attacker gain strategic advantage, both in terms of data and in some cases accessing otherwise restricted systems.

Luckily, SQL injection only applies to applications that are poorly coded. Adding specific controls for the user-provided input – and inner application streams – can prevent this problem altogether. Besides improving the security controls on the input, dropping suspicious web traffic could also help avoid the exploitation of the vulnerability. Ideally, this being a coding error, you should develop secure code in accordance with the best practices available. Here are some general suggestions that will be further explored later in this book:

• Do not allow unnecessary special characters in queries: Usually, it's through the use of special characters that SQL injection is enabled. If special characters are allowed in queries, those could also be encoded in a way that is not interpreted by SQL, thus foiling SQL injection attempts based on special characters such as string separators (single or double quote), instruction separators (semicolon), and comment separators.
• Do not allow specific suspicious commands: Some commands are often used in SQL injection attacks. Allowing specific authorized commands only, through the means of a whitelist, helps us avoid the insertion of arbitrary commands within an application, according to the expected behavior of the software component.
• Do not give carte blanche to the user: While we would love users to be respectful and responsible, to us, they could be anybody – even malicious users as far as we know. It's a good idea to limit their actions as much as possible, thereby never trusting user input. Query input should always be converted into parameters and serialized accordingly.

These points help in protecting against SQL injection, at least as a guideline. The topic of defending against SQL injection with a more low-level and specific meaning will be thoroughly examined in later chapters and sections of this book. In general, it's by enabling a security-driven approach to application coding that most vulnerabilities and security issues can be solved altogether. Also, including security controls during development can help save time and effort as reworking code can be much harder than writing the code from scratch with such controls that are included by design.

This chapter served as an introduction to, in a general sense, the basic topics behind SQL injection. The following is a summary of the main points to focus on in this first chapter so that you can memorize the main concepts we have mentioned thus far:

• SQL injection is a software weakness of SQL, a specific language and engine for interacting with database structures based on the relational model that treats data in a structured format using tables. It can allow malicious users to execute arbitrary commands, thus interacting with the database of an application in a way that is not originally intended by the application SQL injection can be used by attackers in many ways:

• SQL provides a simple language that can be used to perform operations on relational databases. SQL processes statements with simple structures in most cases. Some SQL statements are as follows:

  -SELECT, to extract information from the database returning records

  -INSERT, to insert records within the database

  -CREATE, to create data tables or databases

  -DROP, to delete entire tables or databases

  -DELETE, to delete records within the database

  -ALTER, to modify databases or tables

  Some of these statements can be more effective than others in terms of injection, but the most important is SELECT since it is the main enabler of injection and is the most common command used in applications. It is through the insertion of SQL commands within SELECT statements that almost all SQL injections take place.

  Some specific advanced SQL commands can also put different tables in relation based on, for example, common attributes. This is one of the main advantages of the relational model, which can separate data records into different tables that describe relations, while at the same time being connected and increasing the range of operations that can be performed.

• SQL-based systems can vary in terms of their implementation and can have some minor differences in terms of syntax (comments) and structure (default database names):

  - MySQL, an open source implementation, is the most popular version available. It can be considered as the basic SQL implementation for reference.

  - SQLite is designed to be a standalone version of SQL, storing the database in the same filesystem as the client application that's running. It uses the basic SQL syntax without major differences.

  - Oracle Database is one of the most popular SQL database systems in the enterprise environment as it also supports other database models, making it a multi-model system.

  - Microsoft SQL (MSSQL) server is another popular solution in the enterprise environment thanks to its integration with the Microsoft Windows environment, which is very widespread in the IT world.

  In general, the underlying engine works in the same way in terms of query processing, but there are some minor differences in terms of language and default database schema.

  For this recap, we have prepared a reference table highlighting some basic differences among the main SQL engines. This will help you memorize the main differences we described in this chapter:

  

SQL is used within coded applications to allow interaction with databases, which can be used to store and access data, while also providing means for obtaining authenticated access. Databases usually store access information, such as username and password, that's evaluated for matching purposes in a specific table of the database. For this reason, being a component of applications, SQL could be exploited by possible malicious actors who could abuse its expressive power.

• If there are no security controls in place, an application will evaluate every possible text string that is sent to it, thus possibly allowing a malicious user to insert arbitrary commands that weren't originally intended. To contrast the action of possible malicious users, the best solution would be to implement a security-focused approach in application development so that attacks such as SQL injection (and many other) are prevented by the use of security controls that implement a secure-by-design pattern that follows the existing best practices for secure coding. As a general blueprint, we can summarize some security-by-design principles into three major points:

  - Do not allow unnecessary special characters in queries so that SQL syntax cannot be abused.

  - Do not allow suspicious commands in queries by whitelisting only specific instructions.

  - Do not give the user too much freedom, thereby preventing a malicious user from injecting arbitrary code.

We will see more specific security principles in later chapters, both in the form of countermeasures and existing best practices for what concerns application security.

So, to sum this up, let's take a look at what we covered in this chapter. SQL works using relationships, and it accepts a wide range of commands. We've also seen that, in general, some of these can be abused by malicious attackers. For this reason, we should keep security in mind when designing and developing applications that rely on databases. This chapter gave you a taste of the main security issues and possible solutions.

The next chapter will focus on what a malicious attacker can do by taking advantage of SQL capabilities. We will provide examples of this, all while dealing with aspects related to non-relational databases.

This first chapter, despite being more abstract, is essential for focusing on the main concepts behind SQL injection. Of course, this just an introduction to what we are going to cover throughout this book, but with more concrete examples. Be sure to keep these topics in mind when dealing with the practical aspects of SQL injection.

Our journey into SQL injection has only just begun!

1. What is a database?
2. What is a relational database?
3. What is SQL? What is it used for?
4. Can you name some examples of SQL implementations in terms of database systems?
5. What does SELECT mean in SQL? Why is it so important?
6. Can you describe SQL injection in your own words?