Issue with using vision client tags for filtering a table on vision

Ignition
1

Hello, I've been running into an issue when trying to set up a filter by field to filter items on a table, using vision client tags for the influencing values for said filter.

For some reason these vision tags are interacting weirdly with my SQL query I have linked to my table that is being filtered.
SQL query on table.

SELECT itemName,partNum,zoneFrom,zoneTo,TimeOfMove FROM itemmoverec
WHERE 
	partNum = {[client]filterTagsForItemMoveRecord/filterByTag.value} 
	AND 
	{[client]filterTagsForItemMoveRecord/filterOnOffTag} = true
	OR
	{[client]filterTagsForItemMoveRecord/filterOnOffTag} = false

Whenever the table produces an error, it shows the filterOnOfTag.value as either 1 or 0 dependent on the current state of the tag itself, to combat this I removed the .value from the query and it still produced the same error message. It makes sense that this would throw an error, but I am wondering why the change is occurring at all.
Error Message.

Exception: Error running query:
SQLQuery(query=SELECT itemName,partNum,zoneFrom,zoneTo,TimeOfMove FROM itemmoverec
WHERE 
	partNum = hello 
	AND 
	0 = 1
	OR
	0 = 0
, database=RFIDINV)@0ms
On: itemMovementRecordScreen.Root Container.Power Table.data
	caused by GatewayException: Unknown column 'hello' in 'where clause'
	caused by SQLSyntaxErrorException: Unknown column 'hello' in 'where clause'

Thanks,
Dalton

2

Vision SQL queries use an (insecure, non-ideal) substitution method - tag values are dropped in as literal strings before the query is passed on to the rest of the system. So if your client tag's value is hello, your query is literally something like:

SELECT itemName,partNum,zoneFrom,zoneTo,TimeOfMove FROM itemmoverec
WHERE 
	partNum = hello 
	AND 
	0 = 1
	OR
	0 = 0

And as the error message is telling you, that's not valid SQL syntax. You're trying to compare a column partNum against a literal string, but what you're actually doing is comparing partNum against "an arbitrary SQL keyword hello".

TL;DR: Wrap your filter input in single quotes.

SELECT itemName,partNum,zoneFrom,zoneTo,TimeOfMove FROM itemmoverec
WHERE 
	partNum = '{[client]filterTagsForItemMoveRecord/filterByTag.value}'
	AND 
	{[client]filterTagsForItemMoveRecord/filterOnOffTag} = true
	OR
	{[client]filterTagsForItemMoveRecord/filterOnOffTag} = false
3

To expand on this: The preferred migration path is named queries, which close the security hole (SQL injection, in particular from insecure clients) opened for straight DB queries in Vision. That's why you have to opt in to "legacy database access" in the project permissions.

2 Likes
4

Would a way to close this security loophole be to pass the filter parameters into a named query and link the table to said named query?

5

How to run Script faster - Ignition - Inductive Automation Forum

See here for a named query with filtering capabilities. Not the most ergonomic thing in the world, but it works well.

1 Like
6

Thanks @PGriffith for the help I appreciate it, I will look into setting up a named query to handle the filter as well.

7

What I've done in the past for an optional filter construct like this is a statement in the where clause that assumes that null is the "no filter applied" state and only if the filter parameter is not null does it actually run the LIKE or whatever other clause.

E.G. in named queries, where :param is the syntax for parameter substitution (and you don't want to wrap in single quotes):

SELECT itemName,partNum,zoneFrom,zoneTo,TimeOfMove FROM itemmoverec
WHERE 
	:partNum IS NULL OR partNum = :partNum

EDIT: Exactly as in @dkhayes117's link above :slight_smile:

1 Like
8

Thanks, I did not think of that. That should make things simpler and more efficient.

I appreciate the help.