DB2 Using Dynamic SQL

Jim Youmans
Jim Youmans used Ask the Experts™
on
DB2 10.5 on Windows Server 2012

I need to write a stored proc to search a customer database on multiple, optional parameters.  Lets say firstname, lastname, cust_num.  So here is what I have..

CREATE PROCEDURE DBAUtil.Jim_Search(
     IN p_first_name VARCHAR(50),
     IN p_last_name VARCHAR (50),
     IN p_cust_num INTEGER)
SPECIFIC  DBAUtil.Jim_Search
LANGUAGE SQL RESULT SETS 1

BEGIN

DECLARE v_dynamicSql varchar(5000);
DECLARE c_search CURSOR WITH RETURN FOR v_dynamicSql;

SET v_dynamicSql = 
'SELECT * FROM DBAUtil.CUSTOMER WHERE 1=1 ';

IF p_first_name IS NOT NULL THEN
    SET v_dynamicSql = v_dynamicSql || ' AND UPPER(FIRST_NAME) = UPPER(' || p_first_name || ')';
END IF ;   
     
 IF p_last_name IS NOT NULL THEN
     SET v_dynamicSql = v_dynamicSql || ' AND UPPER(LAST_NAME) = UPPER(' || p_last_name || ')'  ;  
END IF;

IF p_cust_num IS NOT NULL THEN
     SET v_dynamicSql = v_dynamicSql || ' AND CUST_NUM = ' || cust_num || ' ';
END IF;

SET v_dynamicSql = v_dynamicSql || ' ORDER BY LAST_NAME, FIRST_NAME';
 

PREPARE v_dynamicSql FROM v_dynamicSql;
open c_search;

END;

Open in new window

It compiles but when I run it like so
CALL DBAUtil.Jim_Search (NULL,'YOUMANS',123)

Open in new window

I get this
Category	Timestamp	Duration	Message	Line	Position
Error	12/27/2019 10:53:15 AM	0:00:00.020	<link> - DB2 Database Error: ERROR [42703] [IBM][DB2/NT64] SQL0206N  "YOUMANS" is not valid in the context where it is used.	11	0

Open in new window


So I know my sql string is getting messed up but not sure how.  Is there a way to print the v_dynamicSql string that it is trying to run?  I have tried a couple of ways but no luck.

Is there a better way to do this?

Thanks!!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sr Database Administrator
Commented:
I figured it out.  I was not doing my quotes correctly.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
You do realize that by using string concatenation you are opening yourself up to SQL injection.

With the right combination of parameters I can return your entire table.

You should be using bind variables to prevent this.  I'm not a DB2 Expert so cannot provide an exact example but there are several on the web.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial