An Introduction to SQL

Most modern databases provide access to the data using a language called Structured Query Language or SQL. A reviewer involved with the review of a database should be familiar with SQL.

Structured Query Language (SQL) is an ANSI standard that permits users to access and manipulate databases. SQL statements can retrieve and update data in a database, as well as modify the structure of a database. The basics of SQL include a Data Manipulation Language (DML) and Data Definition Language (DDL).

DML includes SELECT, UPDATE, DELETE and INSERT INTO statements.
DDL includes CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX and DROP INDEX statements.

These statements are semantically simple.

• CREATE TABLE creates a database table
• ALTER TABLE alters a database table
• DROP TABLE deletes a database table.

Indexes may be created or dropped using the CREATE INDEX and DROP INDEX statements.

UNION ALL SELECT
This SQL Statement can return data from different tables. An attacker will use this to access information contained within tables that they should not be able to access.

INSERT INTO
Adding this clause to the end adds additional data to a table of your choice. For example, you might add a record to the table that controls authentication therefore adding another username and password that you now have knowledge of to access the database.

JOIN
JOIN allows provides the ability to select data from more than one table. Usually data is related to between tables through the use of a primary or unique key. The tables are joined through a WHERE clause condition.

UNION
The UNION command adds the ability to extract data from two tables; unlike JOIN, it provides the ability to simply “stack” the two result sets on top of each other. The fields do usually need to be of the same data type.

Key Database terms
It is essential that an reviewer understand the following terms associated with databases:

• Database

A database is a grouping of files where actual data and database parameters are stored. Each Database can be connected to by one or more independent instances.

• Data type

Every field has a data type. There are diverse numeric data types such as integer, double integer, decimal, as well as character and block data types.

• Field

A field is a data structure for a single piece of data. (e.g. “first_name”, “last_name” and “phone_number” are all fields). Each database column is a field.

• Instance

An instance is a set of memory and processes that make up an active part of a functional database. An Instance includes the memory buffers (working storage) and background processes. (Oracle uniquely identifies each instance using a SID [System Identification]).

• Joins

Tables can be connected to other tables that have additional information. Usually tables are connected through some kind of key such as a primary or foreign key.

• Primary Key

The primary key is a field that uniquely identifies each record.

• Record

A record is a group of fields that are relevant to a particular topic.

• Stored Procedures

A set of SQL statements can be grouped together in one file (program) with an assigned name. This set of statements is then stored in the database in a compiled form so that it can be shared by a multiple programs.

• Table

Organized group of fields used to store information.

• View

A view is a way of presenting the data in a database.