Hello,
The GUI mode for inserting records can use changeset of object (i.e {{table1.data}}) to insert data into a table quit easily. However, it is not dynamic in selecting the table - user needs to pick the table from a drop down list.
Would like to do something like this but obviously does not work.
INSERT INTO {{table_name}}
VALUES {{table1.data}}
Is there a way to dynamically select the table and still use an object to insert data into the table?
@ricky-raven
Yes, it is absolutely doable. If, for example, you wanted the table that you are inserting into to be dynamic the safest option would be to use a select component to dynamically pass in the string name of the table that you are trying to INSERT INTO. The one caveat here is that you would need to turn off prepared statements.
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.
Hi Lauren,
I am aware that you can disable that setting. I want to know if there is a way to easily convert the array object {{table1.data}} which is key value pairs into a string that can be used in the SQL statement. I am not not that familiar with JS.
You can use JSON.stringify(), I am linking some MDN documentation on that here for you. Hope that helps!
