Problem with a report datasource query

Ignition
1

I am trying to create a query that will pull in different data based on a Where clause manipulation. I want to have a parameter pull from a string tag that will be the column name of the table. The idea being I can dynamically change what column the query is limiting on. The problem is that I always get a null set. Please see the query below. The final ? in the query is a parameter that points at a string tag that has the value Sampler (Sampler being the name of a table column). When I change the query and just put in ‘Sampler’ directly and don’t use a parameter it works fine. I also am able to put in a parameter in for the value that the column needs to compare to and have that work as well. Can the query not substitute a table column through a parameter?

I find it interesting that I don’t get a query error but just a null set.

SELECT Result.StartDateSampled,Result.ResultFloat, Limit.OutFallID, OutFall.CustomerID, Result.ParamName, Limit.MaxOrMinLimit, Limit.SingleResultLimit, Limit.AvgResultLimit, Parameter.MajorGrp
FROM Result, Limit, OutFall, Parameter
WHERE Result.LimitID= Limit.LimitID and Result.ParamID= Parameter.ParamID and Limit.OutFallID=OutFall.OutFallID and OutFall.CustomerID = ? and Result.StartDateSampled > ? and Result.StartDateSampled < ? and ? = ‘City’

2

No SQL driver I know of allows parameter substitution to change the query structure. In fact, to avoid SQL-injection security breaches, you can’t allow it. You will have to perform regular string substitution on that column name prior to delivering it to system.db.runPrep*(). To be secure, your code will have to be designed to ensure only a real column name can be supplied there.

1 Like
3

Just as a note, you can change column names dynamically if you use a named query as your report data source. But like Phil said, you will be vulnerable to SQL injection if you do, so use at your own risk.