# SQL Query Where in list

**URL:** <https://forum.inductiveautomation.com/t/sql-query-where-in-list/42771>\
**Category:** Ignition\
**Tags:** ignition80, database\
**Created:** [January 29, 2021, 9:08pm UTC](https://forum.inductiveautomation.com/t/sql-query-where-in-list/42771 "2021-01-29T21:08:39Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [January 29, 2021, 9:08pm UTC](https://forum.inductiveautomation.com/t/sql-query-where-in-list/42771/1 "2021-01-29T21:08:39Z")

</div>

OK my brain is hurting and I can’t seem to figure this out.

I’ve got a list of strings and I’m trying to do a query using the WHERE IN operator. This is in MySQL.

Basically

```auto
list = [item1,item2,item3,item4]
sqlQuery = 'update table set valid = true where item in (?)'
data = system.db.runPrepQuery(query=sqlQuery,args=list)

```

But I’m not getting any data back. If I directly query the database I get data, but when I do it through Ignition nothing.

Any ideas?

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [January 29, 2021, 9:19pm UTC](https://forum.inductiveautomation.com/t/sql-query-where-in-list/42771/2 "2021-01-29T21:19:39Z")

</div>

I believe you have to have a ? for each parameter.

So assuming that the list size is static

```auto
sqlQuery = 'update table set valid = true where item in (?,?,?,?)'

```

Also, I notice that you’re using an update query but not using system.db.runPrepUpdate()

If the list size is dynamic then you could build the query string dynamically.

```auto
system.db.runPrepUpdate(query = 'update table set valid = true where item in (%s)' % (", ".join(['?'] * len(list))), args = list)

```

---

<div class="post-metadata">

**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 29, 2021, 9:28pm UTC](https://forum.inductiveautomation.com/t/sql-query-where-in-list/42771/3 "2021-01-29T21:28:29Z")

</div>

> [@bschroeder](#):
>
> list = [

BTW, don't use python built-in function names as variable names.

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [January 29, 2021, 9:39pm UTC](https://forum.inductiveautomation.com/t/sql-query-where-in-list/42771/4 "2021-01-29T21:39:46Z")

</div>

It’s not… It’s just a quick example. I had to sanitize for the customer.

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [January 29, 2021, 9:40pm UTC](https://forum.inductiveautomation.com/t/sql-query-where-in-list/42771/5 "2021-01-29T21:40:11Z")

</div>

That’s what I thought.

Thanks. I’ll test when I get back to the office.

---

<div class="post-metadata">

**Author:** ![ToMakPo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tomakpo/32/23875_2.png) [@ToMakPo](https://forum.inductiveautomation.com/u/ToMakPo)\
**Post date:** [November 28, 2023, 6:31pm UTC](https://forum.inductiveautomation.com/t/sql-query-where-in-list/42771/6 "2023-11-28T18:31:41Z")

</div>

While trying to do this, I was still getting errors so I just did this and it worked for me.

```auto
names = [n.replace("'", "\\'") for n in names]

system.db.runUpdataQuery("DELETE FROM [table name] WHERE name IN ('{}')".format(", ".join(names)))

```

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [November 28, 2023, 6:59pm UTC](https://forum.inductiveautomation.com/t/sql-query-where-in-list/42771/7 "2023-11-28T18:59:41Z")

</div>

When you're using `system.db.runUpdateQuery()` then the query string needs to be constructed as valid SQL, meaning that you can't use the `?` for parameter substitution.

You _should_ use the `system.db.runPrepUpdate()` to protect yourself from any SQL Injection risks, particularly if you are accepting user input as the parameters.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [November 29, 2023, 11:48am UTC](https://forum.inductiveautomation.com/t/sql-query-where-in-list/42771/8 "2023-11-29T11:48:29Z")

</div>

Try this instead:

```python
q = "delete from [table name] where name in ({})".format(','.join('?' * len(names)))
system.db.runPrepUpdate(q, names)

```

For the reasons @lrose mentioned, you shouldn't use `runUpdateQuery` for this.