Using Ensemble SQL Operations for Selects, Updates, Inserts, and Stored Procedure calls

This is an excellent overview with easy to understand examples.

Walking through it I foundthat the "WriteUpdateToTargetDB" method is concatenating the SQL-String which then gets executed against the external database. 

Even if this is just an example it would allow a possible attack of a 2nd order sql injection - if the attacker has knowledge of the system. The easiest way to be more secure is to use placeholders in the sql statement and parameters when calling the external database.
Another advantage of using placeholders is that there is no need for quotation marks.
 

I just wanted to add another example to this.  This example shows how to call an oracle stored procedure that uses a SYS_REFCURSOR as an output parameter.

  CREATE OR REPLACE PROCEDURE "QUALITY"."BRENDANGETCURSORS" (

 "ONE" IN DECIMAL,

 "TWO" OUT SYS_REFCURSOR) IS

 BEGIN

 OPEN TWO FOR SELECT * FROM QUALITY.BRENDAN WHERE NUM=ONE;

 END "BRENDANGETCURSORS";

And the table Quality.Brendan just has a DECIMAL column named NUM and a VARCHAR2 column named STRING

Here is the code I used in my Ensemble SQL operation:

Method OnMessage(pRequest As Ens.Request, Output pResponse As Ens.Response) As %Status
{
                set tSC = $$$OK
               
                set parms=2
                set parms(1,"IOType")=$$$SQLPARAMINPUT
                set parms(1,"SqlType")=$$$SqlDecimal
                set parms(1,"Prec")=2
                set parms(1)=1
               
                set parms(2,"IOType")=$$$SQLPARAMOUTPUT
                set parms(2,"SqlType")=$$$SqlWLongVarchar
                set parms(2,"LOB")=0
               
               
                set sql="{CALL QUALITY.BRENDANGETCURSORS(?,?)}"
                set rs = ##class(%ListOfObjects).%New()
                set snap = ##class(EnsLib.SQL.Snapshot).%New()
                set snap.MaxRowsToGet=10
                set tSC = rs.Insert(snap)
                quit:$$$ISERR(tSC) tSC
               
                set tSC=..Adapter.ExecuteProcedureParmArray(.rs,.out,sql,"io",.parms)
                quit:$$$ISERR(tSC) tSC
               
                set snap = rs.GetAt(1)
               
                $$$TRACE("Row Count: "_snap.RowCount)
                $$$TRACE("Column Count: "_snap.ColCount)
               
                $$$TRACE("Object? "_$IsObject(snap))
                while ('snap.AtEnd) {
                                $$$TRACE("Num: "_snap.Get("Num"))
                                $$$TRACE("String: "_snap.Get("String"))
                                do snap.%Next(.tSC)
                                quit:$$$ISERR(tSC)
                }

                quit tSC
}

One more example: How to call a stored procedure passing a Cache stream as a SQL CLOB:

PROCEDURE PUTSTREAM(STREAMIN IN CLOB) IS

BEGIN

               INSERT INTO QUALITY.STREAMTABLE VALUES(STREAMIN);

END PUTSTREAM;

QUALITY.STREAMTABLE just has one column of type CLOB.

Here is the Ensemble Operation code:

Method SendStream(pRequest As Ens.StreamContainer, Output pResponse As Ens.Response) As %Status
{
               set TestParams=1

               set TestParams(1) = pRequest.Stream
               set TestParams(1,"SqlType")=$$$SqlLongVarchar //EnsSQLTypes.inc
               set TestParams(1,"LOB")=1

               set QueryStatement = "{ call QUALITY.PUTSTREAM(?) }"

               set tSC = ..Adapter.ExecuteProcedureParmArray(.ResultSnapshots, .OutputParams, QueryStatement, , .TestParams)

               Quit tSC
}