Named Queries in script escape tag paths wrong

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.

Set the parameter back to a value. In python, the / character is escaped by the / character. Try using

parameters = {"tagPath":"//my//tag_path//"}

EDIT:
See below the correction by @zacht on the escape character

Its slashes the other way (\) that are escape characters in python.

How do you know this?

What's your query (why are you passing a tagpath into SQL)? Does the query work with this input in the testing tab?

1 Like

I have a strong feeling it’s an escape problem because I had a similar problem solved (named-queries-syntax-error-when-passing-tag-path-strings.

My query is: SELECT NiceName FROM my_tags_mapping WHERE TagPath = {tagPath}

and the tag path can be anything like “[default]/Edulent/Giroste/Giroste1_Met01EnergyImm”

and yes the query works ok in the testing tab as is.

I tried with a

parameters = {"tagPath": tagPath.replace("/", "/")}

but it doesn't work.

try using

parameters = {"tagPath":r"/my/tag_path/"}

and make sure you don’t use curly quotes, use straight quotes. Several of your snippets have curly quotes in them.

ok the tagPath works with parameter type Value (QueryString is not necessary) and by passing the parameter as is thus:

named_query = "my/named/query"
tagPath = "{}".format(tagPath)
parameters = {"tagPath": tagPath}

the problem seems to be the table name, so if you pass the table name such as:

named_query = "my/named/query"
tagPath = "{}".format(tagPath)
parameters = {"tableName": mytable_name, "tagPath": tagPath}

and the query is something like:

SELECT * FROM :tableName where TagId = :tagPath

the query breaks, you need to hard wire the name of the table such as:

SELECT * FROM myTable where TagId = :tagPath

apparently the table name is treated differently from other fields in the query

PS: ignore curly quotes, these are added by the CMS of this forum but they are normal quotes indeed

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?

Marking as solved, thank you

1 Like

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.