-
Goal: migrate Retool Database schema from production to staging
-
Steps:
- Ensure
Disable converting queries to prepared statements
is unchecked in Resource settings for Retool Database - Create table in Retool Database in the production environment with a dash "-" in its name, such as
example-table
- Try Schema Migration from production to staging, it will fail on the dash "-"
- Ensure
-
Details: Having
Disable converting queries to prepared statements
unchecked, from what I read here, is the safer option to avoid SQL Injection risks. It unfortunately has the side-effect of breaking Schema Migration if your tables or primary key constraints have a dash in their name. I believe it's the same reason that table names cannot be passed in as a string with this safer approach, and the parser breaks on the dash. -
Screenshots:
-
App json export: Not relevant for bug.
Hello @Abhilash_Nair!
Thank you for finding and reporting this
I will look to reproduce this and file a bug report as needed to our engineering team.
We do recommend not using the '-' symbol for table names. A better name convention would be camelCase.
Also on the topic of prepared statements and turning them on/off. According to ur docs, keeping prepared statements turned ON is safer and prevents SQL injections.
Turning prepared statements OFF is more risky for SQL injections since our code makes sure the statements are safe for the DB when 'preparing' them.
Turning them off is a last case scenario where you need to dynamically interpolate a value into your SQL query that is not working with prepared statements.
Hope this helps!
Thanks Jack. Re: prepared statements, I hope it was clear in my original post that I agree keeping prepared statements on is the safer option. Semantics are tricky here because keeping prepared statements on means keeping the option unchecked. Since it's an option to Disable converting queries to prepared statements.
Completely unrelated, but instead of camel case, I use _
underscores which I find slightly more legible.
Ahhh yes that makes sense, I was slightly confused by the wording but glad we are on the same page