QueryBuilder format errors

Im using the Query builder to create custom 'WHERE' statements for some MySQL tables.

Im using temporary states to store the QueryBuilder.asSql value to, and then executing my query via javascript i.e.


where MySqlQuery looks like:

"SELECT * FROM Contacts" {{ MyTempState.value }}...anyone see why im getting formatting errors doing this?

the value in MyTempState in this case looks like:
"WHERE "innodb.Companies.repid" = '77'"

the full string getting send to my db looks like:
SELECT * FROM innodb.Contacts JOIN innodb.Companies on innodb.Companies.contactid = innodb.Contacts.contactone_id WHERE\n "innodb.Companies.repid" = '77'

So i guess the issue is the newlines, and quotes its entering?


Hi @msd5079

MySql + the query builder can be tricky. Can you try adding .replaceAll('"','') to resolve the issue with the quotes?

Also, if you haven't done so yet, you'll need to disable converting queries to prepared statements on your MySql resource page. As a heads up, this is a resource setting that only Retool Admins can change.

By default, all of our SQL queries are converted to prepared statements to prevent SQL injection, meaning that table/database names and SQL functions aren't able to be defined using a string created dynamically. The main reason we currently convert all statements into prepared statements, is so that users can't enter malicious syntax (like DROP TABLE) into the variable fields.

You can disable this setting in the resource setup, but keep in mind the potential of submitting dangerous SQL through any of the variables referenced in a query. Disabling prepared statements can also break other existing queries. If that's something you'd like to explore, I often recommend setting up another copy of a resource with that setting enabled to help limit the surface area that you have to keep in mind SQL injection for.

1 Like