Освоение PHP и управление базами данных: Создание собственной СУБД - часть II

Освоение PHP и управление базами данных: Создание собственной СУБД - часть II

В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления и удаления. Мы также исправим проблемы безопасности, которые мы специально оставили.

Если вы не читали первую часть, обязательно прочитайте ее сначала:

Осваиваем PHP и управление базами данных: Создание собственной системы СУБД

Реализация функциональности UPDATE

Я начну с того места, на котором остановился, поэтому давайте начнем работать над нашей таблицей. Добавьте <th> в ее заголовок и <td> с действиями в тело.

Crud.php

...    
<table class="table">
        <thead>
            <tr>
                <?php
                $columns = mysqli_query($conn, "SELECT `COLUMN_NAME` 
                        FROM `INFORMATION_SCHEMA`.`COLUMNS` 
                        WHERE `TABLE_SCHEMA`='ultra_crud'
                        AND `TABLE_NAME`= '$table';");
                while ($column =  mysqli_fetch_assoc($columns)) : ?>
                    <th><?= $column['COLUMN_NAME']; ?></th>
                <?php endwhile; ?>
                // Add this tag
                <th></th>
            </tr>
        </thead>
        <tbody id="dynamic-table">
            <tr>
                <?php
                $records = mysqli_query($conn, "SELECT * FROM `$table`");
                while ($record = mysqli_fetch_assoc($records)) :
                    foreach ($record as $key => $value) : ?>
                        <td><?= $value; ?></td>
                    <?php
                    endforeach; ?>
                    <td class="btn-group">
                        <a
                        class="btn btn-sm btn-success"
                        href="crud.php?table=<?= $table ?>&update_id=<?= $record['id']; ?>">
                            <i class="mdi mdi-pencil"></i>
                            <p class="visually-hidden">Edit</p>
                        </a>
                        <a
                        class="btn btn-sm btn-danger"
                        href="delete.php?table=<?= $table ?>&id=<?= $record['id']; ?>">
                            <i class="mdi mdi-trash-can"></i>
                            <p class="visually-hidden">Delete</p>
                        </a>
                    // Add this tag
                    </td>
            </tr> <?php
                endwhile; ?>
        </tbody>
    </table>

Мы будем использовать одну и ту же форму для обновления и вставки. Обновление формы в crud.php

...
if (!empty($_GET['update_id'])) {
    $update_id = $_GET['update_id'];
    $stmt = mysqli_prepare($conn, "SELECT * FROM `$table` WHERE `id` = ?");
    $stmt->bind_param('s', $update_id);
    $stmt->execute();
    $result = $stmt->get_result();
    $update_record = mysqli_fetch_assoc($result);
}
?>
<form action="<?= empty($update_record) ? 'insert' : 'update' ?>.php?table=<?=$table ?>" method="post">
        <?php
        $columns = mysqli_query($conn, "SELECT `COLUMN_NAME`, `DATA_TYPE`, `COLUMN_KEY`
            FROM `INFORMATION_SCHEMA`.`COLUMNS`
            WHERE `TABLE_SCHEMA`='ultra_crud'
            AND `TABLE_NAME`= '$table';");
        while ($column = mysqli_fetch_assoc($columns)):
            if ($column['COLUMN_KEY'] == 'PRI' && empty($update_record)) {
                continue;
            }
        ?>
        <div class="form-group">
        <label for="dynamic_input_<?= $column['COLUMN_NAME']; ?>"><?= $column['COLUMN_NAME']; ?></label>
        <input class="form-control" type="<?php
        switch ($column['DATA_TYPE']) {
            case 'int':
                echo 'number';
                break;
            case 'datetime':
                echo 'datetime-local';
                break;
            default:
            echo 'text';
        }
        ?>" 
        name="<?= $table ?>[<?= $column['COLUMN_NAME']; ?>]"
        id="dynamic_input_<?= $column['COLUMN_NAME']; ?>"
        value="<?= $update_record[$column['COLUMN_NAME']] ?? '' ?>"
        <?= $column['COLUMN_KEY'] == 'PRI' ? 'readonly' : '' ?>>

        </div>
        <?php endwhile; ?>
        <div class="form-group d-flex justify-content-end">
            <button type="submit" class="btn btn-success">Save</button>
        </div>
    </form>

Мы просто обновили место отправки и загрузили значения для обновления.

Давайте продолжим, реализовав update.php

<?php
// Include database connection
require_once __DIR__ . '/includes/db.php';
$table = $_GET['table'];

// We shall implement this method in a minute.
if (!tableExists($conn, $db, $table)) {
    die('delete.php: Invalid table name');
}

$data = $_POST[$table];
// We fetch columns from database, so a malcious user can't update any column
// that doesn't exist
$columns = getTableColumns($conn, $db, $table);

// Create the starting query
$query = 'UPDATE ' . $table . ' SET ';

// We shall use paramaterized queries to prevent SQL injection.
$params = [];

$id = null;
// Set the number of columns
foreach($columns as $column) {
    if ($column['COLUMN_KEY'] == 'PRI') {
        // We don't want to update our primary key!
        $id = $column['COLUMN_NAME'];
        continue;
    }
    $column_name = $column['COLUMN_NAME'];
    $query .= "`$column_name` = ?,";
    $params[] = $data[$column_name] ?? null;
}
$query = rtrim($query, ',');
$query .= " WHERE `$id` = ?";
$params[] = $data['id'];

// Prepare and execute the statement
$stmt = mysqli_prepare($conn, $query);
$stmt->bind_param(str_repeat('s', count($params)), ...$params);
$stmt->execute();

header('Location: crud.php?table=' . $table);

Теперь мы пишем безопасный код производственного уровня. Мы используем параметризованные запросы для предотвращения SQL-инъекций. Мы также проверили, существует ли таблица в базе данных (таблица не может быть параметризованной). Давайте реализуем функцию tableExists.

Db.php

...
function tableExists(mysqli $conn, string $db, string $table) {
    $query="SELECT count(*)
    FROM information_schema.tables
    WHERE table_schema = '$db'
    AND table_name = ?";

    $stmt = mysqli_prepare($conn, $query);
    $stmt->bind_param('s', $table);
    $stmt->execute();
    $result = $stmt->get_result();
    return mysqli_fetch_array($result, MYSQLI_NUM)[0] > 0;
}

function getTableColumns(mysqli $conn, string $db, string $table): array {
    $stmt = mysqli_prepare($conn, "SELECT `COLUMN_NAME`, `COLUMN_KEY`
    FROM `INFORMATION_SCHEMA`.`COLUMNS`
    WHERE `TABLE_SCHEMA`='ultra_crud'
    AND `TABLE_NAME`= ?;");
    $stmt->bind_param('s', $table);
    $stmt->execute();
    $result = $stmt->get_result();
    return mysqli_fetch_all($result, MYSQLI_ASSOC);
}

Думаю, здесь все понятно без пояснений.

Реализация функции DELETE

У нас уже есть ссылки для удаления. Давайте создадим файл delete.php

<?php

require_once __DIR__ . '/includes/db.php';

$table_name = $_GET['table'];
$id = $_GET['id'];

// Make sure that table exists
if (!tableExists($conn, $db, $table_name)) {
    die('delete.php: Invalid table name');
}

// Run the delete query
$stmt = mysqli_prepare($conn, "DELETE FROM `$table_name` WHERE `id` = ?");
$stmt->bind_param('s', $id);
$stmt->execute();

header('Location: crud.php?table=' . $table_name);

Защита предыдущих маршрутов

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

Добавьте следующее в начало файла crud.php

<?php
  require_once __DIR__ . '/includes/db.php';
if (empty($_GET['table'])) {
  die('No table provided');
}
if (!tableExists($conn, $db, $_GET['table'])) {
  die('delete.php: Invalid table name');
}
?>

Insert.php

<?php
require_once __DIR__ . '/includes/db.php';
$table = $_GET['table'];

if (!tableExists($conn, $db, $table)) {
    die('delete.php: Invalid table name');
}

$data = $_POST[$table];
$columns = getTableColumns($conn, $db, $table);

$query = 'INSERT INTO ' . $table . ' SET ';

// Set the number of columns
$params = [];

foreach($columns as $column) {
    $column_name = $column['COLUMN_NAME'];
    $query .= "`$column_name` = ?,";
    $params[] = $data[$column_name] ?? null;
}
$query = rtrim($query, ',');

// Prepare and execute the statement
$stmt = mysqli_prepare($conn, $query);
$stmt->bind_param(str_repeat('s', count($params)), ...$params);
$stmt->execute();

header('Location: crud.php?table=' . $table);

Заключение

Это была довольно короткая задача. Мне было интересно, что делать дальше. У меня есть несколько идей, пишите в комментариях, чтобы я знал, какую из них следует реализовать первой.

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