prepQuery with WHERE IN() statement

Ignition
,
1

Hi,

I would like to execute query below but it return no rows :

usages = [301,311]
pyds = system.db.runPrepQuery("SELECT * FROM distribution_config WHERE usage_id IN (?)",[usages],"base_administrateur")

Do you have a solution without make a loop in my list ?

2

Which database / SQL version is this? Also, if you put 301 or 311 in the WHERE clause separately, does it return any rows?

This should work in MySQL:

usages = [301,311]    # see below. Use tuple instead of list
pyds = system.db.runPrepQuery("SELECT * FROM distribution_config WHERE usage_id IN ?",[str(usages)],"base_administrateur")

EDIT:
I forgot that MySQL doesn’t like square brackets… What you could do is make usages a tuple (ie. usages = (301, 311)) and then do str(usages) like I said above. However, you need to be careful with
the tuple data structure. For instance, a tuple is immutable, unlike a list.

The only potential limitation of @zacht’s approach is that if the usages list is long, your query will place a bunch of placeholders into the query and then fill them with the args list. I don’t know what the performance of that approach is vs casting a tuple to a string and inserting it into query once.

3 
usages = [301,311]
queryString = """SELECT *
				FROM distribution_config
				WHERE usage_id IN ({})""".format(','.join(['?']*len(usages)))
pyds = system.db.runPrepQuery(queryString,usages,"base_administrateur")
4 Likes
4

It’s MySQL.
Your script not works thewebpleb. It returns a Syntax error.

5

Thanks zacht it works !

2 Likes
6

I could be wrong (and I'm sure someone will correct me if I am), but I don't think a single ? will ever work with multiple values. My understanding is that runPrepQuery will pass one literal value for each question mark. In the example above that would mean the query would only return rows where the value of usage_id is the string "[301,311]" for a list or "(301,311)" if you use a tuple (quotes not included in the value). Since usage_id is an integer (of some flavor) column, this will never be true.

Casting a tuple to a string would work if you used a 'queryString' approach, but that would open the query up to SQL injection, exactly what we want to avoid with runPrepQuery.

I can't speak authoritatively to the performance implications, but if I had to guess the two solutions are probably comparable. I doubt that dynamically building the string takes much more effort than typecasting a list/tuple, even with many arguments.

7

I agree that the approach I laid out above isn’t the best approach. However, it is valid SQL (for MySQL v8) because when you say

usages = (1, 2, 3, 4, 5)
query = "SELECT * FROM distribution_config WHERE usage_id IN {}".format(str(usages))

the actual query that gets executed is

SELECT * FROM distribution_config WHERE usage_id IN (1, 2, 3, 4, 5)

NOT

SELECT * FROM distribution_config WHERE usage_id IN "(1, 2, 3, 4, 5)"

or

SELECT * FROM distribution_config WHERE usage_id IN ("1", "2", "3", "4", "5")

@zacht Thank you for the feedback. I like your approach and agree with your SQL injection point. :+1:

1 Like
8

This is correct. Or rather, correct for Ignition's generic connection environments. JDBC drivers for the popular brands extend the JDBC standard by defining methods to construct arbitrary data types, like PostgreSQL arrays. That could pass a list in a single parameter. But Ignition's pooling connection system doesn't give you access to branded extensions, and I can imagine the nightmare to even try.

2 Likes
9

Note that at least one JDBC driver tolerates a form of this through Ignition's "Prep" queries:

10

Follow up: