SQL for SQL injection – a recap

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.