Skip to content

SQL Basics and Syntax

Before we go into the details of how SQLi works, it is useful to understand the way databases, and database queries are made.

Structured Query Language (SQL) is a method of requesting and manipulating information from databases. It provides a set of language primitives, allowing information to be retrieved, added, altered and deleted, through text like commands. While, as hackers, we do not need to use SQL for our day to day work, understanding how we communicate with databases is useful when we are trying to perform SQL injection.

Standards

While there is a Standard for SQL, different Database Management Systems (DBMS) do implement the language in slightly different ways. The standard commands (ie SELECT) will be there, but little things like case sensitivity will mess with you.

SQL and databases make up a whole module on most Computer Science courses. The activities this week will get you up to speed with the common functions we need as pen-testers, but if you want to learn more about how SQL works, google is your friend. You can also take a look at the further reading at the bottom of the page.

Note

If you want to play around with the examples you need to run the web trainer

Connecting to a MySQL Server over the command line

To practice making basic SQL queries, without the added complexity of doing it through a vulnerable web page, we can use a direct connection to a database.

If you want to follow along with the steps below you will need to install an SSH client, on your Kali installation you can use MySQL (apt-get install mysql-client). You will also need to have the Web Trainer running in docker.

Our first step is to connect to the SQL server. For this we need:

  • The address of the server we are connecting to: 172.18.0.1
  • The username: root
  • The password: cueh

Important

If using WSL use the address of your WSL VM to access the site

dang@dang-laptop ~$ mysql -h 172.18.0.1 -u root -p                     
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.7.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>

Using Built in Commands to View Database Structure

As well as making SQL queries, we can use the command line client, to view the database and table structure.

To see the databases available on the server we can use the show databases command

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testingdb          |
+--------------------+
5 rows in set (0.001 sec)

MySQL [(none)]>

We can then select a working database with the use command

mysql> use testingdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

We can get an idea of the tables with the show tables command

MySQL [testingdb]> show tables;
+---------------------+
| Tables_in_testingdb |
+---------------------+
| flag                |
| grade               |
| module              |
| student             |
+---------------------+
4 rows in set (0.001 sec)

And details on a specific table using the describe command

MySQL [testingdb]> describe student;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| firstname | varchar(50) | YES  |     | NULL    |                |
| lastname  | varchar(50) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.001 sec)

MySQL [testingdb]>

Note

The above commands are only available in the command line interface. When we are talking to the database via SQL then things like show tables are not valid.

However, they are good useful to know for debugging.

Depending on our permissions, we may be able to do some enumeration to get the database structure.

SQL Syntax

Once we have a connection to the database, and are looking at the right tables, we can start to make queries.

These examples are from the database on the Web Trainer, if you wish to follow along with the commands then follow the connection instructions above.

Getting information

The command for a query is SELECT <SOMETHING> FROM <SOMEWHERE>

We know from our initial connection that there are the following tables available:

  • testdb
  • flag

So we can ask for all the records in the student table using

MySQL [testingdb]> SELECT * FROM student;
+----+-----------+--------------+
| id | firstname | lastname     |
+----+-----------+--------------+
|  1 | Sam       | Vimes        |
|  2 | Cecil     | Nobbs        |
|  3 | Fred      | Colon        |
|  4 | Carrot    | Ironfoundson |
|  5 | Cheery    | Littlebottom |
+----+-----------+--------------+
5 rows in set (0.001 sec)

If we wanted all module we could use

MySQL [testingdb]> SELECT * FROM module;
+----+------------+-------------------+
| id | modulecode | moduletitle       |
+----+------------+-------------------+
|  1 | 245CT      | Ethical Hacking 1 |
|  2 | 207SE      | Operaing Systems  |
|  3 | 219SE      | Digital Forensics |
+----+------------+-------------------+
3 rows in set (0.001 sec)

A side note about capitalisation

Most of the time SQL doesn't care about the capitalisation. Thus the following commands are all equivalent.

  • SELECT * FROM TABLE
  • SELECT * from TABLE
  • select * from table

However, convention is the SQL keywords (SELECT, FROM) are capitalised, so sticking with UPPERCASE for SQL statements, and lowercase for table names, can help stop you from tripping over yourself.

Also some DMBS (for example sqlite) are case sensitive when it comes to the data that is stored in them, so the tables: Person, PERSON and person are different.

Filtering Things

We may not always want to return every record from a database, so we can filter the results to only return relevant records. In this case we append at WHERE clause to the SQL statement. (it's at this point we start to wonder why DBA's (DataBase Administrators) are paid so much)

So to find all students called Sam:

MySQL [testingdb]> SELECT * FROM student WHERE firstname="Sam";
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | Sam       | Vimes    |
+----+-----------+----------+
1 row in set (0.001 sec)

It's slightly more complicated if we need to use foreign keys here, but still doable. For example, to find all Grades related to Sam, we know from the above example the studentid of this user is 1, therefore:

MySQL [testingdb]> SELECT * FROM grade WHERE studentid = 1;
+----+-----------+----------+-------+
| id | studentid | moduleid | grade |
+----+-----------+----------+-------+
|  1 |         1 |        1 |    80 |
|  2 |         1 |        2 |    60 |
|  3 |         1 |        3 |    50 |
+----+-----------+----------+-------+
3 rows in set (0.001 sec)

We can also reduce the search to show only certain columns by specifying them instead of using *, for example:

MySQL [testingdb]> SELECT firstname, lastname FROM student;
+-----------+--------------+
| firstname | lastname     |
+-----------+--------------+
| Sam       | Vimes        |
| Cecil     | Nobbs        |
| Fred      | Colon        |
| Carrot    | Ironfoundson |
| Cheery    | Littlebottom |
+-----------+--------------+
5 rows in set (0.001 sec)

Unions

Unions are used to combine two or more select statements, and return all the results as one set. This makes unions incredibly useful for SQL injection, as we can append the data we are after to the results of a query.

  • UNION remove duplicate rows
  • UNION ALL does not remove duplicate rows

There is a massive caveat here: All Tables need the same number of columns

For example, let's merge the Student and Grade table together:

MySQL [testingdb]> SELECT id, firstname, lastname FROM student UNION ALL SELECT studentid, moduleid, grade FROM grade;
+------+-----------+--------------+
| id   | firstname | lastname     |
+------+-----------+--------------+
|    1 | Sam       | Vimes        |
|    2 | Cecil     | Nobbs        |
|    3 | Fred      | Colon        |
|    4 | Carrot    | Ironfoundson |
|    5 | Cheery    | Littlebottom |
|    1 | 1         | 80           |
|    1 | 2         | 60           |
|    1 | 3         | 50           |
|    2 | 1         | 50           |
|    2 | 2         | 80           |
|    2 | 3         | 60           |
|    5 | 1         | 70           |
|    5 | 2         | 80           |
|    5 | 3         | 95           |
+------+-----------+--------------+
14 rows in set (0.001 sec)

The Union function is intended to be used to find objects that match in several database tables (where the rows are similar). Because of this the output can be a little tricky to parse.

We have ended up with 2 Queries combined:

  1. The Id, Firstname and Lastname of all Students.
    • These also form the Headers for the result.
    • But look similar to the queries we have seen before
  2. Query on the Grade table
    • The Rows have been transposed onto the Id (studentId), Firstname (moduleId), lastname (grade) columns of the Student query
    • But the data is the same as we have seen before.

Functions Relevant to SQLi

Let's bring this all back to SQL injection (ie the point of it all, at least from a hacking perspective).

Comments

Comments are represented either by # or -- These can allow us to "escape parts" of the query when it is passed to the DBMA. ie if we can inject a comment before the WHERE clause we get to see all the users:

MySQL [testingdb]> SELECT * from student; #WHERE name="Sam";
+----+-----------+--------------+
| id | firstname | lastname     |
+----+-----------+--------------+
|  1 | Sam       | Vimes        |
|  2 | Cecil     | Nobbs        |
|  3 | Fred      | Colon        |
|  4 | Carrot    | Ironfoundson |
|  5 | Cheery    | Littlebottom |
+----+-----------+--------------+
5 rows in set (0.001 sec)

Logical Operators

The OR logical operator is a good friend here. As SQL will evaluate this as part of the query we can try to force the result to always be true using OR 1=1 and work around filtering.

For example:

MySQL [testingdb]> SELECT * from student WHERE firstname="Sam";
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | Sam       | Vimes    |
+----+-----------+----------+
1 row in set (0.001 sec)


mysql> #Bypass filter with 1=1
MySQL [testingdb]> SELECT * from student WHERE firstname="Sam" OR 1=1;
+----+-----------+--------------+
| id | firstname | lastname     |
+----+-----------+--------------+
|  1 | Sam       | Vimes        |
|  2 | Cecil     | Nobbs        |
|  3 | Fred      | Colon        |
|  4 | Carrot    | Ironfoundson |
|  5 | Cheery    | Littlebottom |
+----+-----------+--------------+
5 rows in set (0.001 sec)

Concatenation

Concat allows us to combine columns together into one string -- you can see where this would be useful with UNION ALL queries:

MySQL [testingdb]> SELECT id, CONCAT(lastname, " ", firstname) FROM student;
+----+----------------------------------+
| id | CONCAT(lastname, " ", firstname) |
+----+----------------------------------+
|  1 | Vimes Sam                        |
|  2 | Nobbs Cecil                      |
|  3 | Colon Fred                       |
|  4 | Ironfoundson Carrot              |
|  5 | Littlebottom Cheery              |
+----+----------------------------------+
5 rows in set (0.001 sec)

Limit

Limit can be useful with things like passwords. Consider the following SQL, used to validate a password:

SELECT * FROM user WHERE username = <whatever> AND password = <pwhash>;

A simple check may just show if more than one row is returned (ie if data comes back, both the username and password must be correct).

A slightly better result would be to check if ONE AND ONLY ONE row is returned, as this means that we can detect attackers using the OR 1=1 Trick to return every result on the database. Of course, as attackers we can get around that filtering using a LIMIT clause.

Order By

We may want to know the number of columns in a table. We can use order by to get an indication, by increasing the number until the ORDER BY command fails.

We could also use this to try and guess at column names, making use of information gathered during the reconnaissance process. For example on a corporate website, we may get a list of employees, and the department they work for. WE could guess the other information that may be stored with an employee object, such as email address.

#Row 4 (grade exists)
mysql> SELECT * FROM grade ORDER BY 4;
+----+-----------+----------+-------+
| id | studentid | moduleid | grade |
+----+-----------+----------+-------+
|  3 |         2 |        1 |    60 |
|  2 |         1 |        2 |    75 |
|  1 |         1 |        1 |    85 |
|  4 |         2 |        2 |    85 |
+----+-----------+----------+-------+
4 rows in set (0.00 sec)

#No row 5
mysql> SELECT * FROM grade ORDER BY 5;
ERROR 1054 (42S22): Unknown column '5' in 'order clause'
mysql>

In summary

In this step we have covered the basics of SQL syntax, and see how we can interact directly with a database to obtain information.
While we may not interact with the database as hackers, (usually the database server is not directly available), understanding SQL syntax is useful when trying to perform SQL injection attacks.

Next we will examine SQL Injection and use these techniques to modify queries on vulnerable website.

Further reading

Back to top