2 replies to this topic

[DARREN]

We rolled out a new project today and I thought it may be enlightening to share with you one of the features we used, namely the SQL DYNAMIC feature,

 

We have a corporate diary that we are using more and more as a workflow engine. One of the requirements of the project was to allow end users to create 'filters' that allowed them to define the selection criteria they wished to employ. It was a good example of the use of the dynamic SQL feature within PROIV. We basically build a SQL statement on the fly from the parameters that are defined by the end user. Simple but effective. To get some additional performance we also introduced a view to join the various Oracle tables that were being referenced. This was purely to increase the speed of the display to the grid. I have attached some screen shots.

 

 

Attached Thumbnails

• 
• 
• 

[Richard Bassett]

Obligatory Little Bobby Tables reference:

 

https://xkcd.com/327/

 

The serious point to remember is that using dynamic SQL enables SQL injection attacks, so you should always put in place appropriate sanitization of the variable text you are splicing into your SQL, otherwise bad things can happen to good people.

[DARREN]

Agreed, but the values that can be selected for both the variables (which are given meaningful names for the purpose of display) and the selection values are table driven and are validated. The only way that SQL injection could occur is if the DBA did it himself.