Provide FROM table name to query as a parameter

Primary tabs

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?
 

Answers

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()
  // ...
}

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.