5.1. Basics of SQL

 

There are various database management systems (DBMS). Some of them are free, while others are commercial. Below is a list of the most popular databases:

  • MySQL
  • Oracle Database
  • Microsoft SQL
  • PostgreSQL
  • SQLite

All of them use the SQL language, whose syntax may vary slightly for each DBMS.

PostgreSQL and MySQL are free and widely used in many websites. They are also installed on Kali. Both DBMS are quite similar to each other, including their syntax. Therefore, we will practice on MySQL when it comes to composing queries.

To practice your skills, I have prepared a file containing some data. Your task is to import this data into the system.

 

Creating a Database in MySQL and Importing Data

In Kali, open the terminal and start the MySQL service with the command:

sudo service mysql start

Then, connect to the DBMS itself:

sudo mysql -u root

We are connecting to it as the root user. By default, there is no password set. Run the command:

show databases;

Note that there is a semicolon at the end of the command:

Connecting to MySQL and listing databases

The command displays a list of existing databases. By default, MySQL already has some databases and their tables. We will create our own database:

CREATE DATABASE sql_tutorial;

You can choose any name you like. If you display the list of available databases again, you will see the newly created database:

Display of newly created database in MySQL

Now exit the MySQL mode using the command exit; and import the data into our database:

sudo mysql –u root sql_tutorial < /root/Desktop/basics.sql

Connect to the DB again and select the created database using the following command:

use sql_tutorial;

The terminal prompt will now display the database name instead of 'none':

Changing terminal prompt in MySQL

Execute show tables; to display all the tables in this database:

Displaying a list of tables in database

From this point, we can fully utilize the database and its tables.

 

Connecting to PostgreSQL

PostgreSQL is already installed on Kali, so you can also practice with it and test various combinations. To start, launch the PostgreSQL service:

sudo service postgresql start

Then, connect to the database as the 'postgres' user:

sudo -u postgres psql

To display the available databases, execute the command \l:

Displaying a list of databases in PostgreSQL

To connect to the desired database, use the command \c database_name:

Connecting to specific DB of PostgreSQL

To display the list of tables in the current database, execute \d:

List of PostgreSQL database tables

The syntax of SQL queries for creating new databases and their tables is similar to MySQL. All subsequent SQL statements presented in the article can be used on both DBMSs.

 

Data Retrieval

Task: Retrieve a list of users (name, surname) from the “users” table.

Solution: To retrieve data, we need to create a query in the form of SELECT column_name FROM table_name.

It means "SELECT data in the column (column name) FROM the table (table name)." Execute the command below:

SELECT name, surname FROM users;

Execution of SQL query in MySQL

As you can see from the query above, to retrieve more data, you can specify the appropriate column names separated by commas.

 

Retrieving Data Based on Conditions

Task: Retrieve a list of users whose age is over 40 years old.

Solution: We will extend the SELECT column_name FROM table_name structure with a new operator, WHERE column_name = value. Additionally, we can use operators such as =, <, >.

Now, let's execute the query:

SELECT name, surname, age FROM users WHERE age > 40;

SQL qieries with condtions

If you need to specify multiple conditions, you can combine them using the AND (logical AND) or OR (logical OR) operators. For example, let's create the following conditions: age over 40 AND salary of 50,000 or higher. In this case, our query would look like this:

SELECT name, surname FROM users WHERE age > 40 AND salary > 50000;

Here, AND means that all conditions must be met.

Another example: let's retrieve users who are younger than 30 or older than 40. Our query would be as follows:

SELECT name, surname FROM users WHERE age < 30 OR age > 40;

Here, OR means that it's enough for at least one of the conditions to be met (OR the first condition OR the second condition).

 

Retrieving data by a keyword or pattern

Task: Get a list of users whose email addresses end with mail.com.

Solution: For this operation, we will use the LIKE operator along with a special wildcard % (percent sign). This wildcard represents any characters of arbitrary length. The query will look like this:

SELECT name, surname, email FROM users WHERE email LIKE '%@mail.com';

The LIKE '%@mail.com' condition will match any email address that ends with @mail.com:

SQL query with condtion LIKE

Now let's make the task more complex. This time, we need to find users whose email addresses still contain the keyword @mail but belong to different domains, such as mail.com, mail.ru, mail.net, and so on. The query will look like this:

SELECT name, surname FROM users WHERE email LIKE '%@mail.%';

I hope you understand how this wildcard mask works.

 

Sorting Data

Task: Retrieve all users in ascending order of their salary.

Solution: To start, try running the following query in the terminal:

SELECT name, surname, salary FROM users;

By default, all data will be displayed in the order they were recorded in the database. To display ordered records, execute the following query:

SELECT name, surname, salary FROM users ORDER BY salary ASC;

After the ORDER BY clause, we specify the column by which to sort the data. The ASC (Ascend) operator means sorting in ascending order, but you can omit it as it's always used by default. For descending order, you can use the DESC (Descend) operator.

By the way, after ORDER BY, you don't necessarily need to specify the column name; you can use its ordinal number in the query. Let me explain with an example.

To display the name, salary, age, and work experience, we can use the query:

SELECT name, salary, age, experience FROM users;

Here, we have 4 columns displayed. We can use their ordinal numbers for sorting the data. Each column in the query has its ordinal number: name - 1, salary - 2, age - 3, experience - 4. Now, after ORDER BY, we can specify the ordinal number instead of the column name:

SELECT name, salary, age, experience FROM users ORDER BY salary == SELECT name, salary, age, experience FROM users ORDER BY 2;

Or

SELECT name, salary, age, experience FROM users ORDER BY age == SELECT name, salary, age, experience FROM users ORDER BY 3;

Additionally, you can sort the output by multiple parameters simultaneously. For example, the query

SELECT name, salary FROM users ORDER BY name ASC, salary DESC;

will first sort names alphabetically into groups and then sort within each group by salary. Try running various combinations in the terminal.

The technique of sorting by column ordinal numbers is used in SQL attacks to determine how many columns an SQL query outputs on the server. This is necessary to send our modified query to the server.

 

Nested Constructs

Sometimes, solving a particular task requires creating more complex constructs. These include intricate expressions made up of nested queries. The result of one query can significantly affect the outcome of other queries. Let's create a simple example to help you understand how nested expressions work and how to construct them.

Task: Create a list of countries with a population smaller than that of Brazil.

Solution: Our query would look like this:

SELECT country, population FROM countries WHERE population < X;

where X is the population of Brazil. Information about Brazil is also stored in the database and is a variable, meaning it can change based on specific conditions. So, first, let's determine the population of Brazil:

SELECT population FROM countries WHERE country='Brazil';

Then, we substitute the result of this query in place of X:

SELECT country, population FROM countries WHERE population < (SELECT population FROM countries WHERE country='Brazil');

This nested construct allows us to use the result of one query within another query, making it more flexible and adaptable to changing conditions:

Execution of nested SQL queries

This was the simplest and most illustrative example. When conducting SQL attacks, you will also have to construct complex expressions, but the principle remains the same for all of them. Simply compose one simple query, and then for each unknown variable, create other simpler queries. Special symbols and functions are used to combine multiple queries into a single expression. We will explore some of these in more detail later in this course.

 

Combining Queries (UNION)

In some cases, you may need to execute two independent queries and display the results in a single output stream. For example, display a list of names of users working in Russia and a list of countries belonging to Europe.

Both queries would look like this:

SELECT name FROM users WHERE country='Russia';

SELECT country FROM countries WHERE continent='Europe';

As you can see, both queries are completely independent of each other, and they are querying different tables. However, we need to execute both queries simultaneously and display both results at once.

You can easily do this using the UNION operator, which allows you to combine both queries into a single expression:

SELECT name FROM users WHERE country='Russia' UNION SELECT country FROM countries WHERE continent='Europe';

UNIONed SQL queiries

The question may arise: "Why combine 2 independent queries?"

The UNION operator is often used in SQL attacks when a malicious query is added to an existing one, and both queries are executed simultaneously. If the result of the first query is displayed in the browser, the output of the second query will also be displayed in the browser.

 

Inserting data

Task: Enter a new user with the following data into the users table:

Name (name): Valentin

Surname (surname): Levin

Age (age): 5

City of residence (city): Dubai.

Solution: Entering new data is done as follows:

INSERT INTO table (column_1, column_2) VALUES ('value_1', 'value_2');

In our case, the query would look like this:

INSERT INTO users (id, name, surname, age, city) VALUES ('21', 'Valentin', 'Levin', '5', 'Dubai');

Why is it important to be able to add new records to the database?

Many websites authenticate users using a database. If you can inject a new user with administrator privileges into the database, you can easily gain full control over the system.

 

Deleting data

Task: Delete the user created in the previous example.

Solution: The delete query looks like this:

DELETE FROM table WHERE condition;

To delete the user from the previous example, execute the query:

DELETE FROM users WHERE name='Valentin' AND surname='Levin';

You can add another condition like age to avoid deleting a similar user.

How can deleting database records be useful in website hacking?

Deleting records or the entire table/database is more destructive in nature.

 

Wildcards and Comments

In all DBMS, the use of so-called wildcards is supported, which allows you to create more flexible queries based on keywords. For example, to display all the data from a table, you can simply use the asterisk (*) mask instead of listing the columns:

SELECT * FROM countries;

This will retrieve all columns from the "countries" table:

Demonstration of masks in SQL qieries

The query is quite simple and short, but it displayed all the data from the table. A list of some masks/wildcards for each DBMS is provided below in the table:

Symbol

DBMS

Description

Example

%

Oracle

MySQL

PostgreSQL

Matches any character or string of arbitrary length

LIKE ‘%mail.ru’

Resulting output:

This email address is being protected from spambots. You need JavaScript enabled to view it.

This email address is being protected from spambots. You need JavaScript enabled to view it.

This email address is being protected from spambots. You need JavaScript enabled to view it.

*

MS SQL

_ (underscore)

Oracle

MySQL

PostgreSQL

Matches any single character

LIKE ‘_mail’

Resulting output:

mail

email

gmail

?

MS SQL

- -

Oracle

PostgreSQL

MS SQL

The code and commands after this symbol are treated as comments and are not executed

# sleep(10);

Resulting output:

The delay command will be ignored (not executed)

#

MySQL

Comment characters are used in all SQL exploits, as they allow you to insert your SQL code correctly without causing execution errors on the server.