A quick SQL primer for SQL injections
While there are many other tutorials and resources out there that cover SQL, I wrote this blog post to give you a quick SQL primer for SQL injections, because there are a few facts that will help make that learning journey easier.
So this is not meant as a complete explanation of SQL. 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. It allows us to read, write, and edit data, and it also allows us to configure or manage the database engine being used for our application(s).
This makes SQL a very powerful tool because it’s not just something database administrators use. Developers may need to build their own databases or tables and will more likely than not also need to know how to manipulate data in their databases.
Beyond being a helpful tool to build applications, it can also be a tool used against those very same applications. While we won’t introduce SQL injections in this post, but instead in another one, many of the concepts explained here directly translate to performing SQL injections.
Setting up an example database
An SQL primer for SQL injections wouldn’t be complete without some hands-on practice! 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:
- Users
- 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 15 people who complete this course!', '$0', '10'),
(346, 'Vaccuum', 'The most powerful battery-powered vaccuum cleaner', '$499.99', '44');Code language: JavaScript (javascript)If you’re following along in SQLFiddle, make sure to click on “Build Schema” after copy/pasting the above!
The Users table contains information about all the users of your application including:
- ID
- 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;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: JavaScript (javascript)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: JavaScript (javascript)
We could also run UPDATE statements, to update our data.
UPDATE Products SET ProductPrice='0' WHERE id='346';Code language: JavaScript (javascript)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: JavaScript (javascript)We can DROP tables or even entire databases:
DROP TABLE Users;and a whole lot more…
Database Metadata Tables – Helpful info for SQL Injections
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 (for B-trees) |
| 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: JavaScript (javascript)For MySQL we could use something like this:
SELECT table_name from information_schema.tables
WHERE table_type = 'BASE TABLE' and table_schema = database();Code language: JavaScript (javascript)While for PostgreSQL it could just be
SELECT * FROM information_schema.tables;Code language: CSS (css)And in Oracle:
SELECT table_name FROM dba_tables;Concluding our quick SQL primer for SQL injections
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.
As we complete this blog post, go ahead and move on to the next in order to learn the concepts of SQL injections.
Responses