Всем привет! Хочешь рассмешить Бога – расскажи ему о своих планах. Сработало на 100% стоило лишь запланировать написать статью на прошлой неделе. Много работы. Нагрузка и производительность растёт, зарплата пока нет. Тоже своего рода закономерность.  В прошлый раз писал про HTML-Injection. Сегодня поговорим про базовые основы SQL. Данная статья будет своеобразным введением для последующей — SQL-Injection. Рассказывать об SQL-инъекциях не затрагивая вопрос о базах данных и основах языка SQL не правильно. Нельзя читать не зная букв, но многим это впрочем не мешает.

База данных – это что-то вроде электронной картотеки предназначенной для хранения, обработки и изменения большого количества информации. Основной структурный элемент базы данных — таблица. Столбец – это структурный элемент таблицы. Строка – отдельная запись в таблице.

Язык структурированных запросов SQL (Structured Query Language) был специально создан в 1974 г. для того чтобы работать с базами данных (далее БД). Активно использую его в автоматизированном тестировании. Определения и прочую теорию вы и так сможете нарыть в интернете. Первую книгу которую прочитал по работе с БД — Работа с MySQL, MS SQL Server и Oracle в примерах (практическое пособие для программистов и тестировщиков). Так себе ложиться без практики. Ссылка на хороший практический тренажёр ищите в конце статьи. По этому ниже в практических примерах познакомлю с базовыми командами языка SQL.

На одной из моих виртуальных машин установлен Parrot Security OS. В ней уже из коробки пролита система управления БД — mysql. На момент написания её версия — mysql Ver 15.1 Distrib 10.3.24-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2. Её и буду использовать для опытов. Открыть терминал.

  1. Убедиться что mysql запущена как служба — sudo service --status-all | grep mysql
  2. Подключиться к серверу БД: mysql -u root -p

Но как видно на скрине выше пароль к БД не знаю. Первый квест — сбросить пароль к root в mysql.

 Как сбросить пароль root для MySQL 
  1. Останавливаем сервер базы данных, выполнив следующую команду: systemctl stop mysql
  2. Запустить MySQL командой: mysqld_safe --skip-grant-tables --skip-network &
  3. Войти в БД: mysql
  4. Сбрасываем пароль на новый = P@ssw0rd: 
    UPDATE mysql.user SET password=password('P@ssw0rd') WHERE user='root';
FLUSH PRIVILEGES;

Поднимаем остановленный в п. 1 сервер: systemctl start mysql.

Соединяемся, используя новый пароль: mysql --user=root --password=P@ssw0rd. Как видите тут явно приписал пароль, без флага -p.

Всё, доступ восстановлен. Двигаемся дальше.

 Просмотр баз данных.SHOW. 

С помощью команды  SHOW можно просматривать информацию о колонках в таблицах и о самих базах данных, а также прочие данные о статистике и производительности сервера MySQL. Команды в SQL должны заканчиваться «;».

  1. Просмотрим имеющиеся базы данных: SHOW DATABASES;
  2. Просмотрим таблицы в базе данных с названием mysql: SHOW TABLES FROM mysql;
  3. Просмотрим в таблице host список всех столбцов: SHOW COLUMNS FROM host FROM mysql;

Можно было бы после пункта 1 использовать команду use mysql; и длины команд бы сократились.

 Создание базы данных, таблицы.CREATE. 

Создадим новую базу при помощи команды CREATE DATABASE. Имя базы — test. Полная команда:

CREATE DATABASE test; В созданной базе создадим таблицу CREATE TABLE. Название таблицы — test_table. Перед этим используем use test;  —  говорим MySQL что будем работать с базой test.  Полный запрос создания, пояснения ниже:

CREATE TABLE test_table (id INT AUTO_INCREMENT PRIMARY KEY, name1 NVARCHAR(255) NOT NULL, name2 NVARCHAR(255) NOT NULL);

После названия таблицы в скобках, через запятую, перечислил колонки с типами данных. Первый столбец — id. Тип данных INT (целочисленные значения). Применив команду AUTO_INCREMENT сказали MySQL самостоятельно заполнять строки этой колонки при внесении новых записей в таблицу, добавляя +1 к каждой новой уникальной записи. Использовав PRIMARY KEY сказали MySQL назначить значения из этой колонки первичными ключами – уникальными идентификаторами строк таблицы.

Второй столбец который создали — name1. Тип данных NVARCHAR (строки) Значение в скобках — это размер строки в байтах, а не количество символов как многие считают. Команда NOT NULL — значение в столбце не может быть пустым.

Просмотрим созданную таблицу: SHOW COLUMNS FROM test_table;

 Добавление данных.INSERT. 

Таблица на данный момент пустая. Добавим первые данные.

INSERT INTO test_table (id, name1, name2) VALUES ('7777', 'Hi', 'Team'); И просмотрим результат создания командой SELECT * FROM test_table; Про SELECT расскажу чуть позже.

Через запятую сначала перечислили столбцы, а значения которые мы хотим добавить в соответствующий столбец после команды VALUES. Значения всегда должны быть в одинарных кавычках. Значения указывать в том же порядке в котором перечисляли столбцы.

 Извлечение, сортировка данных.SELECT. 

Блоком выше данную команду использовали, когда извлекали все данные из таблицы test_table. Чаще всего при проведении тестирования надо извлекать из БД данные по какому-то признаку, критерию. Вынем из нашей таблицы только значение содержащее id = 7777.  Используем: SELECT * FROM test_table WHERE id = '7777'; Использовалось условие WHERE. С ним можно использовать логические операторы (AND/OR) и математические операторы сравнения. В базу добавил ещё несколько значений для того чтобы показать как можно извлекать данные используя команду LIKE.

При помощи SELECT * FROM test_table WHERE name1 LIKE '%123%' AND name2 LIKE '%Te%'; сказал  вывести записи из таблицы test_table у которых в столбце name1 есть символы 123 и так же в столбце name2 есть символы 123. Выполнение двух условий — AND (И). Символ % означает любые символы в любом количестве. Если в предыдущем запросе перед LIKE написать NOT (инверсия) то получим все записи в которых нет символов 123.

Если надо отобразить к примеру уникальные значения, то используйте запрос SELECT DISTINCT

Команда ORDER BY используется для сортировки когда нам надо «причесать» нашу таблицу по какому — нибудь столбцу, в алфавитном порядке. К примеру давайте сделаем это по столбцу name1. Запрос: SELECT * FROM test_table ORDER BY name1; Результат:

Таблиц в БД может быть много и часто нужно делать выборку из двух, трёх и более таблиц. Для этого используют команду INNER JOIN.

Команда UNION позволяет объединить два запроса SELECT в один. Для того чтобы показать как это работает создам ещё одну таблицу, назову test_table1. Строки таблицы — name3, name4. заполню данными.

CREATE TABLE test_table1 (id INT AUTO_INCREMENT PRIMARY KEY, name3 NVARCHAR(255) NOT NULL, name4 NVARCHAR(255) NOT NULL);
INSERT INTO test_table1 (id, name3, name4) VALUES ('0', 'test1', 'test2');
INSERT INTO test_table1 (id, name3, name4) VALUES ('10', 'notest', 'nofate');
SELECT * FROM test_table1;

Теперь выполним SELECT name1 FROM test_table WHERE name2='Team' UNION SELECT name3 FROM test_table1 WHERE name3='test1'; Результат ниже:

 Изменение данных.INSERT. 

Данную команду можно было рассмотреть в блоке добавления данных, но это немного другое. Там мы добавляли (наполняли базу), а сейчас к примеру в таблице test_table1 надо в записи с id = 10 поменять значение столбца name3 на test_fail. Делается просто: UPDATE test_table1 SET name3 = 'test_fail' WHERE id = 10;

Если перевести на человеческий: ОБНОВИ значение в таблице test_table1 В СТОЛБЦЕ name3 на test_fail ТАМ ГДЕ id=10. WHERE задало условие поиска.

 Удаление данных.DROP. 

В завершении расскажу как удалить всё то безобразие что насоздавали. Ломать — не строить, сердце не болит! И тут нам на помощь спешит команда DROP.

DROP TABLE test_table1; — удалило полностью таблицу test_table1. Таблицу test_table помучаем немного и сначала удалим всё её содержимое. В этом нам поможет запрос TRUNCATE TABLE test_table; добиваем DROP TABLE test_table; Ну и логично затем отправить к проотцам нашу базу — test.

В начале статьи обещал дать ссылку на хороший практический тренажёр языка SQL. Ловите — клац. В разделе Самообразование создал вкладку Упражнения по SQL — www.sql-ex.ru где буду периодически выкладывать свои решения задач.

Удачи!