# Named query problem **URL:** \ **Category:** Ignition\ **Created:** [February 11, 2019, 3:15pm UTC](https://forum.inductiveautomation.com/t/named-query-problem/22468 "2019-02-11T15:15:33Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![eduardo.ojalvo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/eduardo.ojalvo/32/14356_2.png) [@eduardo.ojalvo](https://forum.inductiveautomation.com/u/eduardo.ojalvo)\ **Post date:** [February 11, 2019, 3:15pm UTC](https://forum.inductiveautomation.com/t/named-query-problem/22468/1 "2019-02-11T15:15:34Z")
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 ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 11, 2019, 3:56pm UTC](https://forum.inductiveautomation.com/t/named-query-problem/22468/2 "2019-02-11T15:56:22Z")
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. ---
**Author:** ![eduardo.ojalvo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/eduardo.ojalvo/32/14356_2.png) [@eduardo.ojalvo](https://forum.inductiveautomation.com/u/eduardo.ojalvo)\ **Post date:** [February 11, 2019, 4:13pm UTC](https://forum.inductiveautomation.com/t/named-query-problem/22468/3 "2019-02-11T16:13:34Z")
Thank you very much Phil. ---
**Author:** ![Paul.Scott](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul.scott/32/4578_2.png) [@Paul.Scott](https://forum.inductiveautomation.com/u/Paul.Scott)\ **Post date:** [February 11, 2019, 4:15pm UTC](https://forum.inductiveautomation.com/t/named-query-problem/22468/4 "2019-02-11T16:15:02Z")
> [@eduardo.ojalvo](#): > > SELECT \* FROM Trend > WHERE PV IN (:txt) 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.](https://docs.inductiveautomation.com/display/DOC79/Named+Query+Parameters#NamedQueryParameters-QueryString) 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})` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 11, 2019, 4:20pm UTC](https://forum.inductiveautomation.com/t/named-query-problem/22468/5 "2019-02-11T16:20:37Z")
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. ---
**Author:** ![eduardo.ojalvo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/eduardo.ojalvo/32/14356_2.png) [@eduardo.ojalvo](https://forum.inductiveautomation.com/u/eduardo.ojalvo)\ **Post date:** [February 11, 2019, 4:37pm UTC](https://forum.inductiveautomation.com/t/named-query-problem/22468/6 "2019-02-11T16:37:19Z")
Great it works perfectly. Thank you very much both. ---
**Author:** ![Paul.Scott](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul.scott/32/4578_2.png) [@Paul.Scott](https://forum.inductiveautomation.com/u/Paul.Scott)\ **Post date:** [February 11, 2019, 4:46pm UTC](https://forum.inductiveautomation.com/t/named-query-problem/22468/7 "2019-02-11T16:46:57Z")
> [@pturmel](#): > > These can be passed through from a client? Correct. > [@pturmel](#): > > If so, you can’t claim legacy query access is a security feature, because this is just as big a hole. 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. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [February 11, 2019, 5:02pm UTC](https://forum.inductiveautomation.com/t/named-query-problem/22468/8 "2019-02-11T17:02:33Z")
> [@pturmel](#): > > 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. 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. ---
**Author:** ![Sanderd17](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/sanderd17/32/8735_2.png) [@Sanderd17](https://forum.inductiveautomation.com/u/Sanderd17)\ **Post date:** [February 12, 2019, 7:32am UTC](https://forum.inductiveautomation.com/t/named-query-problem/22468/9 "2019-02-12T07:32:38Z")
I can think of two improvements that can be made to protect against SQL injection: - Allow passing lists (single-column datasets) as a parameter, which seems to be supported in JQL ([https://stackoverflow.com/questions/1557085/setting-a-parameter-as-a-list-for-an-in-expression](https://stackoverflow.com/questions/1557085/setting-a-parameter-as-a-list-for-an-in-expression)) - Allow server-side checking of string parameters. So when you do allow a plain-text part to be passed (like a column name or a list), you can check it with a regex f.e. Then you can check if it’s an identifier (`[a-zA-Z0-9_]*`) or a list of integers (`[0-9]+(,[0-9]+)*`) without trusting the client. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [February 13, 2019, 4:28pm UTC](https://forum.inductiveautomation.com/t/named-query-problem/22468/10 "2019-02-13T16:28:02Z")
> [@Sanderd17](#): > > Allow passing lists (single-column datasets) as a parameter, which seems to be supported in JQL ([https://stackoverflow.com/questions/1557085/setting-a-parameter-as-a-list-for-an-in-expression](https://stackoverflow.com/questions/1557085/setting-a-parameter-as-a-list-for-an-in-expression)) 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](http://ideas.inductiveautomation.com) 🙂 ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 13, 2019, 5:32pm UTC](https://forum.inductiveautomation.com/t/named-query-problem/22468/11 "2019-02-13T17:32:19Z")
I haven’t tried this, but [PostgreSQL’s driver supports](https://jdbc.postgresql.org/documentation/head/arrays.html) direct usage of int[] and other primitive type arrays. In place of the IN (…) clause, you’d use [PostgreSQL’s ANY operator](https://www.postgresql.org/docs/11/functions-comparisons.html#id-1.5.8.28.16): ```auto SELECT * From someTable Where someColumn = ANY (?) ``` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [January 5, 2024, 7:41pm UTC](https://forum.inductiveautomation.com/t/named-query-problem/22468/12 "2024-01-05T19:41:52Z")
Someone who finds this might find the following useful: > [@Taking a Dataset and Inserting it to a SQL temp table](https://forum.inductiveautomation.com/t/taking-a-dataset-and-inserting-it-to-a-sql-temp-table/83852/4): > > If you were using a competent database (the following is PostgreSQL), you can pass java arrays as arguments, and unnest() them in a FROM clause. For an example, I created a table in my psql forum playground called phrases, to be used in a join. When queried by itself, you get this dataset: "#NAMES" "prefix","suffix" "#TYPES" "str","I" "#ROWS","4" "Hello","1" "Good Bye","2" "Welcome to","3" "Whoa!","4" To go with that, in a custom property named lookups, I created the following unbound datase…