Question
Jorge De La Garza · Jan 9, 2020

Provide FROM table name to query as a parameter

I have an array of table names that I want to iterate over and for each table, get the number of rows.  I was hoping I could do something like:

set tStatement = ##class(%SQL.Statement).%New()
set tSC = tStatement.%Prepare("SELECT COUNT(*) AS ObservedRowCount FROM ?")
$$$ThrowOnError(tSC)
set tTableName = ""
for
{
  set tTableName = $O(tCounterAry(tTableName))
  quit:tTableName=""
  set tResult = tStatement.%Execute(tTableName)
  // ...
}

But that doesn't work:

ERROR #5540: SQLCODE: -1 Message:  IDENTIFIER expected, ? found^ SELECT COUNT ( * ) AS ObservedRowCount FROM ?

For reasons that I hope are obvious I would prefer not to do this with something like:

set tResult = ##class(%SQL.Statement).%ExecDirect(,"SELECT COUNT(*) AS ObservedRowCount FROM "_tTableName)

(And if it's not obvious: this makes me vulnerable to a SQL injection attack.  See also: https://xkcd.com/327/)

Is there a way that I can safely, dynamically query different tables with the same query?
 

#ObjectScript #SQL #InterSystems IRIS
00
2 0 2 206

Replies

Chris Eslinger · Jan 9, 2020

You could create the complete SQL string prior to the prepare statement.  There are similiar examples in the documentation here as well: https://docs.intersystems.com/csp/documatic/%25CSP.Documatic.cls?CLASSNAME=%25SQL.Statement
 

set tStatement = ##class(%SQL.Statement).%New()
set tTableName = "" 
for {  
  set tTableName = $O(tCounterAry(tTableName))  
  quit:tTableName="" 
  Set tSqlString = "SELECT COUNT(*) AS ObservedRowCount FROM "_tTableName     set tSC = tStatement.%Prepare(.tSqlString)  
  $$$ThrowOnError(tSC)
  set tResult = tStatement.%Execute()
  // ...
}
00
Eduard Lebedyuk · Jan 9, 2020

While the table name cannot be a query argument, you can sanitize the input using:

Write $SYSTEM.SQL.TableExists("Sample.Person")

And as far as sanitizing the table access itself - SQL access privileges should take care of that.

10