The syntax and logic of SQL

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.