jDBexport does not (and will not) import/update/insert/delete into database

Dear all,

as we are repeatedly (and increasingly) asked whether jDBexport can be used to update the database (either the JOOMLA database or another one), let me clarify this issue...

jDBexport is designed as a data reporting instrument, and as such can only export data from the database(s), not import - hence the name jDBexport...

jDBexport in it's versionbranch 4.0 (and later) relies on PhpSpreadsheet as underlying library to create spreadsheets. This library is capable of reading spreadsheets. However we decided to exclude this part of the library from jDBexport and do not implement any reading feature into jDBexport.
jDBexport in it's versionbranch 3.1 and 3.2 relied on PHPExcel as underlying library to create spreadsheets. This library is capable of reading spreadsheets. However we decided to exclude this part of the library from jDBexport and do not implement any reading feature into jDBexport.
jDBexport in it's versionbranch 3.0 does not contain any libraries that can read spreadsheets.

BUT (you might want to counterargue...):
... jDBexport processes SQL statements, doesn't it? So it theoretically "could execute an INSERT sql statement", couldn't it?

Yes it could, but we actively took steps to prevent jDBexport from processing SQL queries that contain SQL keywords other than those used by SELECT queries. In the components [Options] dialog on the [Security] tab you find two configuration options connected to this topic.

Check SQL
List of disallowed words

The first option generally activates or deactivates the security check for disallowed words in the finalised SQL queries (e.g. after all substitution variables have been substituted by their actual values).
We recommend strongly, to set this to YES at all times.

The second option lists those words that are considered "disallowed". The following SQL keywords are (by default) disallowed:

ALTER, CREATE, DELETE, DROP, GRANT, INSERT, KILL, LOAD, LOCK, RENAME, REPLACE, REVOKE, SET, TRUNCATE, UPDATE

So if you need to allow certain of these keywords (for example "SET"), you could remove them from this list.

Warning
Please keep in mind, that the more permissive you are here in these two options, the more you have to trust everybody who is allowed to access the component's backend administration.


The main reason behind our reluctance here is, there are numerous issues (including security) when allowing imports/update/inserts through Excel documents (or CSV files):

To name a few:

• Overwrite existing data or not
• Which table to insert into - do you ask or do you use the sheet name and "guess"
• If a table doesn't exist, should we create it?
• what about column headers/extra info - how to handle those
• Blank lines, should they be imported or not
• For tables with auto-increment fields, if you defined the auto increment field in your spreadsheet - then you would have to drop the table and recreate it in MySQL to be able to get the IDs inserted as you wanted
• If you have to drop a table, then you have to rebuild it with all fields/types/indexes as well as foreign keys. That can be close to impossible to do automatically. You have to turn off foreign keys, checking and import/create the new data then turn the foreign keys on again and hope things match up. If you have made a mistake then you can't turn on the foreign keys again and the data integrity is not guaranteed.
• ...

There are tons of issues with building a generic upload functionality as you can see. You could get it to work if users understood and followed certain guidelines. But the risk of the user making a mistake is enormous, if you put something like this into the hands of the average Joomla user (just imagine the potential chaos, if you would allow some of your users to fully access PhpMyAdmin from the frontend of your website).

To make something decent you have to write a lot of code which checks an awful lot of things and try to prevent various mistakes. The potential support burden and liability is enough reason for us to NOT do this...

Kind regards
Ruediger Schultz