Written by

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?

Discussion (2)2

Add reply

Comments

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

0 0

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.

0 0