Create Project: Issue SQL Query - Query cannot contain data manipulation keyword: DELETE

[franparrastome]

To Reproduce

Steps to reproduce the behavior:

1. First, create a new project
2. Then, select database and create a query where any column name contain the word delete, for example "isdeleted"
3. Finally, click preview query result

Current Results

Message: Query cannot contain data manipulation keyword: DELETE

Expected Behavior

Query should run

Screenshots

Versions

• Operating System: Windows 10
• Browser Version: Google Chrome Version 116.0.5845.96 (Official Build) (64-bit)
• JRE or JDK Version: JRE 1.8
• OpenRefine: OpenRefine 3.7.4

Datasets

Additional context

[tfmorris]

I've put up a draft PR for discussion which fixes this, but I'm not convinced that we shouldn't just remove all the checking code, since it's basically impossible to do reliably when the user can submit arbitrary SQL. The DB admin should be restricted read/write access to only those users who are trusted.

[tfmorris]

@franparrastome Do you have an opinion on whether we should attempt to improve the SQL "bad verb" detection or just remove it altogether? I'm leaning towards removing it.

[franparrastome]

Yes I think should be improved, it is really common to use isdeleted or isactive for records where we need to have a soft deletion :) so can he incorporated on the where clauses.

…

On Wed, 23 Aug 2023 at 20:04, Tom Morris ***@***.***> wrote: @franparrastome <https://github.com/franparrastome> Do you have an opinion on whether we should attempt to improve the SQL "bad verb" detection or just remove it altogether? I'm leaning towards removing it. — Reply to this email directly, view it on GitHub <#6019 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/A45B7Q7OO47JSSEDWE6PSQLXWZH5BANCNFSM6AAAAAA3WUWT6I> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- Thanks, Fran *Fran Parras* *Senior Manager, Global Solution Consulting, EMEA NORTH & DACH* +44 7557 245910 ***@***.***