search

cerebris / jsonapi-resources

A resource-focused Rails library for developing JSON:API compliant servers.
http://jsonapi-resources.com
MIT License
2.32k stars 529 forks source link

Multi-tenancy SQL syntax error caused by ActiveRelationResource (0.10.x) #1310

Open Albertjan90 opened 4 years ago

Albertjan90 commented 4 years ago

This issue is a (choose one):

Checklist before submitting:

Description

After upgrading JSONAPI::Resources to version 0.10.2 (coming from 0.9), we noticed the API is causing SQL errors when loading resources from a different database. We have a multi-tenant database, so some resources are fetched from a shared database. It seems this line is causing the syntax to break: https://github.com/cerebris/jsonapi-resources/blob/master/lib/jsonapi/active_relation_resource.rb#L117

Fetching resources from a different database results in the given query:

SELECT database.table.id AS database.table_id (...)

The dot (.) in the alias is illegal. This results in the following error:

Internal Server Error: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.table_id ' at line 1:
lgebhardt commented 4 years ago

@Albertjan90 Could you test out the #1318 PR and add comments there? I don't have the test framework setup to handle multi tenancy, and unfortunately I'm a bit short on time at the moment to work on that.

This PR converts the '.' to '_' in the table_name to handle the same table name in both databases. Not sure if that's likely to happen, but without it I think we could have issues. Hopefully that doesn't create too long of aliases.