User is able to drop tables

[jdrew1303]

Current behaviour:

• Users are currently able to do destructive queries against DBs (DROP TABLE, DELETE, etc). They are also able to add data (INSERT, etc).

Expected behaviour:

• Users should not be allowed to submit queries that can delete, add or change data. (This can be configured in some databases by creating a readonly user)

Possible solutions:

Application fixes:

• I'd suggest using the ast from the codeschool sqlite-parser, walking the ast with sqltraverse (yup it's a shameless plug 😋 ). That way you can check for subqueries that may cause issues and notify the user.

Databases admin fixes:

• Create a read only user for the application to use:
• MySQL:

grant select on database_name.* to 'read-only_user_name'@'%' identified by 'password';

• PostgreSQL:

psql databasehere
CREATE USER moodle_readonly WITH ENCRYPTED PASSWORD 'blablablapasswordhere';
GRANT CONNECT ON DATABASE moodle TO moodle_readonly;
GRANT USAGE ON SCHEMA public TO moodle_readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO moodle_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public to moodle_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO moodle_readonly;

[antimatter15]

Franchise already bundles sqlite-parser, and we were looking into bundling your sqltraverse library for some cool query builder stuff.

MySQL and Postgres have somewhat different syntax than SQLite, so it remains an open question what the tool should do if the parser fails.

Clearly the right way to do this isn’t with a front end limitation. If the user shouldn’t be able to write to the database then that account shouldn’t have the privileges.

But I’m all for making it so that the user has to be extra careful if the tool detects that a query might write to the database. Perhaps making the user have to check a box that proves they’re aware of this.