# Duplicating & Renaming SQL Master Table **URL:** \ **Category:** Ignition\ **Tags:** vision\ **Created:** [January 18, 2025, 2:24am UTC](https://forum.inductiveautomation.com/t/duplicating-renaming-sql-master-table/101064 "2025-01-18T02:24:21Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Warren\_McDonald](https://avatars.discourse-cdn.com/v4/letter/w/91b2a8/32.png) [@Warren\_McDonald](https://forum.inductiveautomation.com/u/Warren_McDonald)\ **Post date:** [January 18, 2025, 2:24am UTC](https://forum.inductiveautomation.com/t/duplicating-renaming-sql-master-table/101064/1 "2025-01-18T02:24:21Z")
I have a project in Vision where I am running a recipe database system through SQL and populating screen data using numerous button commands attached to NQ's. I have all of these basic NEW, SAVE, DELETE, LOAD, INSERT functions working. The area I am having trouble with is where I'm trying to attach a NQ to the "NEW" button on a particular screen. This NQ needs to create a new SQL table, referencing the "master" as the framework to duplicate. The name of the new SQL table will be referencing a vision tag holding a string value which is a process name. My "NEW" button is running these scripts: ```python system.db.runNamedQuery("INSERT NEW PROCESS") system.db.runNamedQuery("NEW MASTER TABLE") processname = event.source.parent.getComponent('Master Recipe Dropdown').data parameters = {"process_name": processname} system.db.runNamedQuery("RENAME TABLE", parameters) ``` The scripts: INSERT NEW PROCESS = ```python INSERT INTO master_processes (StepStart, StepEnd, Name) VALUES ('1', '0', 'New Process') ``` This script is functioning. I get the correct row added into my list of processes as a generic "New Process" name. NEW MASTER TABLE = `CREATE TABLE new_table AS SELECT * FROM master` This is currently working. new\_table populates with the same columns as master RENAME TABLE = ```python ALTER TABLE new_table RENAME TO :process_name ``` This is not working - getting syntax error. The ":process\_name" is referencing the parameter string. I'd like this to be a variable name that is attached to that drop down data shown above. What do I need to change on my rename script? Any help is greatly appreciated. ---
**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:** [January 18, 2025, 10:14am UTC](https://forum.inductiveautomation.com/t/duplicating-renaming-sql-master-table/101064/2 "2025-01-18T10:14:15Z")
The `:Value` parameterType can only be used in WHERE clauses. (There may be some exceptions - I'd have to check). You need to change to a `{QueryString}` to use it in the RENAME TO function. Be aware that this may expose you to SQL injection and precautions have to be taken. The idea of creating separate tables for each user-defined process doesn't seem like a good one. The usual approach would be to have one table and add a column to record the process name for each row of the table. It will simplify your database maintenance, your INSERTS and UPDATES and your data retrieval. ---
**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:** [January 18, 2025, 3:28pm UTC](https://forum.inductiveautomation.com/t/duplicating-renaming-sql-master-table/101064/3 "2025-01-18T15:28:04Z")
> [@Transistor](#): > > only be used in WHERE clauses No, `:value` parameters can be used for any **data**. They cannot be used for any SQL **structure** , which includes column/table/schema names. > [@Transistor](#): > > Be aware that this may expose you to SQL injection and precautions have to be taken. The precaution needed is to not allow user-entered values in QueryString parameters. At all. If this use-case allows the user to choose their table name, it is **not safe**. ---
**Author:** ![Warren\_McDonald](https://avatars.discourse-cdn.com/v4/letter/w/91b2a8/32.png) [@Warren\_McDonald](https://forum.inductiveautomation.com/u/Warren_McDonald)\ **Post date:** [January 19, 2025, 3:55am UTC](https://forum.inductiveautomation.com/t/duplicating-renaming-sql-master-table/101064/4 "2025-01-19T03:55:34Z")
> [@pturmel](#): > > case allows the user to choose their table name, it is **not safe**. I'm not sure I'm understanding exactly what you're saying here but there is an "UPDATE" button that pulls a string from a textbox to use to update the process name, which in turn would update the table name simultaneously. There is no limit to how many times this can be done - is that what you mean here? Is that a problem? ---
**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:** [January 19, 2025, 9:45am UTC](https://forum.inductiveautomation.com/t/duplicating-renaming-sql-master-table/101064/5 "2025-01-19T09:45:09Z")
These two (randomly chosen) introductory articles explain how SQL injection can be performed by a user on a vulnerable system. [SQL Injection](https://www.w3schools.com/sql/sql_injection.asp) and [What Is SQL Injection and How Does It Work? | Black Duck](https://www.blackduck.com/glossary/what-is-sql-injection.html). In your proposed system the user can, for example, type into the text box commands to delete records or DROP the whole table. ---
**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:** [January 19, 2025, 3:08pm UTC](https://forum.inductiveautomation.com/t/duplicating-renaming-sql-master-table/101064/6 "2025-01-19T15:08:42Z")
> [@Warren\_McDonald](#): > > pulls a string from a textbox to use to update the process name This is not safe. You are feeding a user-entered string directly into SQL. A malicious user can break your DB and possible break your entire system. You could, theoretically, run some kind of validation (gateway side) on that string to be sure it is just a valid table name, but such validation tends to be fragile. > [@Transistor](#): > > The idea of creating separate tables for each user-defined process doesn't seem like a good one. I endorse this. You should not be making new tables. Make a separate table for Process Names and use the primary key of that as a foreign key in your main table. It also frees you from needing process names that are also valid table names. ---
**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\ **Post date:** [January 19, 2025, 3:43pm UTC](https://forum.inductiveautomation.com/t/duplicating-renaming-sql-master-table/101064/7 "2025-01-19T15:43:04Z")
> **[Exploits of a Mom](https://xkcd.com/327/)**