如何确保SqLite中的只读事务?

时间:2022-09-12 08:44:04

I have public interface which allows people to interact with the database by typing in sql commands. However, I do not want them to change the database in any way (and if possible, not access certain tables). As I understand though, SQLite has no concept of users, so how do I accomplish this?

我有公共接口,允许人们通过输入sql命令与数据库进行交互。但是,我不希望他们以任何方式更改数据库(如果可能,不访问某些表)。据我所知,SQLite没有用户概念,所以我该如何做到这一点?

2 个解决方案

#1


2  

Copy the "master" database file first and open that :-) No, really, this is a serious suggestion.

首先复制“主”数据库文件并打开:-)不,真的,这是一个严肃的建议。

Otherwise, depending on how SQLite is accessed, the SQLITE_OPEN_READONLY flag that can be passed to sqlite3_open_v2. This applies to the entire connection -- and all transactions on that connection.

否则,根据访问SQLite的方式,可以传递给sqlite3_open_v2的SQLITE_OPEN_READONLY标志。这适用于整个连接 - 以及该连接上的所有事务。

Another option is to limit the SQL entry, but this is very very hard to do correctly and thus I don't recommend this route.

另一种选择是限制SQL条目,但这非常难以正确执行,因此我不建议使用此路由。

Happy coding.

#2


2  

If within the query there are no application defined sql functions, which indirectly modifies the database(e.g: SELECT eval('DELETE FROM t1') FROM t2; ), then use sqlite3_stmt_readonly to determine whether the prepared sql statement writes the database, otherwise you can try to open an other, read_only, database connection handler(SQLITE_OPEN_READONLY) which will be used for read_only access.

如果在查询中没有应用程序定义的sql函数,它间接修改数据库(例如:SELECT eval('DELETE FROM t1')FROM t2;),然后使用sqlite3_stmt_readonly来确定准备好的sql语句是否写入数据库,否则你可以尝试打开另一个read_only数据库连接处理程序(SQLITE_OPEN_READONLY),该处理程序将用于read_only访问。

#1


2  

Copy the "master" database file first and open that :-) No, really, this is a serious suggestion.

首先复制“主”数据库文件并打开:-)不,真的,这是一个严肃的建议。

Otherwise, depending on how SQLite is accessed, the SQLITE_OPEN_READONLY flag that can be passed to sqlite3_open_v2. This applies to the entire connection -- and all transactions on that connection.

否则,根据访问SQLite的方式,可以传递给sqlite3_open_v2的SQLITE_OPEN_READONLY标志。这适用于整个连接 - 以及该连接上的所有事务。

Another option is to limit the SQL entry, but this is very very hard to do correctly and thus I don't recommend this route.

另一种选择是限制SQL条目,但这非常难以正确执行,因此我不建议使用此路由。

Happy coding.

#2


2  

If within the query there are no application defined sql functions, which indirectly modifies the database(e.g: SELECT eval('DELETE FROM t1') FROM t2; ), then use sqlite3_stmt_readonly to determine whether the prepared sql statement writes the database, otherwise you can try to open an other, read_only, database connection handler(SQLITE_OPEN_READONLY) which will be used for read_only access.

如果在查询中没有应用程序定义的sql函数,它间接修改数据库(例如:SELECT eval('DELETE FROM t1')FROM t2;),然后使用sqlite3_stmt_readonly来确定准备好的sql语句是否写入数据库,否则你可以尝试打开另一个read_only数据库连接处理程序(SQLITE_OPEN_READONLY),该处理程序将用于read_only访问。