search

airbytehq / PyAirbyte

PyAirbyte brings the power of Airbyte to every Python developer.
https://docs.airbyte.com/pyairbyte
Other
225 stars 36 forks source link

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

Closed flash1293 closed 8 months ago

flash1293 commented 8 months ago

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:

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

aaronsteers commented 8 months ago

Resolved by: