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:
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 =
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 =
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.
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.
1 Like
No, :value parameters can be used for any data. They cannot be used for any SQL structure, which includes column/table/schema names.
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.
2 Likes
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?
These two (randomly chosen) introductory articles explain how SQL injection can be performed by a user on a vulnerable system.
SQL Injection and What Is SQL Injection and How Does It Work? | Black Duck. In your proposed system the user can, for example, type into the text box commands to delete records or DROP the whole table.
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.
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.
1 Like