Provide FROM table name to query as a parameter

Jorge De La Garza 9 January 2020

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

Answer by Chris Eslinger 9 January 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()
  // ...
}

0 0

Answer by Eduard Lebedyuk 9 January 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.

0 0

Provide FROM table name to query as a parameter

Primary tabs

Answers