🐛 Bug: columns that match SQL reserved words cause failure during insert

[flash1293]

When the final table is created in SQL caches, field names matching SQL keywords cause the query to break.

For example when reading from the pokeapi connector, the following query is issued:

CREATE TABLE main.pokemon (
  id VARCHAR,
  name VARCHAR,
  base_experience VARCHAR,
  height VARCHAR,
  is_default VARCHAR,
  order VARCHAR,
  weight VARCHAR,
  abilities VARCHAR,
  forms VARCHAR,
  game_indices VARCHAR,
  held_items VARCHAR,
  location_area_encounters VARCHAR,
  moves VARCHAR,
  sprites VARCHAR,
  species VARCHAR,
  stats VARCHAR,
  types VARCHAR,
  past_types VARCHAR
)

As order is also a SQL keyword, the query fails.

This is a problem throughout the SQLCacheBase class which uses string concatenation to interface with the SQL database.

As a stopgap solution the individual places in the code can be patched by escaping field names one by one to avoid having to refactor the whole class:

• https://github.com/airbytehq/airbyte/blob/d9b500c060c4d9ae0ab5d825443c11d90ea1fc18/airbyte-lib/airbyte_lib/caches/base.py#L314
• https://github.com/airbytehq/airbyte/blob/d9b500c060c4d9ae0ab5d825443c11d90ea1fc18/airbyte-lib/airbyte_lib/caches/base.py#L358
• https://github.com/airbytehq/airbyte/blob/d9b500c060c4d9ae0ab5d825443c11d90ea1fc18/airbyte-lib/airbyte_lib/caches/base.py#L642
• https://github.com/airbytehq/airbyte/blob/d9b500c060c4d9ae0ab5d825443c11d90ea1fc18/airbyte-lib/airbyte_lib/caches/base.py#L707

However, mid term the refactor to sqlachemy should be performed, also to avoid security issues via SQL injection.

[aaronsteers]

Resolved by:

• airbyte-lib: Escape column names airbyte#34969