Hello! Starting to study information security in general and penetration testing in particular, you will soon hear such a term as “SQL injection”. And it will surely interest you. And after a little understanding, you will find out that SQL injection is, if you do not go into details, one of the ways to hack sites.
The essence of which is to inject arbitrary SQL code into a query in order to manipulate the database. And it was at this stage that the most important, or rather a beginner’s mistake, buried itself. And it lies in the fact that many people begin to understand what SQL injection is from the SQL injection itself. Without bothering to figure out what a database is.
And even more so without learning, at least a little, the SQL language itself. From the outside it looks like if a person learned to write without learning the letters. But, we will not repeat that and will approach this issue correctly, ie systemically.
- What is a database
- What is SQL
- Installing MySQL
- Creating a database and tables in SQL
- DROP
- CREATE TABLE
- SHOW TABLES
- Adding data to a table in SQL
- Retrieving data from a table in SQL. SELECT
- Sorting data
- ORDER BY
- Concatenating tables in SQL
- Some nuances of the SQL language
What is a database
Contents
So what is a database? This term is widely used in everyday life. This is not surprising, because in essence, a database is just a repository of ordered information. We, in relation to SQL, will consider a database as a container for information in the form of one or more files. You also need to know that the software that manages the database is called a DBMS, i.e. a database management system.
Another point to understand is the structure of the database. Nothing complicated here either. The database consists of three elements: table, row, and column. Well, a few words about them.
A table is the main building block of a database. Moreover, one database can contain several tables. From a security and usability point of view, it is considered correct to store only a certain type of data in each table. When creating a table, you need to give it a unique name. There cannot be tables with the same names in one database, you also need to indicate which columns it consists of and also assign names to them.
Column (column) is a structural element of a table. For each column, you need to specify what kind of data is allowed to be stored in it. This can be text, numbers, ranges of numbers, files, booleans, etc.
Each record in the table has a separate row. Those. in fact, a row is a separate record in a table. Each row must be assigned a so-called “ primary key ” – this is a unique identifier for the row. It allows you to later refer to this particular line. In principle, any column can be the primary key. The main thing is to observe some rules: it must be unique for each line and must have an assigned value (it cannot be null), and it cannot be changed.Note:Sometimes you may come across the term – relational database. It just means that tables in this database can refer to each other and interact with each other. There are three types of table relationships: one-to-many (the most common), one-to-one, and many-to-many (but less common).
What is SQL
We sort of figured out what databases are. It remains to figure out what to do with them. But fortunately, not everything is as complicated as it might seem at first glance. In order to work with databases, the SQL language was invented.
SQL (Structured Query Language) is a structured query language. And it allows you to perform any action with data and databases: from simple data entry and creating tables to administering the DBMS server. At the same time, he very successfully combines simplicity and enormous possibilities. To understand how SQL works, imagine that you are talking and giving commands to a person who is very executive, but knows only a few words. And, now, if you give him a command in the words he knows, he will fulfill it perfectly. In general, the point is that creating queries in SQL is very similar to a conversation, but about everything in order.
I think at this stage it has already become clear that knowledge of SQL is extremely useful. Therefore, in this article we will pump it to a level sufficient for comfortable work with databases.
Installing MySQL
First, we need a DBMS. We’ll use MySQL (just for example), but it will work the same way in any other DBMS.
First you need to install MySQL. You can download it from the official website https://dev.mysql.com/downloads/mysql/
Or install with the command:
sudo apt install mysql-server |
After installation, MySQL will automatically start, you can check this with the command:
sudo service mysql status |
if suddenly, for some incomprehensible reason, you do not start, run manually:
sudo service mysql start |
The next step we need, to slightly increase the security of our database, change some of the standard settings and delete any test records. To do this, enter the command:
sudo mysql_secure_installation |
The first step we will be asked to come up with a password. We select Y and enter the password twice, well, answer all the following questions with Y.
Now let’s connect to the server and continue:
sudo mysql -u root -p |
After entering the password, we will see the MySQL prompt. This means that we can start working with databases.
To make the process of learning SQL more visual, we will create a database, for example, of users of some conditional site. And we will work with her. Our database will contain some conditional tables that we come up with in the process.
Creating a database and tables in SQL
The CREATE DATABASE command is used to create a new database. Then the name of the new database is indicated and also remember that the command in SQL must end with ”; “
CREATE DATABASE all_users; |
As you can see we said: CREATE DATABASE all_users. Now we need to say that we want to work with this base. For this there is the USE command
USE all_users; |
DROP
If the database needs to be dropped , use the DROP DATABASE dbname ; By the way, you can delete anything you want with the DROP command, not just databases. For example, to drop a table, use the DROP TABLE table_name command ; And if you need to delete all data from the table, but leave the table itself, that is, the command: TRUNCATE TABLE table_name ;
CREATE TABLE
Since the database consists of tables, now we go directly to creating tables. To do this, use the CREATE TABLE command. After which you need to specify the name of the new table. And then in brackets, separated by commas, list which columns and data types in these columns will be in the table. For example, let’s create a table with usernames and their status on the site:
CREATE TABLE users_nick (id INT AUTO_INCREMENT PRIMARY KEY, nick NVARCHAR (64) NOT NULL, status NVARCHAR (32)); |
Now let’s see what we created there:
SHOW COLUMNS FROM users_nick; |
Well, let’s take a closer look at the command itself that we entered:
- CREATE TABLE users_nick – create a table named users_nick;
- id INT AUTO_INCREMENT PRIMARY KEY – we named the first column of the table id. We assigned it the data type INT i.e. integer values (this means that there can be no fractions, letters, symbols – nothing but integers). With the AUTO_INCREMENT command, we asked MySQL to independently fill in the rows of this column when new records were added to the table, while adding +1 to each new record. In other words, we created a counter to make the values in this column unique. And we need unique values to apply the PRIMARY KEY parameter i.e. assign values from this column as primary keys – unique identifiers of table rows. Using the values from this column, we will later refer to the rows in this table;
- nick NVARCHAR (64) NOT NULL – we named the second column nick. And we assigned it the NVARCHAR type, this is string data of variable size, while limiting the size of the value to 64 bytes. By the way, some “reputable” Internet schools in their courses claim that the value in brackets is the number of characters. This is not entirely true, or rather it is true for single-byte encodings such as LATIN. Because there is 1 character = 1 byte, but we are using UTF-8 and this is a multibyte encoding, which means that one character can take more than one byte. So just remember – the value in parentheses is the size of the string in bytes. With the NOT NULL command, we indicated that the value in this column cannot be NULL, i.e. cannot be empty.
- status NVARCHAR (32) – we named the third column status. The type is string data of variable size, size is 32 bytes, and since we did not specify NOT NULL, this column can be empty.
We will also create a table in which the possible user statuses will be listed:
CREATE TABLE users_status (id INT AUTO_INCREMENT PRIMARY KEY, status NVARCHAR (32)); |
SHOW TABLES
To view all the tables we have created, use the command:
SHOW TABLES; |
If you need to delete a single record from the table, then use the DELETE command:
DELETE FROM users_nick WHERE id = 1; |
Here we said: DELETE FROM THE TABLE users_nick WHERE id IS 1.
Adding data to a table in SQL
Now we need to fill in the data of the tables we have created i.e. add lines. To do this, use the INSERT command. It can add rows in three ways:
– full line
– part of a line
– query result
Let’s start with the status table:
INSERT INTO users_status (status) VALUES ('admin'); |
translating into human we said: Add the value admin to the users_status table in the status column. In my example, the name of the column and the value that we add in brackets can be omitted. Because we are adding one value to one column. But as a rule, you need to add to several columns – different values, for this we list the columns in brackets, separated by commas, and the values that we want to enter into the corresponding column after the VALUES command. Values must always be in single quotes. And do not forget that the values must be specified in the same order in which we listed the columns, but this order does not have to correspond to the order of the columns in the table:
INSERT INTO users_nick (id, nick) VALUES ('1', 'Pulse'); |
As you can see, we have added only part of the line, omitting the status column. In such a situation, a NULL value will be written there, i.e. empty, but only if we did not specify the NOT NULL parameter for this column. Well, by the way, the id column for the correct one would also have to be skipped, because it will be automatically filled anyway.
If suddenly you decide to change some value in the table, the UPDATE command is used for this:
UPDATE users_status SET status = 'administrator' WHERE id = 1; |
In essence, we said: UPDATE the value in the users_status table IN the status COLUMN to administrator WHERE id = 1. Using WHERE, we set conditions for finding the record we need. Here we just needed our identifiers, thanks to which we select exactly the required row. By the way, if you do not specify the condition, using WHERE we will update the entire value in the table. You don’t need to do this.
Retrieving data from a table in SQL. SELECT
Getting data from a table is probably one of the main functions that we will use more often than others. To retrieve data from one or more tables, use the SELECT command. In order for this command to be executed correctly, you need to specify two parameters: what to get and where from.
So, for example, to get all the usernames from our table, we need to write:
SELECT nick FROM users_nick; |
Thus, we saw a list of users from the nick column of the users_nick table. By the same principle, we can display the data of several columns, specifying them separated by commas. Or if we want to see our entire table, then instead of listing the columns, we need to put the *
SELECT * FROM users_nick; |
In human terms, it would sound like this: CHOOSE everything FROM users_nick.
Sorting data
As you can see, everything is very simple, and difficulties will begin if there are several thousand records in the table. And besides the obvious complexity associated with the size, the records will be displayed in an unpredictable order. This is due to how the DBMS uses memory, how and when records were added or edited. But why this happens to us is not very important, but what is important is what to do with it. Therefore, you need to learn how to sort the output data.
In order to understand the sorting, I added several records to our users_nick table. The easiest way to sort is using conditions, for example:
SELECT * FROM users_nick WHERE status = 'user'; |
In this request, we asked the piece of iron to show us the records in the users_nick table in which, in the status column, the value user is assigned. Do not forget that you can use logical operators (AND / OR) and mathematical comparison operators (=, <,>, <=,> =, <>) in the WHERE clause. Well, no less useful thing is pattern search. Because we do not always know exactly which record we need, the LIKE command is used for this:
SELECT * FROM users_nick WHERE status LIKE '% adm%'; |
In this way, we found a user whose status column contains the letters adm. The% symbol means any characters in any number (if you need to specify “one any character”, an underscore _ is used. By the way, this is called metacharacters and there are certainly more than two of them). In SQL, there is another thing that programmers (and maybe someone else) call inversion. It’s like the opposite. And if it is correct, then this is a logical operator meaning negation. Those. if in the previous example write NOT before LIKE, then as a result we will get all records that do not contain the letters adm.
Also, sometimes you only need to select unique values in a table. To do this, use the SELECT DISTINCT command, for example by entering:
SELECT DISTINCT status FROM users_nick; |
we will get all unique values from the status column of the users_nick table.
ORDER BY
It is also very common to use the ORDER BY command to sort data. After which we must specify the name of one or more columns by which the output data will be sorted. The simplest example of its use:
SELECT * FROM users_nick ORDER BY nick; |
Here we asked to sort the table data by the nick column. And as a result, we got a list of users in alphabetical order. But this is not the only way. You can, for example, sort the data not in alphabetical order (ascending – if numeric), but in reverse order (descending – if numeric). To do this, add DESC to the name of the column by which we filter.
Concatenating tables in SQL
Since there are several tables in the database, it may be necessary to display data from several tables at once. And filter them by some common values. So, for example, in our table with users, the Pulse user is not assigned a status, but there is a separate table with statuses. In this case, the general parameter we will have is id (although of course it would be more logical to use the general parameter, for example, a nickname, but oh well, the meaning does not change from this). To get the data, use the INNER JOIN command:
SELECT users_nick.nick, users_status.status FROM users_status INNER JOIN users_nick ON users_status.id = users_nick.id; |
Basically, we have merged records from two different users_nick and users_status tables into one. But take only the result in which the id matches. In this example, I showed exactly the INNER JOIN because it seems to be considered “correct” syntax and beautiful code, but in fact it is the same as if we wrote:
SELECT nick, users_status.status FROM users_nick, users_status WHERE users_nick.id = users_status.id; |
Both options are correct and you can use the one that suits you best.
By the way, pay attention that when we use the SELECT command and select some columns from different tables, and at the same time these columns have the same names (as we have the status column in both tables), then we must first write which table we mean, then put a full stop and write a column. Otherwise, we get an error at the exit.
It is also useful to remember about the existence of summary functions, they are needed to obtain, for example, the characteristics of a table:
- COUNT ( column ) – returns the number of rows;
- SUM ( column ) – returns the sum of the values in the given column;
- AVG ( column ) – Returns the average of the given column;
- MIN ( column ) – Returns the smallest value for the given column;
- MAX ( column ) – Returns the largest value for the given column.
For example:
SELECT MAX (id) FROM users_nick; |
will show us the maximum value in the id column of the users_nick table.
It is worth remembering the UNION command when using the SELECT command. Which allows you to combine two SELECT queries into one.
SELECT nick FROM users_nick WHERE nick = 'Pulse' UNION SELECT status FROM users_status; |
In this query, we first found a user with the nickname Pulse in the same table. And then they deduced all records from the status column of the second table.
Some nuances of the SQL language
Case of characters.
As you may have noticed, we wrote commands in uppercase. Actually it is not necessary, SQL language is not case sensitive. Therefore, you can enter commands as you like: SELECT select Select is always the same command. Therefore, uppercase in commands and lowercase in column and table names are used just for convenience. But do not forget that table and column names are case sensitive i.e. users_nick and USERS_NICK are two different tables.
Spaces
Any extra spaces in SQL are discarded. Therefore, for example, one instruction can be written on several lines. You can use spaces where it is more convenient for you, it basically does not affect anything.
Comments
Of course there are comments in SQL. The hash # at the beginning of a line will turn it into a comment. If you need to create a multi-line comment, then it must start with the characters / * and end with * /
This concludes our acquaintance with databases and SQL. Of course, in this article, I have given only the most basic knowledge. But they are more or less enough for understanding the processes occurring with databases and independent more in-depth study of SQL and everything connected with it, including the study of sql injection. By the way, what will we do in the following articles on this topic, and therefore do not forget to come back to us
