User is able to drop tables
- Dominant language
- JavaScript
- Stars
- 4k
- Forks
- 262
- PR merge metrics
- No merged PRs in 30d
Description
## 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`](https://github.com/jdrew1303/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:
```sql
grant select on database_name.* to 'read-only_user_name'@'%' identified by 'password';
```
- PostgreSQL:
```sql
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;
```
Contributor guide
Research direction
Start by tracing where submitted SQL is executed and compare application-side filtering with the read-only database-user approach described in the issue. Review sqlite-parser and sqltraverse for handling subqueries, then verify that destructive and data-changing queries are rejected or cannot execute through the configured database access.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- javascript, mysql, postgresql, sql
- Domain
- databases, security
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 30/100