Jump to content


Photo
- - - - -

Example of using dynamic SQL


2 replies to this topic

#1 DARREN

DARREN

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 395 posts
  • Gender:Male
  • Location:Florida,USA

Posted 20 February 2014 - 09:19 PM

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

  • CorporateDiaryByFilter.png
  • CorporateDiaryFilterMaintenance.png
  • PolicyInquiryBillingTab.png

Things should be made as simple as possible, but not simpler

#2 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 25 February 2014 - 02:32 PM

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.


Nothing's as simple as you think

#3 DARREN

DARREN

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 395 posts
  • Gender:Male
  • Location:Florida,USA

Posted 25 February 2014 - 10:40 PM

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. (w00t)


Things should be made as simple as possible, but not simpler



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users