SQLite 查询限制 [英] SQLite query restrictions

问题描述

我正在构建一个小界面，我希望用户能够写出他们的整个 sql 语句，然后查看返回的数据.但是，我不希望用户能够做任何有趣的事情，即 delete from user_table;.实际上，我希望用户能够做的唯一一件事就是运行 select 语句.我知道 SQLite 没有特定的用户，所以我在想我将要做的，是有一组拒绝某些查询的规则.也许是正则表达式字符串或其他东西(正则表达式让我有点害怕).关于如何实现这一点的任何想法?

I am building a little interface where I would like users to be able to write out their entire sql statement and then see the data that is returned. However, I don't want a user to be able to do anything funny ie delete from user_table;. Actually, the only thing I would like users to be able to do is to run select statements. I know there aren't specific users for SQLite, so I am thinking what I am going to have to do, is have a set of rules that reject certain queries. Maybe a regex string or something (regex scares me a little bit). Any ideas on how to accomplish this?

def input_is_safe(input):
    input = input.lower()
    if "select" not in input:
        return False
    #more stuff
    return True

推荐答案

我可以建议不同的方法来解决您的问题.您可以将数据库访问限制为只读.这样，即使用户尝试执行删除/更新查询，他们也不会损坏您的数据.

I can suggest a different approach to your problem. You can restrict the access to your database as read-only. That way even when the users try to execute delete/update queries they will not be able to damage your data.

这里是 Python 的答案如何打开只读连接:

Here is the answer for Python on how to open a read-only connection:

db = sqlite3.connect('file:/path/to/database?mode=ro', uri=True)

这篇关于SQLite 查询限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！