Hi everybody.
I don´t know why the next named query doesn´t work successfully.
When i run it in the query browser it Works right but when it runs like a named query and with the txt parameter i get rows empty.
SELECT * FROM Trend
WHERE PV IN (‘HC_SALA_B02’)
txt-> like string parameter
SELECT * FROM Trend
WHERE PV IN (:txt)
Thanks
Are you trying to put quotes or commas in your txt variable? If so, that is structure, not data, and cannot be supplied by a parameter. You would need a separate param for each element of the list. Named queries can’t do this.
Thank you very much Phil.
pturmel is correct, in that you're using the parameter as structure. Named Queries can do this, but you need to set your parameter to a Query String type parameter. Afterwards, you would have to change the reference to the parameter in your query (the notation for a Query String parameter is different than a Value parameter) to something like:
SELECT * FROM Trend WHERE PV IN ({txt})
I have to admit I never noticed that feature bug. These can be passed through from a client? If so, you can’t claim legacy query access is a security feature, because this is just as big a hole.
Great it works perfectly.
Thank you very much both.
Correct.
Could you elaborate on this point a bit more? I wasn't part of the talks for Client Permissions, so my knowledge on the topic is secondhand at best, but my understanding was that we wanted a way to apply security restrictions to non-Named Query SQL calls (among the other features Client Permissions can restrict).
Each Named Query has a dedicated interface to provide role and zone restrictions via the Settings tab, similar to the Client Permissions interface, so you could restrict which users can call a named query containing Query String parameters.
If you're worried about SQL Injection Attacks, you can avoid them by never using Query String parameters. I believe the interface in the Designer tries to warn about their use for this very reason.
While you are correct, there is a theoretical SQL injection risk, it's significantly mitigated, and easy to entirely prevent. The previous model, prior to named queries, is that anything that the gateway thinks is a client (ie, anything that's passed basic authentication) can then send any query text over the RPC channel to the gateway, and it will be directly executed.
With named queries, value parameters and query string parameters must match what the gateway already expects - so, you can't send arbitrary text to a named query that only accepts value parameters; it will just trigger a data type/evaluation error.
That said, query string parameters are a big (theoretical) hole, which is why, as @Paul.Scott noted, the UI (attempts) to discourage them. If you never use them, but do use client permissions, you're as protected from SQL injection as we can make you.
I can think of two improvements that can be made to protect against SQL injection:
[a-zA-Z0-9_]*) or a list of integers ([0-9]+(,[0-9]+)*) without trusting the client.We don't use JQL, although it might be possible to pass arrays to JDBC connections directly (might depend on SQL flavor/JDBC driver support, which would be a rough spot in terms of compatibility.
The latter seems like a reasonable idea, although definitely an edge case - simply not allowing SQL substitution is definitely 'preferred'. Would be good for ideas.inductiveautomation.com 
I haven’t tried this, but PostgreSQL’s driver supports direct usage of int[] and other primitive type arrays. In place of the IN (…) clause, you’d use PostgreSQL’s ANY operator:
SELECT * From someTable Where someColumn = ANY (?)
Someone who finds this might find the following useful: