How to validate fields and conditions when performing overrides

Question

We have a central server and several regional servers, such as d1, d2, d3, d4, d5.

There are some tables to be copied. For simplicity, let's assume we have a table called tblFoo which exists on d1, d2, d3, d4, d5 and c and has the same structure on all these servers. The rules are simple:

• If a record exists on server d, it also exists on server c, and the value of each field is exactly the same
• If a record exists on d server (e.g. on d1), it will not exist on any other d server (d2, d3, d4 or d5)

The goal is to ensure that if a change (insert, update, delete) is made to tblFoo on the d server, it should also be immediately changed on the c server. This works great for insert operations (because the id, which is pkFooID, has the auto_increment attribute by definition). This also works for update and delete operations, but we have some concerns with these operations. Here is the code (simplified version):

namespace App\ORM;

use Cake\ORM\Query as ORMQuery;
// 其他一些use语句

class Query extends ORMQuery
{
    // 大量的代码...

    /**
     * 重写同名方法以处理与c的同步
     */
    public function execute()
    {
        // 一些表需要复制。如果是这样的表，则我们需要执行一些额外的步骤。否则，我们只需调用父方法
        if (($this->_repository->getIgnoreType() || (!in_array($this->type(), ['select']))) && $this->isReplicate() && ($this->getConnection()->configName() !== 'c')) {
            // 获取表
            $table = $this->_repository->getTable();
            // 复制查询
            $replica = clone $this;
            // 将复制品的连接设置为c，因为我们需要在中央应用地区的更改
            $replica->setParentConnectionType('d')->setConnection(ConnectionManager::get('c'));
            $replica->setIgnoreType($this->_repository->getIgnoreType());
            // 首先执行复制品，因为我们需要引用c的ID而不是反过来
            $replica->execute();
            // 如果是插入操作，我们还需要处理ID
            if (!empty($this->clause('insert'))) {
                // 加载主键的名称，以便稍后使用它来查找最大值
                $primaryKey = $this->_repository->getPrimaryKey();
                // 获取最高的ID值，这将始终是一个正数，因为我们已经在复制品上执行了查询
                $firstID = $replica->getConnection()
                                   ->execute("SELECT LAST_INSERT_ID() AS {$primaryKey}")
                                   ->fetchAll('assoc')[0][$primaryKey];

                // 获取列
                $columns = $this->clause('values')->getColumns();
                // 为了添加主键
                $columns[] = $primaryKey;
                // 然后用这个调整后的数组覆盖插入子句
                $this->insert($columns);
                // 获取值
                $values = $this->clause('values')->getValues();
                // 以及它们的数量
                $count = count($values);
                // 可能已经将多个行插入到复制品中作为此查询的一部分，我们需要复制所有它们的ID，而不是假设有一个单独的插入记录
                for ($index = 0; $index < $count; $index++) {
                    // 将适当的ID值添加到所有要插入的记录中
                    $values[$index][$primaryKey] = $firstID + $index;
                }
                // 用这个调整后的数组覆盖值子句，其中包含PK值
                $this->clause('values')->values($values);
            }
        }
        if ($this->isQueryDelete) {
            $this->setIgnoreType(false);
        }
        // 无论如何都执行查询，无论它是复制表还是非复制表
        // 如果是复制表，则我们已经在if块中对查询进行了调整
        return parent::execute();
    }

}

The worry is: If we execute an update or delete statement on d1, and its conditions can be satisfied on other regional servers (d2, d3, d4, d5), then it will be executed correctly on d1 update and delete statements, but once the same statements are executed on d1, we may accidentally update/delete records from other regions from the c server.

To resolve this issue, the recommended solution is to validate the statement and throw an exception if one of the following conditions is not met:

• The condition is = or IN
• The field is [pk|fk]*ID, that is, foreign key or primary key

Tables without replication behavior will execute execute normally. The above restrictions only apply to tables with replication behavior, such as tblFoo in our example.

question

How do I validate update/delete queries in my execute rewrite so that only primary or foreign keys can be searched, and only = or IN operators can be used?

Answer 1

This is how I solved the problem.

Models with copy behavior perform the following validations:

<?php

namespace App\ORM;

use Cake\ORM\Table as ORMTable;

class Table extends ORMTable
{
    protected static $replicateTables = [
        'inteacherkeyjoin',
    ];

    public function isValidReplicateCondition(array $conditions)
    {
        return count(array_filter($conditions, function ($v, $k) {
            return (bool) preg_match('/^[\s]*[pf]k(' . implode('|', self::$replicateTables) . ')id[\s]*((in|=).*)?$/i', strtolower(($k === intval($k)) ? $v : $k));
        }, ARRAY_FILTER_USE_BOTH)) > 0;
    }

    public function validateUpdateDeleteCondition($action, $conditions)
    {
        if ($this->behaviors()->has('Replicate')) {
            if (!is_array($conditions)) {
                throw new \Exception("在调用{$action}时，需要传递一个数组");
            } elseif (!$this->isValidReplicateCondition($conditions)) {
                throw new \Exception("传递给{$action}操作的条件不安全，您需要指定主键或带有=或IN运算符的外键");
            }
        }
    }

    public function query()
    {
        return new Query($this->getConnection(), $this);
    }
}

For the Query class, we have a isReplicate method that triggers the validation we need, and the where method has been overridden to ensure the condition is correctly validated ：

/**
     * 当且仅当：
     * - _repository已正确初始化
     * - _repository具有复制行为
     * - 当前连接不是c
     */
    protected function isReplicate()
    {
        if (($this->type() !== 'select') && ($this->getConnection()->configName() === 'c') && ($this->getParentConnectionType() !== 'd')) {
            throw new \Exception('副本表必须始终从区域连接更改');
        }
        if (in_array($this->type(), ['update', 'delete'])) {
            $this->_repository->validateUpdateDeleteCondition($this->type(), $this->conditions);
        }

        return ($this->_repository && $this->_repository->behaviors()->has('Replicate'));
    }

    public function where($conditions = null, $types = [], $overwrite = false)
    {
        $preparedConditions = is_array($conditions) ? $conditions : [$conditions];
        $this->conditions = array_merge($this->conditions, $preparedConditions);

        return parent::where($conditions, $types, $overwrite);
    }