验证数据库中的数据：sql vs code

My database schema has a 'locked' setting meaning that the entry can not be changed once it is set.

我的数据库模式具有“锁定”设置,这意味着一旦设置了该条目就无法更改。

Before the locked flag is set we can update other attributes. So:

在设置锁定标志之前,我们可以更新其他属性。所以:

Would you check the locked flag in code and then update the entry

你会检查代码中的锁定标志,然后更新条目

would it be better to combine that into a SQL query, if so, any examples?

最好将它组合成一个SQL查询,如果是这样,任何例子?

EDIT: how would you combine the update & check into one SQL statement?

编辑:如何将更新和检查合并到一个SQL语句中?

5 个解决方案

#1

You should do both. The database should use an update trigger to decide if a row can be updated - this would prevent any one updating the row from the back tables accidentally. And the application should check to see if it should be able to update the rows and act accordingly.

你应该两个都做。数据库应该使用更新触发器来确定是否可以更新行 - 这将阻止任何人意外地更新后台表中的行。应用程序应检查是否应该能够更新行并相应地执行操作。

#2

"how would you combine the update & check into one SQL statement?"

“你如何将更新和检查合并到一个SQL语句中?”

update table
set ...
where key = ...
and locked ='N';

That would not raise an error, but would update 0 rows - something you should be able to test for after the update.

这不会引发错误,但会更新0行 - 您应该能够在更新后测试。

As for which is better, my view is that if this locked flag is important then:

至于哪个更好,我的观点是,如果这个锁定标志很重要,那么:

you must check/enforce it in the database to ensure it is never violated by any access method

您必须在数据库中检查/强制执行它,以确保它不会被任何访问方法侵犯

you may also check/enforce it in the application, if that is more user-friendly

您也可以在应用程序中检查/执行它,如果这更加用户友好

#3

I would check the locked flag in code and then (assuming the record isn't locked) run the update query, setting the locked flag in the query as well. That way it can be wrapped in a transaction and committed/rolled back all at once.

我会检查代码中的锁定标志,然后(假设记录未锁定)运行更新查询,同时在查询中设置锁定标志。这样它就可以包装在一个事务中并一次性提交/回滚。

#4

I would use a trigger if your DBMS offers this function to enforce the flag. If you set the flag, all updates fail.

如果您的DBMS提供此功能来强制执行该标志,我会使用触发器。如果设置标志,则所有更新都将失败。

Then you can create a special query which will update the flag. Your trigger can check what called the update, and allow the flag to flick back if necessary. That way whether the TSQL is nice or malicious, no one can update your row once the flag is set.

然后,您可以创建一个更新标志的特殊查询。您的触发器可以检查调用更新的内容,并在必要时允许标志回退。这样,无论TSQL是好还是恶意,一旦设置了标志,没有人可以更新你的行。

#5

As a rule of thumb I would always prefer to check everything in code and consider writing the DB constraints after that. Having the DB perform consistency checks for you is cool and admittedly faster than doing it in your code but then you are putting some logic in the DB and have to pay some price. These constraints can become vendor-dependent and, worse, can you perform automated tests on these restrictions in your development environment?

根据经验,我总是更愿意检查代码中的所有内容,并考虑在此之后编写数据库约束。让数据库执行一致性检查很酷,而且比在代码中执行它更快,但是你在数据库中放入一些逻辑并且需要付出一些代价。这些约束可能会依赖于供应商,更糟糕的是,您是否可以在开发环境中对这些限制执行自动化测试?

So I'd consider putting this kind of checks in the DB as an added safety net but wouldn't rely on them alone.

因此,我会考虑将这种检查作为一个额外的安全网放在数据库中,但不会单独依赖它们。

#1

#2