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.
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.
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.
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.
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" )
SQL SECURITY INVOKER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS
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 ) ;
Change the input parameter value to ‘OR 1 = 1’ and click “Run” again.
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.