# Variable table name in select query

**URL:** <https://forum.inductiveautomation.com/t/variable-table-name-in-select-query/58909>\
**Category:** Ignition\
**Tags:** ignition81\
**Created:** [April 27, 2022, 5:38am UTC](https://forum.inductiveautomation.com/t/variable-table-name-in-select-query/58909 "2022-04-27T05:38:28Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jaydavid97](https://avatars.discourse-cdn.com/v4/letter/j/a587f6/32.png) [@jaydavid97](https://forum.inductiveautomation.com/u/jaydavid97)\
**Post date:** [April 27, 2022, 5:38am UTC](https://forum.inductiveautomation.com/t/variable-table-name-in-select-query/58909/1 "2022-04-27T05:38:28Z")

</div>

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.

```auto
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.

 ![Query](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/1/3123158812e5f201370b19c52dc7aadc24ce8dc6.png)

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [April 27, 2022, 5:55am UTC](https://forum.inductiveautomation.com/t/variable-table-name-in-select-query/58909/2 "2022-04-27T05:55:17Z")

</div>

Use a **named query** instead.

 ![Named query jaydavid97](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/7/278f21bd272d4fe6573ef58b17549cbe4a8284a4.png)  
_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+Queries?searchId=4W6WDN7S7)

[https://docs.inductiveautomation.com/display/DOC81/Named+Query+Bindings?searchId=LCJUS6N40](https://docs.inductiveautomation.com/display/DOC81/Named+Query+Bindings?searchId=LCJUS6N40)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [April 27, 2022, 11:32am UTC](https://forum.inductiveautomation.com/t/variable-table-name-in-select-query/58909/3 "2022-04-27T11:32:49Z")

</div>

> [@Transistor](#):
>
> Be aware of the possibility of SLQ injection on the QueryString. See the docs below.

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.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [April 27, 2022, 12:32pm UTC](https://forum.inductiveautomation.com/t/variable-table-name-in-select-query/58909/4 "2022-04-27T12:32:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Steve\_Laubach](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steve_laubach/32/60900_2.png) [@Steve\_Laubach](https://forum.inductiveautomation.com/u/Steve_Laubach)\
**Post date:** [April 27, 2022, 12:51pm UTC](https://forum.inductiveautomation.com/t/variable-table-name-in-select-query/58909/5 "2022-04-27T12:51:34Z")

</div>

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.  
 ![Bobby Tables](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/4/b4f5a62d0a3a74fdf6a3b208329e6f9b623a2768.png)