Skip to Content
Previous

Using APPLY FILTER statement

By Craig Cmehil

Leveraging SQLScript in Stored Procedures & User Defined Functions

You will learn

In contrast to EXEC and EXECUTE IMMEDIATE, APPLY_FILTER is SQL injection save. Furthermore the result of the APPLY_FILTER will be assigned to a table variable. This allows further processing of the result by referring to the table variable. As APPLY_FILTER does not allow executing dynamic DML/DDL statements the procedure can be flagged read only which allows further optimization.

Details

Please note - This tutorial is based on SPS11


Step 1: Add output parameter

Return to the procedure editor. Add an output parameter called ex_user_filtered_products and reference the dev602.data::MD.Products table as the type.

procedure editor
Please log in to access this content.
Step 2: Edit the procedure

Now that we are not using the dynamic SQL keywords, we no longer need a read/write procedure, so add the READS SQL DATA before AS.

READS SQL DATA

Remove the EXECUTE IMMEDIATE statement and instead insert the following SELECT statement and APPLY_FILTER statement using table variable assignments. The APPLY_FILTER needs two input parameters: table variable which will used for filtering and a scalar variable which contains the string.

modify statement
Please log in to access this content.
Step 3: Check complete code

The completed code should be very similar to this. If you do not wish to type this code, you can reference the solution web page at http://<hostname>:51013/workshop/admin/ui/exerciseMaster/?workshop=dev602&sub=ex2_18

PROCEDURE "dev602.procedures::get_product_by_filter" (
      IN im_product_filter_string varchar(5000),
      OUT ex_user_filtered_products "dev602.data::MD.Products" )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA  AS
BEGIN

pre_filtered_products =
     SELECT * FROM "dev602.data::MD.Products" WHERE CATEGORY NOT IN ('Laser printers');

ex_user_filtered_products = APPLY_FILTER(:pre_filtered_products, :im_product_filter_string ) ;

END
Please log in to access this content.
Step 4: Save and build
  1. Click Save.
save

Use what you have learned already and perform a build on your hdb module. Then return to the HRTT page and invoke the procedure again.

HRTT
Please log in to access this content.
Step 5: Run and check results

Click Format Code.

format code

Enter the filter string for the input parameter as

'CATEGORY = ''Notebooks'''

and click Run.

filter string

Once again, the results are displayed, but this time they are passed through a parameter which you are able to access for further processing.

results
Please log in to access this content.
Step 6: Test different input

Change the input parameter value to ‘OR 1 = 1’ and click “Run” again.

input parameter

You will notice you now get an error message when passing ‘OR 1 = 1’ to the procedure. This happens as the provided string will no longer be concatenated to the predefined filter conditions. Instead the provided string will be treated as a stand-alone filter condition, in this case having an invalid syntax.

syntax
Please log in to access this content.

Next Steps

Updated 03/24/2017

Time to Complete

15 Min.

Intermediate

Prerequisites

Next
Back to top