Bug 31398 - RFE: LibreOffice Base should allow update/insert/drop query in sql-editor with hsqldb access controls
Summary: RFE: LibreOffice Base should allow update/insert/drop query in sql-editor wit...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: x86-64 (AMD64) All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 37599 112272 (view as bug list)
Depends on:
Blocks: Base-Enhancements Database-HSQLDB
  Show dependency treegraph
 
Reported: 2010-11-04 17:04 UTC by collura
Modified: 2024-07-10 13:44 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
sample database for query testing (4.77 KB, application/vnd.oasis.opendocument.database)
2016-05-07 08:40 UTC, collura
Details
MySQL-native-connector with possibility to update data through query (1.34 MB, application/vnd.openofficeorg.extension)
2017-11-30 15:29 UTC, Robert Großkopf
Details
Error Message (40.67 KB, image/png)
2024-07-10 10:33 UTC, Andreas Heinisch
Details

Note You need to log in before you can comment on or make changes to this bug.
Description collura 2010-11-04 17:04:38 UTC
most people can figure out an sql command syntax to do what they need much 
easier than they can write a macro to do the same thing because of the requisite concepts of 'subroutine' and 'dimension' which most people dont know about and the different interface you need to work with to write a macro.

if we provide these types of query functions it would speed adoption of Base with a lot of smaller simpler project users and make others more productive to boot.  

0) we should probably control access rights directly not by limiting functionality of update/insert/drop functions in sql-editor.


----if a non-destructive query is considered a requirement such that update/insert/drop are not acceptable then the macro for these queries should be made simpler to access/setup.




----the problem with adding destructive queries to the sql editor is presumably that it concerns adminsitrators because it makes potential tinkering damage by random users more likely.  




so the real problem to be tackled should be not IF to add the update/add/remove functionality but HOW to add the add/update/remove query functionality to sql editor while limiting any pottential misuse.  



so maybe the most productive place to put that access control would be to pull it from the permission structure of the hsqldb/mysql/postgresql database instead of limiting the functionality of the Base database.



http://hsqldb.org/doc/2.0/guide/accesscontrol-chapt.html



     "Access Rights



     By default, the objects in a schema can only be accessed by the schema owner. 

     But the schema owner can grant privileges (access rights) on the objects 

     to other users or roles.



     Things can get far more complex, because the grant of privileges 

     can be made WITH GRANT OPTION. In this case, the role or user that has been granted 

     the privilege can grant the privilege to other roles and users.



     Privileges can also be revoked from users or roles.



     The statements for granting and revoking privileges normally specify which privileges 

     are granted or revoked. However, there is a shortcut, ALL PRIVILEGES, which means all 

     the privileges that the <grantor> has on the schema object. The <grantor> is normally 

     the CURRENT_USER of the session that issues the statement.



     The user or role that is granted privileges is referred to as <grantee> for 

     the granted privileges."



     ...



     "The INSERT privilege designates the columns into which explicit values can be inserted. 

     To be able to insert a row into the table, the user must therefore have the INSERT privilege

     on the table, or at least all the columns that do not have a default value."




so access rights are part of hsqldb and if we apply them we dont have to limit functionality.





1) further to make it easy to access maybe we should add an "access controls" functionality group to the base gui: 

(tables, queries, forms, reports) ---> (tables, queries, forms, reports, accesscontrols)



or add "access controls" as an item to the secondaryclick menu for tables, queries, forms, reports icons in the Base gui.





example of desired use of update/insert/delete is:

   -- a 'reset field' button which activates an update query changing values in some particular field which have some particular value and changing that value to some other particular value



   -- an 'add record' button that takes inforamtion entered in display fields and adds a record for them in the database



   -- a 'delete record' button that removes a record from the database






2) test of update query:


version:

LibreOffice 3.3.0 

OOO330m9 (Build:1)

libreoffice-build 3.2.99.2


test file done on mswindows-os


make a table: 

Table1.key, integer, autonumber, primary key

Table1.fld-flag, boolean

Table1.fld-desc, string

Table1.fld-int, integer



populate the table:

(auto number didnt seem to work in key when enter in table view so entered number manually)

key,    fld-flag,     fld-desc,     fld-int

1,      No,           one,            1 

2,      YES,          two,            2 

3,      YES,          three,          3

4,      No,           four,           4





when run as query fails: 

     UPDATE "Table1" set "Table1"."fld-flag" = False where "Table1"."fld-flag" = True;



(error was: "LibraOffice Base: The data content could not be loaded.  Statement does not generate a result set.")





when run same sql code as tools-sql-execute_command it succeeds ("Command successfully executed."):

     UPDATE "Table1" set "Table1"."fld-flag" = False where "Table1"."fld-flag" = True;





which is comfirmed by running query as:

     SELECT "fld-flag", "fld-desc", "fld-int" FROM "Table1" WHERE "fld-flag" = True



which correctly returns empty since none were true:



key,    fld-flag,     fld-desc,     fld-int

1,      No,           one,            1 

2,      NO,           two,            2 

3,      NO,           three,          3

4,      No,           four,           4



if go back in to table view to try to edit boolean flags to true get an error:



'File input/output error: C:\Program Files\Office\New Database1.odb.log in statement 

[UPDATE "Table1" SET "fld-flag" = ? WHERE "ID-key" = ?] '



but if then exit base and then restart base and reopen the file then there is no problem editing the values in the table view. 

this data edit error was not reproduceable.
Comment 1 Alex Thurgood 2011-05-26 01:52:25 UTC
*** Bug 37599 has been marked as a duplicate of this bug. ***
Comment 2 Björn Michaelsen 2011-12-23 11:35:40 UTC Comment hidden (obsolete)
Comment 3 collura 2011-12-26 00:33:40 UTC
hi, yes it still seems to be there as of

  3.5.0beta2 
  Build ID: 8589e48-760cc4d-f39cf3d-1b2857e-60db978

where trying to run the test in section2 of original comment 
as update query still gives: 

  The data content could not be loaded.

  SQL Status: 00000
  Error code: -155

  Statement does not generate a result set

  The SQL command leading to this error is:

  UPDATE "Table1" SET "Table1"."fld-flag" = FALSE WHERE "Table1"."fld-flag" = TRUE;

while running the same sql code through the tools-sql-execute produces the change and gives no errors.

setting status to 'unconfirmed' to clear the 'needinfo' flag.
Comment 4 collura 2011-12-26 00:36:54 UTC
sorry misread, setting status from 'unconfirmed' to 'new' to clear the 'needinfo' flag.
Comment 5 collura 2012-06-12 02:40:55 UTC
status is still the same as in comment#3 as of 

  LibreOffice 3.5.4.2 
  Build ID: 350m1(Build:2)

(as in fedora package libreoffice-base-1:3.5.4.2-1.fc17.x86_64)
Comment 6 QA Administrators 2014-10-23 17:32:27 UTC Comment hidden (obsolete)
Comment 7 ribotb 2014-10-24 11:19:35 UTC
Hi,

With Version: 4.3.3.1
Build ID: 7d55112667c8fcddb67bc3803796b46c93aa56b0
under Win7/x86

I create and populate Table1.
I execute query: UPDATE "Table1" set "Table1"."fld-flag" = False where "Table1"."fld-flag" = True;
- in Tools > SQL: "Command successfully executed" and result is good;
- in Query > Create a SQL Query: "Syntax error".

Bernard
Comment 8 Alex Thurgood 2015-01-03 17:38:53 UTC Comment hidden (no-value)
Comment 9 collura 2016-05-07 08:40:30 UTC
Created attachment 124891 [details]
sample database for query testing


status is still same as in comment#3 as of 

   LibreOffice 5
   Version: 5.0.6.2
   Build ID: 5.0.6.2-3.fc23

   (as in fedora package libreoffice-base-1:5.0.6.2-3.fc23.x86_64)


0) observation on comment#7

  >  - in Tools > SQL: "Command successfully executed" and result is good;
  >  - in Query > Create a SQL Query: "Syntax error".
  > 

in order to get the query to save from sql view 
you have to select Edit-"Run SQL command directly" 
otherwise it wont let you save but just gives syntax error:

     SQL Status: HY000
     Error code: 1000

     Syntax error in SQL statement

     syntax error, unexpected $end, 
     expecting BETWEEN or IN or SQL_TOKEN_LIKE


1) run the saved update query 
doesnt make any changes to table and still gives the error:

      The data content could not be loaded.

      SQL Status: 00000
      Error code: -155

      Statement does not generate a result set

2) input sql of update query into tools-sql-menu of main base window

running the same sql code through the tools-sql-execute still works 

sets the true flagged records to be false flagged and gives no errors:

      1: Command successfully executed.
Comment 10 collura 2016-06-28 04:20:47 UTC
distantly related bug as testing embedded_firebird replacement for this hsql rfe: 

   error when try open a database: 

      https://bugs.documentfoundation.org/show_bug.cgi?id=85146
Comment 11 Peter Toye 2017-01-31 15:52:47 UTC
I would suggest that it would be even netter to provide update and delete queries directly through the IDE rather than expecting the user to enter SQL commands.
Comment 12 Alex Thurgood 2017-09-07 10:38:53 UTC
*** Bug 112272 has been marked as a duplicate of this bug. ***
Comment 13 Xisco Faulí 2017-10-28 18:21:46 UTC Comment hidden (obsolete)
Comment 14 Robert Großkopf 2017-11-30 15:08:46 UTC
One hint: At this moment it is possible to update tables through the query-editor if using MySQL/MariaDB and the native connector. There won't appear any error, only a blank page of the query if query is created in direct SQL and executed in directly.

Problem: The "query" will be executed every time you go to "Queries" if you change at the right 'none' to 'document' (which should show the content of the query when the query is chosen).
Comment 15 Robert Großkopf 2017-11-30 15:29:59 UTC
Created attachment 138143 [details]
MySQL-native-connector with possibility to update data through query
Comment 16 collura 2019-05-24 01:58:32 UTC
repeated test, some improvement.

LibreO_6.1.5.2 (tested on linux) has same results as described in comment9
where update query fails to run and embedded hsqldb database is not updated.

however repeating the test with an embedded firebird_db is an improvement as described below. 

LibreO_6.1.6.3 (tested on macos) using an embedded firebird_db (instead of hsqldb)
still gives errors, BUT the Table Gets Updated :')

(as before the query wont save unless select "Run SQL command Directly" button 
on the far right of query toolbar)

the error generated by running the saved update query is

    "The data content could not be loaded."
    "Error code: 1"
    "firebird_sdbc error: Cursor is not open
         caused by isc_dsql_fetch"

despite the error, the saved update query did indeed update the table
which is confirmed by running the select query
(or by then opening the table to verify that the fields had changed 
in case you were in surprised disbelief).

I didnt have time to test other modification query types or read up on possible parallel bugs of the firebird error so I am leaving this bug open.
Comment 17 Andreas Heinisch 2024-07-10 10:33:48 UTC
Created attachment 195202 [details]
Error Message

At the moment I get the above error message. What does a user expect here? Should the query just be updated and nothing else happens?
Comment 18 Robert Großkopf 2024-07-10 13:10:52 UTC
(In reply to Andreas Heinisch from comment #17)
> 
> At the moment I get the above error message. What does a user expect here?
> Should the query just be updated and nothing else happens?

If the enhancement is working the query should be executed. There should be shown an empty result, not an error message.

But: Do we really want to get this behavior in queries? Queries should produce a result. They should never change the content of any table in the database.

I would prefer to execute all other commands, which should be executed often, by a macro. There you could suppress to execute the SQL code after it has already been executed. 
If the SQL code could be executed through a "query" you will get another security problem with "normal users", who execute the query again and again.

Here is such a problematic "query":

Table with a column "class" for pupils.

UPDATE "tablePersons" SET "class" = "class" + 1

Gives new values every time I execute this "query".
Comment 19 Andreas Heinisch 2024-07-10 13:44:07 UTC
It is not that difficult to implement this feature (use execute instead of executeQuery, i.e., [1] and [2]), but users may change their data without knowing it.

Should we improve the error message in the case where the query does not return a result set and point to Tools -> SQL?

[1] https://opengrok.libreoffice.org/xref/core/dbaccess/source/ui/dlg/directsql.cxx?r=d52a4dba#230
[2] https://opengrok.libreoffice.org/xref/core/dbaccess/source/core/api/RowSet.cxx?r=493b8ab9#1662