What is SQL?

Slides used for this lesson.

In this lesson, we’re going to quickly go over what SQL is as a refresher, because understanding the inner workings of SQL is critical to understanding SQL injections. 

This is not meant as a complete explanation of SQL, so if you have zero knowledge of SQL, you should probably take a proper course for that first. Think of this as a crash course on SQL in just a few minutes in case you haven’t touched it in a while.

What is SQL?

SQL is a language designed for communicating with databases that store our data. So it allows us to read, write, and edit our data, and it also allows us to configure or manage the database engine.

Setting up an example database

I’ll be using a website called SQLFiddle in order to demonstrate, and this is a free resource so feel free to follow along.

So let’s say that we have two tables in our database:

1. Users
2. Products

Here’s the SQL I used to build the schema in my SQLFiddle example:

CREATE TABLE Users (
    ID int,
    Email varchar(255),
    Password varchar(255),
    RegistrationDate varchar(255),
    PhysicalAddress varchar(255)
);

CREATE TABLE Products (
    ID int,
    ProductName varchar(255),
    ProductDescription varchar(255),
    ProductPrice varchar(255),
    Quantity varchar(255)
);

INSERT INTO Users (ID, Email, Password, RegistrationDate, PhysicalAddress)
VALUES (159, '[email protected]', 'strongpassword12345', '7-1-2020', 'Stavanger, 4006 Norway'),
(11, '[email protected]', 'b3stp@assw0rd', '5-5-2019', 'SQLFiddle, 4006 USA');

INSERT INTO Products (ID, ProductName, ProductDescription, ProductPrice, Quantity)
VALUES (13, 'Cybr SQL Injection T-Shirt', 'Really cool t-shirt for the first 10 people who complete this course!', '$0', '10'),
(346, 'Vaccuum', 'The most powerful battery-powered vaccuum cleaner', '$499.99', '44');Code language: SQL (Structured Query Language) (sql)

The Users table contains information about all the users of your application including:

• ID
• Email
• Password
• RegistrationDate
• PhysicalAddress

And the Products table contains information about all the products in your online store including:

• ID
• ProductName
• ProductDescription
• ProductPrice
• Quantity

Examples of SQL queries

To pull data from the Users table, we might run this query:

SELECT * FROM Users;Code language: SQL (Structured Query Language) (sql)

And it will pull all columns from Users.

Or for just the registration date of a specific user, we could run:

SELECT RegistrationDate FROM Users WHERE Email ='[email protected]';Code language: SQL (Structured Query Language) (sql)

In order to run these queries, in some cases, we have to use information submitted by our users or our application.

For example, if a user is checking out our product with ID 346, our application may request it like this:

const http = new XMLHttpRequest()

http.open("GET", "https://url.co/v1/products/346")
http.send()

http.onload = () => console.log(http.responseText)Code language: JavaScript (javascript)

and that API URL is running this SQL query:

SELECT * FROM Products WHERE ID='346';Code language: SQL (Structured Query Language) (sql)

We could also run UPDATE statements, to update our data.

UPDATE Products SET ProductPrice='0' WHERE id='346';Code language: SQL (Structured Query Language) (sql)

SQLFiddle doesn’t allow us to UPDATE tables from this window, but this would work in practice.

We can use UNION statements to combine results from multiple SELECT statements into a single result set:

SELECT Email,Password FROM Users UNION SELECT ProductName, ProductPrice from Products;Code language: SQL (Structured Query Language) (sql)

We can DROP tables or even entire databases:

DROP TABLE Users;Code language: SQL (Structured Query Language) (sql)

and a whole lot more…

Database Metadata Tables

The database engines also typically have tables that contain general information and metadata, such as keeping track of all tables or the database’s schema — basically, the essentials for the database to keep running properly.

For different database engines, it’s a bit different:

• SQLite → sqlite_master
• MySQL → information_schema
• PostgreSQL → information_schema
• Oracle → dba_tables

For example, the sqlite_master table looks like this:

Column Name	Description
type	The type of database object such as table, index, trigger, or view
name	Name of the database object
tbl_name	The table name that the database object is associated with
rootpage	The root page
sql	SQL used to create the database object

So to get a complete list of all tables in a database for SQLite, we could run this query:

SELECT name FROM sqlite_master
WHERE type='table'ORDER BY name;Code language: SQL (Structured Query Language) (sql)

For MySQL we could use something like this:

SELECT table_namefrom information_schema.tables
WHERE table_type = 'BASE TABLE' and table_schema = database();Code language: SQL (Structured Query Language) (sql)

While for PostgreSQL it could just be 

SELECT * FROM information_schema.tables;Code language: SQL (Structured Query Language) (sql)

And in Oracle:

SELECT table_name FROM dba_tables;Code language: SQL (Structured Query Language) (sql)

Conclusion

All of what we talked about will be important as we perform SQL injections, because what allows our applications to manipulate database data can also be used to exploit those very same databases. 

That’s right, seemingly harmless queries can result in simple — all the way to massive — exploits that take down applications, or extract sensitive information.

Let’s complete this lesson and move on to the next, where we will start to explain how SQL injections can be carried out.