How is escaping treated in Perspective scripting?
I have the following namedQuery in a script:
named_query = "system/tags/getTagPath"
parameters = {"tagPath": "/my/tag_path/"}
result = system.db.runNamedQuery(named_query, parameters)
if not result or not result.getRowCount():
output = "no result"
else:
output = "result"
but still when passing a tag path with slashes the input is escaped and no result is returned.
I have enabled the option “queryString” in order to avoid automatic escaping of the tagPath input but still no result is returned even though the same input in the Testing tab returns correct results.
As far as I know, you cannot make the table name dynamic in a named query. This would have been identified much quicker if you would have included your query in the original post. Anyway, you would be better served by system.db.runPrepQuery().
query = " select * from %s where TagId = ?" % (tableName)
results = system.db.runPrepQuery(query, [tagPath], database = <myDBName>)
Note that the database name is required if you run this at the gateway scope. It can be omitted if you run it at the project level and you want to use the project’s default DB connection.
yes but the problem affected several complex queries and I didn't see some also had the table name as parameter, anyway I found this has been solved in the forum here: How to pass a table name parameter to named query?
You can make the table name dynamic in a named query. You have to set the table name parameter type to QueryString.
When a parameter is set to type QueryString, you cannot reference the parameter with a colon in your query, you must enclose it in curly braces instead.
The above query should be (assuming you have tableName parameter properly set to type QueryString):
SELECT * FROM {tableName} WHERE TagId = :tagPath
BIG warning here! Be very careful with how you handle QueryString parameters. You should NOT allow end users to directly write to these parameters in the client as you are opening yourself up to SQL injection attacks.