问题[база-данных]

我在 EnSO 版主Dharman的个人资料中看到了以下警告：

免费翻译：

警告：

您对 SQL 注入的威胁非常大，应该使用参数化的准备好的语句，而不是手动构建查询。它们由 PDO 或 MySQLi 提供。永远不要相信任何输入数据！即使您的请求仅由受信任的用户提出，您仍然有可能损坏您的数据。屏蔽还不够！

如果版主认为在他的个人资料中发布这个警告很重要，以便尽可能多的人可以看到它，那么我自然会对SQL 注入到底是什么样的怪物以及与之相关的一切问题感兴趣：

来自MySQLi标签描述

PHP 编程语言中使用的关系数据库驱动程序的扩展，用于提供对 MySQL 数据库的访问。 MySQLi 是 PHP MySQL 驱动程序的更新版本，并在数据库处理方面提供了各种改进。

我希望得到更详细的答复。

如何实施保护，最好有例子并附上简短的解释性评论。

当我在不同的事务中并行执行查询时，我遇到了 Postgres 的奇怪行为。 Postgres 15.3，数据库上的事务隔离级别 -读提交。请求如下：

with inserting as (
            insert into device(device_guid, platform, created_at)
            values (
                '5f0ca10f-a257-44ac-940e-a808c63a9914'::uuid,
                'WEB',
                NOW()
            )
            ON CONFLICT (device_guid) DO NOTHING
            returning *
        )

        select id, device_guid, platform, created_at, 'INSERT' as db_operation from inserting
                UNION ALL
        select id, device_guid, platform, created_at, 'GET' as db_operation from device where device_guid = '5f0ca10f-a257-44ac-940e-a808c63a9914'::uuid

（device_guid 上有一个唯一索引）

预期会出现以下行为：

1. 输入是device_guid和一些数据（在示例中为'5f0ca10f-a257-44ac-940e-a808c63a9914'，'WEB'，NOW（））
2. 尝试在 CTE 中插入传递的 device_guid。如果这样的device_guid已经存在，那么我们只需退出CTE进入下一步（选择）。如果表中没有这样的 device_guid，那么我们将其插入表中，并将其插入到 CTE 中
3. 接下来，我们从物理表设备和插入临时 CTE 表中进行选择。这里 db_operation 会告诉您该行是从哪里获取的，是从 CTE（INSERT）获取的，还是该行已经在设备表中（GET）。
4. 如果执行查询两次，一切都会按预期工作：第一次，出现一行 db_operation = 'INSERT'，第二次，出现一行 db_operation = 'GET'。

但是，当两个事务同时开始使用相同的 device_guid 执行此查询时，一切都会中断。我期望以下行为是正确的：

1. 事务 A比事务 B稍早开始。
2. 字符串 A尝试将一行插入到具有唯一索引的表中。这意味着事务 A将对该行采取排他锁，并对索引采取共享锁，直到事务提交为止。
3. 线程 B看到线程 A已获取锁，则只需等待锁被释放。
4. Thr-A插入成功并继续进行两次选择。但自从如果事务还未提交，则不会移除锁。
5. Tr-ya A完成其选择和整个查询脚本并提交事务，释放锁。
6. Tr-ya B发现锁被移除，尝试使用相同的 device_guid 向设备中插入一行，发生冲突，它悄悄地跳过冲突，并使用 select 转到块中
7. 在这个阶段，字符串A应该已经被提交（或者更准确地说，已经在第5点），这意味着从设备表中选择应该已经看到字符串A插入的行。这保证了我们的读取已提交隔离级别。
8. 这里带有 union all 的选择块应该返回一行，其中 db_operation = 'GET'，即物理表设备中已有的行，由tr-ya A放置在那里

但事实上，第 8 点返回0 行..

该错误最初是在 spring data jdbc 和 jooq 中发现的，但后来我在标准 psql 中多次重现了该错误。这可以通过在begin 中包装 SQL 脚本来完成； ... 犯罪;并在两个独立的 psql 控制台中运行它。但只能在一次写入开始； ...（没有提交；），然后在第二个控制台中使用begin 执行整个脚本； ... 犯罪; 如果您查看锁，您会发现第二个事务将被第一个事务阻止，并且锁在 device_guid 字段的 unicode 索引上可见。但是一旦你输入提交；在第一个控制台中，第二个控制台将完成其交易并返回 0 行。

看上去出现这种情况的原因是，在准备事务A的提交时，首先会释放索引和行上的锁，然后才提交整个事务A。在释放锁和提交之间的这段时间内，事务 B设法执行其代码，而从未看到事务 A提交的行。第三天，我一直在试图了解我是否期待了错误的行为，或者 Postgres 是否表现异常。审查了有关阻塞、CTE 和隔离级别的文档。看来一切都正如我所料的那样。如有任何信息我将不胜感激！

大家好！问题是，我怎样才能使新对象的每一列的值都可以改变，但又不必进行硬编码？ UPDATE 使用选项将不起作用，因为此代码将用于更新触发器。

FOREACH v_column IN ARRAY v_columns LOOP
        IF EXISTS (
            SELECT 1
            FROM information_schema.columns
            WHERE table_name = TG_TABLE_NAME AND column_name = v_column
        ) THEN
            EXECUTE format('SELECT ($1).%I', v_column) INTO v_value USING NEW;--получаем значение незашифровонное  
            
            IF v_value IS NOT NULL THEN --если оно не NULL
            --NEW.email := text(pgp_sym_encrypt(v_value, 'Aboba')); можно конечно вот так, но тогда придется каждый столбец писать харкодом
            EXECUTE format('$1.%I := $2', v_column) USING NEW, text(pgp_sym_encrypt(v_value, 'Aboba')); -- шифруем, здесь я пытался заменять значение NEW через execute чтобы столбцы заменялись динамически
            END IF;
        ELSE
            RAISE EXCEPTION 'Column "%" not found in table %', v_column, TG_TABLE_NAME;
        END IF;
    END LOOP;

我的问题是在阅读 Martin Kleppmann 所著的《高负载应用程序：编程、扩展、支持》ISBN 978-5-4461-0512-0 的书时出现的，我在第 383 页遇到了一个我不清楚的点。图 9.4

在本页的前半部分，作者讨论了阴影矩形，并表示该操作是非线性的。我不明白为什么？

直到第383页，作者解释了术语“线性化操作”的含义。他通过给出时间上重叠的读写操作的示例来解释这一点：

• 写操作收到成功确认后，从那一刻起，所有读操作都会收到一个新的
• 作者还提到，在写操作期间，有一个时刻，旧值被自动更改为新值，并且发起写操作的客户端尚未收到确认，而有读请求的客户端已经收到了新值

回到第383页的图9.4，我不明白这一点，为什么作者特别关注客户B？毕竟，在此之前，有一个时刻，客户端 C 使用 操作将 2 更改为 4 cas(x,2,4)，导致客户端 A 读取了新值 4。客户端 A 和客户端 B 一样，也是竞争执行的！为什么作者不说客户端A，却说客户端B，和客户端A一样的情况，突然说他的读操作是非线性的。

作者的话“在没有其他查询的情况下，如果 read B 返回 2 就好了”完全令人困惑！为什么好呢？

应用程序需要存储诸如语言、主题之类的数据，存储这些数据的最佳位置在哪里？我一直使用LocalStorage，但在我看来这不是正确的解决方案，它可以加载到数据库中，但是每次都必须从数据库中加载，那么标识信息应该存储在哪里呢？并非每次都要求用户登录。最好的使用方法是什么？