| Summary: | SQL reports should allow Common Table Expressions | ||
|---|---|---|---|
| Product: | Koha | Reporter: | Michał <schodkowy.omegi-0r> |
| Component: | Reports | Assignee: | Bugs List <koha-bugs> |
| Status: | NEW --- | QA Contact: | Testopia <testopia> |
| Severity: | minor | ||
| Priority: | P5 - low | CC: | dcook, mathsabypro |
| Version: | Main | ||
| Hardware: | All | ||
| OS: | All | ||
| GIT URL: | Initiative type: | --- | |
| Sponsorship status: | --- | Crowdfunding goal: | 0 |
| Patch complexity: | --- | Documentation contact: | |
| Documentation submission: | Text to go in the release notes: | ||
| Version(s) released in: | Circulation function: | ||
I'm not sure that will work because it would allow the following: WITH ... SELECT ... WITH ... UPDATE ... WITH ... DELETE ... As you noted on Mattermost, I think SQL reports really should have a dedicated read-only user, so that permissions can be managed ultimately by the DB rather than Koha. Actually, now you've gotten me thinking about ways to try to bypass the system... I have just discover CTE... It would be nice to be able to use them in Koha reports Reports security really needs an overhaul... |
Common table expression in MySQL is a syntax like this: WITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, d FROM table2) SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c; However, in Koha/Koha/Report.pm, there's func is_sql_valid: } elsif ( $sql !~ /^\s*SELECT\b\s*/i ) { push @errors, { queryerr => 'Missing SELECT' }; } It should be changed to SELECT|WITH, otherwise it won't allow these nice queries, instead having you to do an ugly workaround like this: SELECT * FROM ( WITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, d FROM table2) SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c ) AS dt;