search

taozhi8833998 / node-sql-parser

Parse simple SQL statements into an abstract syntax tree (AST) with the visited tableList and convert it back to SQL
https://taozhi8833998.github.io/node-sql-parser/
Apache License 2.0
820 stars 183 forks source link

Snowflake: Include schema name in addition to database and table name in table list #2159

Closed chris-codaio closed 1 month ago

chris-codaio commented 1 month ago

Describe the bug When retrieving the tableList from parsing, table references should be of the form dbName:schemaName:tableName

Database Engine Snowflake

To Reproduce parser.tableList(SELECT * from foo.bar.baz, {database: 'Snowflake'});

[ 'select::foo::baz' ]

Expected behavior parser.tableList(SELECT * from foo.bar.baz, {database: 'Snowflake'});

[ 'select::foo::bar::baz' ]

Additional context Schema is an important part of enforcing table whitelisting since skipping this means one could target a schema other than the one desired to be whitelisted.

taozhi8833998 commented 1 month ago

@chris-codaio In some dialects, there is no schema. To maintain a constant white list check, I can include the schema name, which might be [ 'select::foo.bar::baz' ].

chris-codaio commented 1 month ago

@chris-codaio In some dialects, there is no schema. To maintain a constant white list check, I can include the schema name, which might be [ 'select::foo.bar::baz' ].

Sounds reasonable; or you could accept an opts parameter which specifies the format of this field? Let users opt-in to [ 'select::foo.bar.baz' ] if they want?