
Is SQL injection still a bad thing if the user is restricted to - elsadek
https://security.stackexchange.com/a/229255/21144
======
zzo38computer
If the user is supposed to enter arbitrary SQL queries, then it is already
allowed. (SQL injection should still be prevented when using forms that do not
say you can use arbitrary SQL code, though, even if it is the same database
and even if it is read-only and all data is public. This isn't because of any
kind of vulnerabilities, but merely to avoid bugs in the form in case a user
enters something unexpected.) However, you may add some access restrictions to
the database to ensure that it cannot be written (how to do that depends what
database you are using; with SQLite, there is an authorizer hook, defensive
mode, read-only mode (when opening the file), file permissions in the
operating system, progress callbacks, etc). Also, if you are allowing users to
enter arbitrary SQL queries, please link to the documentation so that the user
is aware what variant of SQL is in use; there are different variants of SQL
which have a few differences from each other. Being able to enter arbitrary
SQL queries is helpful, but this should be done properly, which is by
documenting this feature properly.

In other words: If the only query form is for the user to enter their own SQL
query, then SQL injection is irrelevant. If it has such a query form but also
has an additional form that does not use the user's own SQL query, then the
additional form without the user's own SQL query should be protected against
SQL injections, by passing parameters properly.

But there is another possibility too, which is to use remote virtual tables
(which could then be accessed using SQLite on the user's own computer, with a
suitable extension installed (I invented a "remote virtual table protocol",
although possibly improvements could be made)). This allows the user to do
such things as use JOIN queries with other data, create temporary tables, do
their own formatting, etc.

