MYSQL: Can't use template variable for database name in query

App Building
, , ,
1

Hello from the team at DoltHub.com, the version-controlled MySQL database. We recently published a blog article where we connect Retool to a cloud hosted dolt database for version-control functionality.

While building the sample app, we noticed a bug with query templating which only occurs when prepared statements are enabled in the resource settings.

MySQL supports specifying the database name in the query:

select * from `dbName`.tableName;

In Retool, if you use a variable as the dbName, a syntax error is thrown by MySQL because the value of tempVarDbName gets wrapped in single quotes.

select * from `{{tempVarDbName.value}}`.tableName;

For example, if tempVarDbName equals testDb, then the above query template compiles to:

select * from `'testDb'`.tableName;

Which is invalid syntax.

Couple questions:

  • Is disabling prepared statements the best way to resolve this issue?
  • Is this a bug?

We have a sample app that we are happy to share with the reproduction.

2

Hey druvv! Welcome to the forum :-).
This is based on injection-safety, as it's quite untraditional you want user input on the database you're selecting from. If you really know what you're doing, and the app is only used by you and a close group of users, you could disable injection safety.

However, I think there may be a better route to get to dynamic database selection. How many different options are there, and are they fairly static?

Jonathan

1 Like