I have a question regarding arguments within prep queries in perspective scripting.
Is it possible to parameterize a table name using arguments within a PrepUpdate query? I want to insert a value into a database table based on the table the user chooses. The user choice comes from a dropdown, so I don't think it opens me up to a SQL injection attack. Simplified example:
userChoice = "Warehouse"
value = 123
query = "INSERT INTO (?) VALUES (?)"
args = [userChoice,value]
system.db.runPrepUpdate(query,args)
I know I could use a Named Query, but from my understanding prep queries are faster? Thanks for any help!
No, you can't parameterize the table name, because it's part of the structure of the query, not the arguments.
If you actually need dynamic SQL, then your options are assembling the query and executing it via scripting, or using a named query using the (unsafe) query string parameter type.
In either case, do not accept arbitrary user input unvalidated, as both are open to SQL injection vulnerabilities.
I'm curious where you got that impression from. I haven't really done any benchmarking, but the slowest part of the underlying execution (the round trip to the DB) is going to be using the exact same JDBC mechanisms, so I would expect either approach to be basically the same, speed wise. Especially since safely calling a named query with query string parameters requires a script in front of it to validate inputs.
Gotcha, thanks! Honestly, someone that was helping me with Ignition told me about the speed thing. I was wondering why/if it was true. But I generally find it easier to see the query within my script, so I would've mostly been using prep queries either way.
2 Likes