Безопасная, расширяемая база данных с динамическими подготовленными инструкциями
Проблема: Моя задача - создать базу данных для хранения информации о различных продуктах и создать RESTful api для обслуживания и управления этой информацией. Но клиент не знает точно, какая информация им понадобится об этих продуктах, поэтому в базу данных, скорее всего, позже будут добавлены новые столбцы и таблицы для размещения новых свойств продукта. Мой вопрос заключается в создании базы данных, которая с готовностью примет эти изменения, и построении запросов, которые могут извлекать продукты надежно основаны на свойствах продукта, которые еще не существуют, практически без изменений.
Предлагаемое решение: У меня есть тестовая настройка базы данных со следующей структурой.
+------------------+
| item |
+----+------+------+
| id | name | cost |
+----+------+------+
| 0 | test | 50 |
+----+------+------+
+--------------+
| color |
+----+---------+
| id | val |
+----+---------+
| 0 | blue |
| 1 | purple |
+----+---------+
+--------------------+
| item_color |
+---------+----------+
| item_id | color_id |
+---------+----------+
| 0 | 0 |
| 0 | 1 |
+---------+----------+
В таблицу "элемент", скорее всего, будут добавлены столбцы позже, и, скорее всего, также будут добавлены дополнительные таблицы соединений.
Запрос на извлечение продуктов хотел бы http://www.example.com/api/products?цвет =фиолетовый и стоимость=50 С помощью php я динамически строю готовые инструкции для извлечения соответствующих продуктов, надеюсь, не открывая дверь для sql-инъекций. Сначала я определяю, какие свойства включены в таблицу "элемент", а какие находятся в отдельных таблицах, используя следующие функции:
function column_exists($column, $pdo) {
$statement = $pdo -> prepare("DESCRIBE item");
$statement -> execute();
$columns = $statement -> fetchAll(PDO::FETCH_COLUMN);
$column_exists = in_array($column, $columns);
return $column_exists;
}
function table_exists($table, $pdo) {
$statement = $pdo -> prepare("SHOW TABLES");
$statement -> execute();
$tables = $statement -> fetchAll();
$table_exists = in_array($table, $tables);
return $table_exists;
}
Если свойство не найдено в виде столбца в таблице "элемент" или в виде имени таблицы, то возникает исключение.
Подготовленный оператор, который строит мой код, будет выглядеть следующим образом:
$sql = "SELECT * FROM item WHERE cost = :cost
AND id IN (SELECT item_id FROM item_color
WHERE color_id IN (SELECT id FROM color WHERE val = :color));";
И будет выполняться следующим образом итак,
$statement = $pdo -> prepare($sql);
$statement -> execute(Array(":cost" => $cost, ":color" => $color));
Что я хочу знать: Столкнусь ли я с серьезными узкими местами по мере роста базы данных и более частого доступа к ней? Является ли мой метод извлечения безопасным для атак с использованием sql-инъекций 1-го порядка?
Что я сделал: Я читал об элементарных принципах проектирования баз данных и основных методах атаки/защиты с помощью sql-инъекций. Я пытался прочитать о динамическом создании подготовленных инструкций, но материал, который я нашел, - это не то, что я ищу. Я проверил свою дизайн против базовой атаки таблиц Бобби.
Почему этот вопрос актуален: Принципы проектирования, которые я прочитал, предостерегают от попыток сделать базу данных слишком гибкой Те, кто новичок в этой области, не могут оценить, насколько гибкая является слишком гибкой, и могут извлечь выгоду из анализа этого примера. Кроме того, похоже, что подготовленные инструкции предназначены только для использования в качестве статических шаблонов. Если я нахожу этот динамичный способ их создания заманчивым, то, вероятно, другие новички тоже будет, поэтому нам нужно знать, создаем ли мы серьезную уязвимость в системе безопасности. Наконец, я задал эти вопросы вместе, потому что дизайн базы данных и структура запросов, которые будут выполняться к ней, напрямую связаны.
Подробности: php версии 5.6.17 mysql версии 5.6.35
1 answers
Моя первая реакция на ваш SQL заключается в том, что вам действительно нужно научиться использовать JOIN
в SQL. Операция соединения действительно имеет основополагающее значение для SQL и реляционных данных. Использование только подзапросов вместо JOIN
похоже на использование другого языка программирования, но отказ от использования цикла while()
. Конечно, вы можете это сделать, но почему?
$sql = "SELECT * FROM item WHERE cost = :cost
AND id IN (SELECT item_id FROM item_color
WHERE color_id IN (SELECT id FROM color WHERE val = :color));";
Должно быть
$sql = "
SELECT i.id, i.name, i.cost, c.color
FROM item AS i
INNER JOIN item_color AS ic ON i.id = ic.item_id
INNER JOIN color AS c ON c.id = ic.color_id
WHERE i.cost = :cost AND c.val = :color";
Любая ссылка или учебник по SQL охватывает соединения.
Что касается вашего вопроса о безопасности, да - использование параметров запроса безопасен в отношении SQL-инъекции. Делая базовый запрос жестко запрограммированным и разделяя динамические части на параметры, вы исключаете любую возможность того, что небезопасные данные изменят синтаксический анализ вашего SQL-запроса.
Возможно, вам понравится моя презентация Мифы и заблуждения о внедрении SQL (видео: https://www.youtube.com/watch?v=VldxqTejybk).
Ваши требования заставляют меня думать, что вам было бы лучше использовать базу данных документов, такую как MongoDB, где вы можете добавлять атрибуты в любую документ. Это не означает, что вам все еще не нужно быть осторожным при проектировании базы данных, но это дает вам возможность легче добавлять атрибуты после проектирования.