[Bug]: reserved SQL word as an attribute table column name causes multiple failures

[geografin]

Describe the bug A clear and concise description of what the bug is.

To Reproduce Steps to reproduce the behavior:

1. Go to ['...'](https://www.hydrosheds.org/products/hydrobasins) and download any standard region.
2. Download lake polygons from here https://www.hydrosheds.org/products/hydrolakes
3. Import lakes with GUI Windows
4. Close GUI and export one basin at your choice as shp file as it is with QGIS for example 
5. Run docker with grass and try to import this basin with v.in.ogr.
6. Try to v.select lakes with this basin
7. See, the table for lakes can't be found gy GRASS
8. check with db.tables, that no tables you have in the database. 
9. Check that Iink in the dbln file exists.

Expected behavior Importing files shouldn't break database connections.

Screenshots If applicable, add screenshots to help explain your problem.

System description (please complete the following information):

• Operating System: [e.g. Windows, Linux, ..., incl. version]
• GRASS GIS version 8.4.0dev (after last commit) https://github.com/OSGeo/grass/pull/3605

Additional context As mentioned in https://github.com/OSGeo/grass/issues/3604 GUI can't really open attribute table correctly, as reserved names of columns exist, so the last commit doesn't solve the problem of importing files too. in database browser I see that no tables there are in the database. When I try to add a table in GUI I get "ERROR: There is already a table linked to layer <1>"

[marisn]

I tested importing and it works just fine. Attribute table is created and I am able to query it from GUI. Still it is not the only place where column names are not quoted properly thus e.g. opening an attribute table will fail. I expect other tools to fail too. If you are not interested in debugging the issue, you can use the "columns" parameter of v.in.ogr to specify a list of new column names to rename "order" into e.g. "order_".

[geografin]

So you propose to change these names, in that case all will be working fine? I am interested in debuggin.

[marisn]

So you propose to change these names, in that case all will be working fine? I am interested in debuggin.

GRASS does not use secure SQL code (quoted column/table names, parameterized SQL expressions) thus column/table names being SQL keywords ("ORDER" in your case) will cause failures. We have made first attempts at improving things but it will take long time (if ever) to fix all problematical places. If you are not interested in helping us to find and fix all places where things go wrong, just rename the "order" column into anything else and carry on with your analysis.

[marisn]

See also: #3604 Related PRs: #3614 #3612 #3605

[ninsbl]

See also: https://trac.osgeo.org/grass/ticket/3071

[geografin]

Renaming and lowering letters doesn't help at all. I delete the file, then I try again with command: v.in.ogr input=$FILES_PATH/$LOCATION_FILE output=basin columns=id,bas_id,next_d,next_s,main_b,dist_s,dist_m,sub_a,up_a,pfaf_id,side_,lake,endo_,coast,order_,sort_ --overwrite and

Check if OGR layer <basin2> contains polygons...
 100%
Creating attribute table for layer <basin2>...
Column name <HYBAS_ID> renamed to <BAS_ID>
Column name <NEXT_DOWN> renamed to <NEXT_D>
Column name <NEXT_SINK> renamed to <NEXT_S>
Column name <MAIN_BAS> renamed to <MAIN_B>
Column name <DIST_SINK> renamed to <DIST_S>
Column name <DIST_MAIN> renamed to <DIST_M>
Column name <SUB_AREA> renamed to <SUB_A>
Column name <UP_AREA> renamed to <UP_A>
Column name <SIDE> renamed to <SIDE_>
Column name <ENDO> renamed to <ENDO_>
Column name <ORDER> renamed to <ORDER_>
Column name <SORT> renamed to <SORT_>
DBMI-SQLite driver error:
Error in sqlite3_prepare():
table basin already exists

DBMI-SQLite driver error:
Error in sqlite3_prepare():
table basin already exists

ERROR: Unable to create table: 'create table basin ("ID" integer, "BAS_ID"
       integer, "NEXT_D" integer, "NEXT_S" integer, "MAIN_B" integer,
       "DIST_S" double precision, "DIST_M" double precision, "SUB_A" double
       precision, "UP_A" double precision, "PFAF_ID" integer, "SIDE_"
       varchar ( 6 ), "LAKE" integer, "ENDO_" integer, "COAST" integer,
       "ORDER_" integer, "SORT_" integer)'

Then I try to forcely delete basin vector from linux terminal and rerun the program: the basin is imported, but

DBMI-SQLite driver error:
Error in sqlite3_prepare():
select * from HydroLAKES_polys_v10 where 0 = 1
no such table: HydroLAKES_polys_v10

DBMI-SQLite driver error:
Error in sqlite3_prepare():
select * from HydroLAKES_polys_v10 where 0 = 1
no such table: HydroLAKES_polys_v10

here we are - the table is broken, this means that the file wasn't imported correctly. Problem still is existing.

[geografin]

I also don't understand how the file can exist befor is was imported. Seems that some traces of table in the database exist and db.tables command doesn't see them.

[geografin]

And very interesting is that if I import a part of lakes vector, named test_lakes (no reserved column names!), then rename it from Linux to HydroLAKES_polys_v10 we see the following: That is - name was changed, but the name of table - not. From GUI it is renaming with the table.

[geografin]

So, what's wrong with my command or with my file basin2.shp? Why it isn't imported even while columns names are changed?

[geografin]

I tried with lower case. It doesn't help. And also I deleted location and recreated it. All the same. My last files are here - basin and lakes shapes. https://drive.google.com/file/d/1gMfyCEnOlhibrnfLEMI2BTyYZLXgEX5j/view?usp=drive_link When I run db.tables from Linux I get "basin", from GUI I get HydroLAKES_polys_v10. It's very strange.

[marisn]

That is - name was changed, but the name of table - not. From GUI it is renaming with the table.

Per design. Table names are not required to match map names.

[tmszi]

@geografin after you successfully import your shapefile into GRASS GIS database, rename vector map table columns which names are DB reserved keywords with db.execute (v.db.renamecolumn module is actually not working or GUI tool) module, please.

example of renaming ORDER column to renamed column name:

GRASS hybas/PERMANENT:~ > db.execute sql='ALTER TABLE hybas_lake_eu_lev01_v1c RENAME COLUMN "ORDER" TO renamed'

[marisn]

Renaming and lowering letters doesn't help at all.

You are holding it wrong (a.k.a. I can not reproduce the issue):

grass -c epsg:4326 grassdata/hydro2/

v.in.ogr input=HydroLAKES_polys_v10.shp output=lakes
db.select sql="select * from lakes limit 1"
cat|Hylak_id|Lake_name|Country|Continent|Poly_src|Lake_type|Grand_id|Lake_area|Shore_len|Shore_dev|Vol_total|Vol_res|Vol_src|Depth_avg|Dis_avg|Res_time|Elevation|Slope_100|Wshd_area|Pour_long|Pour_lat
1|1|Caspian Sea|Russia|Europe|SWBD|1|0|377001.91|15829.37|7.27|75600000|0|1|200.5|8110.642|107883|-29|-1|1404108|47.717708|45.591934

v.in.ogr input=hybas_eu_lev01_v1c.shp output=basins columns=cat,hybas_id,next_d,next_s,main_b,dist_s,dist_m,sub_a,up_a,pfaf_id,endo_,coast,order_,sort_
db.select sql="select * from basins limit 1"
cat|hybas_id|next_d|next_s|main_b|dist_s|dist_m|sub_a|up_a|pfaf_id|endo_|coast|order_|sort_
1|2010000010|0|2010000010|2010000010|0|0|17858904|17858904|2|0|1|0|1

[geografin]

I really don't understand now. Because if I open GUI db.tables gives 'HydroLAKES_polys_v10', from Linux the same Location - db.tables give 'basin'

[geografin]

Sorry. The only problem here there was the following: I didn't exported $GISDBASE variable (I used other name) - and somewhere GRASS uses exactly GISDBASE name. So Always you should set the name of GRASSDATA folder as GISDBASE. That will solve all these problems. Thanks.