If you're seeing this message, it means we're having trouble loading external resources on our website.

如果你被网页过滤器挡住,请确保域名*.kastatic.org*.kasandbox.org 没有被阻止.

主要内容

让你的SQL更安全

SQL 很精彩,但也可能很危险。如果您使用 SQL访问一个APP应用的数据库,而这个应用有数百、数千甚至数百万个用户,你需要很小心--因为您可能会意外地损坏或删除所有这些数据。然而,您可以采用各种技术来使 SQL 更安全。

避免错误的更新/删除

在确认 UPDATE之前,请使用相同的 WHERE 运行 SELECT ,以确保你将要更新的位置是正确的行和列。
例如, 在运行下面命令之前:
UPDATE users SET deleted = true WHERE id = 1;
您可以运行如下命令:
SELECT id, deleted FROM users WHERE id = 1;
一旦决定运行更新后,你可以使用 LIMIT 操作,来确保不会因失误对其他行进行了更新:
UPDATE users SET deleted = true WHERE id = 1 LIMIT 1;
或者,如果您要删除一个内容:
DELETE users WHERE id = 1 LIMIT 1;

使用数据库事务

当我们发出会更改数据库的SQL命令时,将启动所谓的“交易”。 交易是一系列作为单个逻辑任务(如银行交易)处理的操作,在数据库世界中,交易必须符合ACID 要素,以确保操作得到可靠处理。
当我们发出一个指令,比如 CREATEUPDATEINSERT,或者 DELETE时,我们就自动启动了一个事务。即便如此,我们依然可以根据需要,将多个指令打包成为一个更大的事务。比如这种情形,只有当一个 UPDATE 顺利进行时,我们才会进行另一个 UPDATE ,对于这种情况,我们就要把两个指令放在一个事务中。
实际操作时,我们用命令 BEGIN TRANSACTION 开始,用 COMMIT结束,将执行命令放在中间:
BEGIN TRANSACTION;
UPDATE people SET husband = "Winston" WHERE user_id = 1;
UPDATE people SET wife = "Winnefer" WHERE user_id = 2;
COMMIT;
如果由于某种原因,数据库无法发出这两个UPDATE 命令,那么它将回滚这个事务,并使数据库保持执行该事务前的状态。
当我们希望确保所有命令都在相同的数据视图上运行时,我们也会使用事务。也就是当我们要确保在一个系列命令运行时,不会出现对同一数据运行其他事务的情况。当您查看要运行的一系列命令时,想想看,如果其他用户也正同时对该数据发出命令,会出现什么结果。你的数据最终会陷入一种奇怪的状态吗?为了避免此种情况发生,我们要使用一个事务来运行该命令。
例如,以下命令的目的是创建一个行,标识用户获得了一枚徽章,然后更新用户最近的活动信息以记录:
INSERT INTO user_badges VALUES (1, "SQL Master", "4pm");
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;
就在同一个时间点,另一个用户或进程可能也在向该用户颁发第二枚徽章:
INSERT INTO user_badges VALUES (1, "Great Listener", "4:05pm");
UPDATE user SET recent_activity = "Earned Great Listener badge" WHERE id = 1;
这两个同时发出的命令,实际的执行情况很可能是这样的顺序:
INSERT INTO user_badges VALUES (1, "SQL Master");
INSERT INTO user_badges VALUES (1, "Great Listener");
UPDATE user SET recent_activity = "Earned Great Listener badge" WHERE id = 1;
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;
他们最近的活动现在显示的将是 "Earned SQL Master badge" ,尽管最近的徽章实际应该是 "Great listener"。虽然这看起来并不是什么大事,但这绝不是我们想要的结果。
更好的办法是,我们可以将上面的命令在一个事务中运行,这样可以保证中间不会同时发生其他事务:
BEGIN TRANSACTION;
INSERT INTO user_badges VALUES (1, "SQL Master");
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;
COMMIT;

对数据库进行备份

尽管你严格地遵循上述这些安全技术,但有时错误仍然会发生。正因为如此,大多数公司都会根据数据库的大小和可用空间,按照每小时、每天或每周的频率对其数据库进行备份。当发生意外事故时,对于任何遭到破坏或丢失的表单,都可以从旧数据库中恢复数据。尽管这些备份数据可能不是最后更新的,但总比根本没有数据要好的多。

复制数据库

还有一个常用的方法是复制——始终将数据库的多个副本存储在不同的地点。如果由于某种原因,数据库的某个副本不可用了 (比如闪电击中了它存放处的建筑物, 我就亲身经历过!),那么查询就可以发送到数据库的另一个副本,它会仍然可用。如果数据非常重要,则应再进行数据复制,以确保随时可调用。例如, 如果在急救情况下,医生要立即提取患者过敏的列表数据,以确定治疗方案,那么他们就不能等待工程师从备份中获取数据, 他们需要马上获得数据。
但是,复制数据库的工作量要大得多,这通常意味着效能降低,因为必须在所有数据库中执行写入操作。因此公司必须权衡复制的成本和好处,以找到适合自己情况的最佳的方法。

授予特权

许多数据库系统都内置了用户和权限,因为它们存储在服务器上,并供多个用户访问。在可汗学院的SQL 脚本中没有用户/权限的概念,因为 SQLite 通常在单用户方案中使用,所以只要您有权访问存储它的驱动器,就可以向它写入数据。
但是,如果有一天您在共享服务器上使用数据库系统,,那么切记从一开始就正确设置用户和权限。一般的原则是,应该只有少数用户可以完全访问数据库 (如后端工程师),因为它可能带来很大风险。
示例,下面是向特定用户提供完全访问权限的命令:
GRANT FULL ON TABLE users TO super_admin;
向另外用户只赋予他部分权限的命令:
GRANT SELECT ON TABLE users TO analyzing_user;
在一些大公司里,出于表单中包含姓名邮箱等大量隐私信息的考虑,对于绝大部分用户,通常我们连部分权限SELECT 都不赋予他们。 而有些公司的处理方式是,生成数据库的匿名版本,这样用户可以查询数据库却不会接触到隐私信息。
彩蛋:关于使SQL更安全的著名资料 XKCD comic (以及 详解)。