HVF / HVF/franchise

User is able to drop tables

Open
#26 1 comment 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.