Using Ensemble SQL Operations for Selects, Updates, Inserts, and Stored Procedure calls
Hello,
We are sometimes asked for examples of how to do 3rd party SQL database selects/updates/inserts/stored procedures from an Ensemble SQL Business Operation.
In the attached file there are 4 sample Ensemble Operations that demonstrate four different types of transactions with an external SQL DB:
1: The "CheckExists" shows how to use an Operation to do a select statement to see if a given patient MRN exists in a DB
2: The ExecSP Operation shows how to execute a stored procedure from a BO
3: the Update Operation does an Update
4: the Insert Operation does an Insert
Given the samples, one can combine updates/inserts/SPs/selects into a single operation as needed.
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.
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:
One more example: How to call a stored procedure passing a Cache stream as a SQL CLOB:
QUALITY.STREAMTABLE just has one column of type CLOB.
Here is the Ensemble Operation code: