Hi,
I am hoping you could help me with a query I am running in the Database Query Browser from the designer.
I am attempting to use dynamic sql for this.
SET @TABLE_NAME:='sql_table_1';
SET @SQL:= CONCAT('SELECT * FROM ', @TABLE_NAME , ' LIMIT 100');
PREPARE dynamic_statement FROM @SQL;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;
My end goal is to dynamically change the table name from an input selected by the user but I seem to be running intro trouble when the table name is variable. Is there any way around this?
Its worth mentioning that this query executes fine when run on HeidiSQL and querying the DB directly.
Thanks in advance.
Use a named query instead.
Example of a named query with table name passed as query string and some WHERE clause parameters.
You can then call the named query in a binding and pass in the parameters.
Be aware of the possibility of SLQ injection on the QueryString. See the docs below.
https://docs.inductiveautomation.com/display/DOC81/Named+Queries?searchId=4W6WDN7S7
https://docs.inductiveautomation.com/display/DOC81/Named+Query+Bindings?searchId=LCJUS6N40
2 Likes
This can't be emphasized enough. Don't let your users type in a table name. Always present a list of allowed tables with a numeric key. Transform that key into a table name just before handing off to the named query parameter.
3 Likes
The convenience in developing of having a SELECT * FROM {tableName} type of named query is not worth the security issues it raises imo. It kind of defeats the point of a named query as well.
I would hardcode select * from someTable for each table that is used in your application- you only need to do it once. Given you want to use a SELECT * as well, it’s not like you will need to edit it when you add or remove columns.
Yeah, you really should take the risk of a SQL injection attack seriously. You may want to limit some of the permissions on your service account that Ignition is using for the database connection if you can. If you’re using automatic table creation you might not be able to or you might want to create a second connection to the database that can’t drop tables and link that connection to your named query that is using a dynamic table name.
XKCD did a comic about this situation.
