search

t-rex-tileserver / t-rex

t-rex is a vector tile server specialized on publishing MVT tiles from your own data
https://t-rex.tileserver.ch/
MIT License
556 stars 69 forks source link

genconfig doesn't quote column/table names #283

Open joto opened 2 years ago

joto commented 2 years ago

When using the genconfig command, the SQL queries generated do not quote the column/table names. This is necessary if they contain "strange" characters. So either all column/table names should be double quoted or at least those containing non-identifier characters.

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

pka commented 2 years ago

genconfig tries to generate a human readable configuration and lets quoting to be done by the user: https://github.com/t-rex-tileserver/t-rex/blob/af2a21425c5fe233034dd8d657c1758a7d6ac012/t-rex-core/src/datasource/postgis_ds.rs#L450-L451

Maybe there should be an option for generating a template with quoted column/table names, for cases when there a a log of ugly names.

joto commented 2 years ago

At the moment the query it generates doesn't work and I would consider this a bug. There is also a security consideration here, although minor, because the user should hopefully know what they are doing.

It should be possible to figure out which column names need quoting and which don't and only quote those that need it. This way you get the best of both worlds: A working query and minimal quoting for readability and security.

pka commented 2 years ago

A good compromise would be removing double quotes only if select_list doesn't contain special characters.