1 2 Previous Next 21 Replies Latest reply on Dec 15, 2015 12:44 PM by Jarkko Turpeinen

    Dynamic SQL and avoiding SQL Injection

    DevGuy

      Looking for some advice on how to make PL/SQL and SQL code SQL Injection proof.

       

      I have some procs where 10 screen input fields are passed in to the proc as parameters and about 5 of them are varchar2 data type.

      All screen fields are passed into the proc whether they are left NULL or are populated by the user.

       

      In the proc, we check which parameters are passed in as NOT NULL and then concatenate strings of them to make a WHERE clause to include all of the NOT NULL parameter values that are passed in.

      So we could have only 1 or 2 conditions in the final WHERE clause or up 10 conditions in a large query.

       

      I have seen advice to use bind variables to avoid SQL injection threats, but how does one maintain the dynamic characteristic where we don't know the number of sub clauses in the final WHERE clause until after parameters are passed into the proc?

       

      Any help would be much appreciated.

        • 1. Re: Dynamic SQL and avoiding SQL Injection
          Karthick2003

          > In the proc, we check which parameters are passed in as NOT NULL and then concatenate strings of them to make a WHERE clause to include all of the NOT NULL parameter values that are passed in.

           

          I smell trouble. Especially SQL Injection as already advised to you.

           

          Please show us your code. The bind variable suggestion is the right thing. But first we need to see your Procedure to comment further.

          • 2. Re: Dynamic SQL and avoiding SQL Injection
            Billy~Verreynne

            The problem description of yours lacks a lot of detail on what the nature of the screen input system is (Apex, PHP, Java, etc), and how the dynamically created cursor is used.

             

            Basic approach however would be:

            - Build dynamic SQL using DBMS_SQL

            - Perform dynamic binding

            - Convert DBMS_SQL cursor address/number into a ref cursor

            - Pass ref cursor to caller for consumption

            • 3. Re: Dynamic SQL and avoiding SQL Injection
              Nimish Garg

              I mostly prefer to use EXECUTE IMMEDIATE with bind variables, and it has handled my all needs for sql injection proof dynamic sql.

              If you want to use DBMS_SQL, look at asktom for how to use it with bind variables

               

              https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:455220177497

              • 5. Re: Dynamic SQL and avoiding SQL Injection
                Sven W.

                DevGuy wrote:

                 

                Looking for some advice on how to make PL/SQL and SQL code SQL Injection proof.

                 

                I have some procs where 10 screen input fields are passed in to the proc as parameters and about 5 of them are varchar2 data type.

                All screen fields are passed into the proc whether they are left NULL or are populated by the user.

                 

                In the proc, we check which parameters are passed in as NOT NULL and then concatenate strings of them to make a WHERE clause to include all of the NOT NULL parameter values that are passed in.

                So we could have only 1 or 2 conditions in the final WHERE clause or up 10 conditions in a large query.

                 

                I have seen advice to use bind variables to avoid SQL injection threats, but how does one maintain the dynamic characteristic where we don't know the number of sub clauses in the final WHERE clause until after parameters are passed into the proc?

                 

                Any help would be much appreciated.

                If I understand right, then you have issues because the number of parameters in your dynamic query changes.

                In your procedure you should know how many values are binded to the dynamic query. Depending on that number you can run a different execute_immediate code block.

                 

                here is a crude example how to do it. (not syntax checked)

                 

                procedure doSQL (p1 in number default null, p2 in number default null)
                is
                  type params_t is table of number;
                
                  v_sql varchar2(4000);
                  v_no_of_binds binary_integer := 0;
                  v_bind_params params_t ; 
                begin
                  v_sql := 'select * from emp';
                  if p1 is not null then
                        v_no_of_binds := v_no_of_binds +1;
                        if  v_no_of_binds = 1 then 
                          v_sql := v_sql || ' where ';
                        else
                          v_sql := v_sql || ' and ';
                      end if;
                
                       v_sql := v_sql || ' deptno = :'||to_char(v_no_of_binds);
                        v_bind_params(v_no_of_binds) := p1;
                  end if;
                
                  if p2 is not null then
                        v_no_of_binds := v_no_of_binds +1;
                        if  v_no_of_binds = 1 then 
                          v_sql := v_sql || ' where ';
                        else
                          v_sql := v_sql || ' and ';
                      end if;
                
                       v_sql := v_sql || ' empno = :'||to_char(v_no_of_binds);
                        v_bind_params(v_no_of_binds) := p2;
                  end if;
                
                  --- do the statement
                  if v_no_of_binds = 0 then
                    execute immediate v_sql;
                  elsif v_no_of_binds = 1 then
                    execute immediate v_sql using  v_bind_params(1);
                  elsif v_no_of_binds = 2 then
                    execute immediate v_sql using  v_bind_params(1), v_bind_params(2);
                  end if;
                end doSQL;
                /
                
                
                
                1 person found this helpful
                • 6. Re: Re: Dynamic SQL and avoiding SQL Injection
                  Mike Kutz

                  That works for a low number of bind parameters.

                  The use of EXECUTE IMMEDIATE starts getting complicated the more combination of binds you need.

                   

                  The first recommendation is to not do dynamic SQL if you can avoid it.

                  It sounds like this may not be possible for this situation.

                   

                  The other option would be to follow Billy's suggestion and use DBMS_SQL.

                   

                  The following code may have syntax errors and is untested.  But the technique is solid.

                   

                  MK

                   

                  procedure doSQL( p_ref out SYS_REFCURSOR
                        , param1_match in varchar2, param1_value1 in date, param1_value2 in date
                        , ... -- repeat for each parameter
                     )
                  as
                  c pls_integer;
                  l_sql clob;
                  l_ret int;
                  begin
                  l_sql := q'[SELECT *
                      FROM T
                    WHERE 1=1
                  ]';
                  
                    -- dynamically build the WHERE clause
                  -- some of this could be put into a different procedure.
                    if upper( param1_match ) = 'BETWEEN' then
                    l_sql := l_sql || ' AND PARAM1 BETWEEN :p1_val1 and :p2_val2 ' || chr(10);
                  elsif param1_match in ( '<', '<=', '=', >=', '>') then
                    l_sql := l_sql || ' AND PARAM1 ' || param1_match || ' :p1_val1 ' || chr(10);
                  elsif param1_match is null then
                      null; -- parameter not used
                  else
                    raise BAD_INPUT
                  end if;
                  
                  -- debug - output actual SQL here
                  
                    c := dbms_sql.open_cursor();
                    dbms_sql.parse(c, l_sql, dbms_sql.native);
                  
                    -- now, we dynamically BIND the variables.
                  if upper(param1_match)='BETWEEN' then
                    dbms_sql.bind(c, ':p1_val1', param1_val1 );
                    dbms_sql.bind(c, ':p1_val2', param1_val2 );
                  elsif ...... -- you get the idea
                  endif;
                  
                    l_ret := dbms_sql.execute(c);
                  
                    p_ref := dbms_sql.to_refcursor( c );
                  
                  end;
                  
                  1 person found this helpful
                  • 7. Re: Dynamic SQL and avoiding SQL Injection
                    gaverill

                    With such a small maximum number of parameters you can simply bind them all, even if you don't use them...

                     

                    with    All_My_Parameters
                    as    (
                        select    cast(:param1 as date) param1
                        ,    cast(:param2 as number) param2
                        --    ...
                        from    DUAL
                        )
                    select    ...
                    from    All_My_Parameters p
                    ,    <rest-of-query-tables>
                    where    <dynamic-conditions-involving-p>
                    

                     

                    HTH.

                    Gerard

                    • 8. Re: Dynamic SQL and avoiding SQL Injection
                      Sven W.

                      Mike Kutz wrote:

                       

                      That works for a low number of bind parameters.

                      The use of EXECUTE IMMEDIATE starts getting complicated the more combination of binds you need.

                       

                      A non-dynamic select would scale in a exponential way, because for each parameter the number of permutations to consider raises considerably.

                      The code template I provided scales in a linear way. That means the same number of lines of code is added for each parameter. Parameters with different datatype would also be possible, but it might require some more coding effort.

                       

                      Having said that, I think it depends a lot from the real task and the sql that needs to be created. DBMS_SQL is certainly a solid way to do it, but that also has its own complexities. As long as the output structure is static, I would prefer execute immediate instead of dbms_sql.

                      • 9. Re: Dynamic SQL and avoiding SQL Injection
                        Jarkko Turpeinen

                        Hi,

                         

                        how to make PL/SQL and SQL code SQL Injection proof.

                        well by not doing it dynamic at all is the most secure and maintainable i think

                         

                        -- SQL bind variables
                        select * from scott.emp
                        where (:empno     is null or to_number(:empno) = empno)
                          and (:ename     is null or :ename = ename)
                          and (:job       is null or :job = job)
                          and (:mgr       is null or to_number(:mgr) = mgr)
                          and (:hiredate  is null or to_date(:hiredate, 'DD-MON-YYYY', 'nls_date_language=english') = hiredate)
                          and (:sal       is null or to_number(:sal) = sal)
                          and (:comm      is null or to_number(:comm) = comm)  
                          and (:deptno    is null or to_number(:deptno) = deptno)
                        ;
                        
                        
                        -- PL/SQL bind variables
                        set serveroutput on size unlimited
                        declare
                          p_emp scott.emp%rowtype;
                        begin
                          p_emp.empno :=  null;
                          p_emp.ename := null;
                          p_emp.job :=  'CLERK';
                          p_emp.mgr :=  null;
                          p_emp.hiredate :=  null;
                          p_emp.sal :=  null;
                          p_emp.comm :=  null;
                          p_emp.deptno :=  null;
                          --
                          for r in (
                            select * from scott.emp
                            where (p_emp.empno     is null or p_emp.empno = empno)
                              and (p_emp.ename     is null or p_emp.ename = ename)
                              and (p_emp.job       is null or p_emp.job = job)
                              and (p_emp.mgr       is null or p_emp.mgr = mgr)
                              and (p_emp.hiredate  is null or p_emp.hiredate = hiredate)
                              and (p_emp.sal       is null or p_emp.sal = sal)
                              and (p_emp.comm      is null or p_emp.comm = comm)  
                              and (p_emp.deptno    is null or p_emp.deptno = deptno)
                          )
                          loop
                            dbms_output.put_line(lpad('-', 10, '-'));    
                            dbms_output.put('empno: ');
                            dbms_output.put_line(r.empno);
                            dbms_output.put('ename: ');
                            dbms_output.put_line(r.ename);
                            dbms_output.put('job: ');
                            dbms_output.put_line(r.job);
                            dbms_output.put('mgr: ');
                            dbms_output.put_line(r.mgr);
                            dbms_output.put('hiredate: ');
                            dbms_output.put_line(r.hiredate);
                            dbms_output.put('sal: ');
                            dbms_output.put_line(r.sal);
                            dbms_output.put('comm: ');
                            dbms_output.put_line(r.comm);
                            dbms_output.put('deptno: ');
                            dbms_output.put_line(r.deptno);
                          end loop;
                        end;
                        /
                        
                        
                        
                        
                        PL/SQL procedure successfully completed.
                        
                        
                        ----------
                        empno: 7369
                        ename: SMITH
                        job: CLERK
                        mgr: 7902
                        hiredate: 17.12.1980
                        sal: 800
                        comm: 
                        deptno: 20
                        ----------
                        empno: 7876
                        ename: ADAMS
                        job: CLERK
                        mgr: 7788
                        hiredate: 23.05.1987
                        sal: 1100
                        comm: 
                        deptno: 20
                        ----------
                        empno: 7900
                        ename: JAMES
                        job: CLERK
                        mgr: 7698
                        hiredate: 03.12.1981
                        sal: 950
                        comm: 
                        deptno: 30
                        ----------
                        empno: 7934
                        ename: MILLER
                        job: CLERK
                        mgr: 7782
                        hiredate: 23.01.1982
                        sal: 1300
                        comm: 
                        deptno: 10
                        
                        
                        
                        • 10. Re: Re: Dynamic SQL and avoiding SQL Injection
                          Sven W.

                          gaverill wrote:

                           

                          With such a small maximum number of parameters you can simply bind them all, even if you don't use them...

                          ...

                          HTH.

                          Gerard

                          Op mentioned 10 parameters. I'm not sure if you correctly understood the problem that arises from added 10 conditions. In general this will lead to a sub optimal execution plan.

                           

                          example:

                           

                          select *
                          from emp
                          where deptno = nvl(:p1, deptno)
                          and    empno = nvl(:p2, empno)
                          ;
                          

                           

                          the optimal execution plan would be (it depends from some other factors too, like size of the table, but lets ignore that for a moment).

                          A) a full table scan if both parameters are NULL

                          B) an index access via index range scan on deptno  if p2 is NULL

                          C) an index access via index unique scan on empno  if p1 is NULL or if both parameters have some value

                           

                          The problem now is that the index access might not be not possible (because of the NVL function accessing the column again).

                          However if you have different sql statements for each parameter conbination, then the optimizer has a much better chance to find an optimal plan.

                          This is where dynamic SQL (or different direct SQL statements) can offer some help.

                           

                          A) select * from emp;

                          B) select * from emp where deptno = :p1;

                          C) select * from emp where empno = 2;

                          • 11. Re: Dynamic SQL and avoiding SQL Injection
                            gaverill

                            I did and do understand the problem (with dynamically added conditions), but was only addressing how to simplify one aspect of the problem (determining the number of binds that will go with them).

                             

                            Gerard

                            • 12. Re: Dynamic SQL and avoiding SQL Injection
                              Jarkko Turpeinen

                              Here's my kind of dynamic SQL if i must use one.

                               

                              I try first without dynamic SQL with all combinations. If there is any performance issues, i'll try to add (kindly ask) mandatory and narrowest base filters as possible. If that does not fix the performance issue, i use dynamic SQL and start breaking SQL join by join to see what combination performs as required and serves requirements. Still keeping the whole SQL in one variable.

                               

                              So i will not write if then else this and that and build SQL from smallest piece possible because at the end it's pure hell to maintain. Hell = very hard to read, understand and easy to break without knowing the combination that breaks it.

                               

                              declare
                                l_sql clob default q'{
                                -- whole SQL and designed few options commented out so it will remain readable and understandable (without running and debugging)
                                select emp.* from scott.emp
                                --@DEPT join scott.dept on emp.deptno = dept.deptno
                                where (:empno     is null or to_number(:empno) = emp.empno) 
                                  and (:ename     is null or :ename = emp.ename) 
                                  and (:job       is null or :job = job) 
                                  and (:mgr       is null or to_number(:mgr) = emp.mgr) 
                                  and (:hiredate  is null or to_date(:hiredate, 'DD-MON-YYYY', 'nls_date_language=english') = hiredate) 
                                  and (:sal       is null or to_number(:sal) = emp.sal) 
                                  and (:comm      is null or to_number(:comm) = emp.comm)
                                  --@DEPT and dept.deptno = to_number(:deptno)
                                }';
                                type emp_table is table of scott.emp%rowtype index by pls_integer;
                                l_emp emp_table;
                                p_emp scott.emp%rowtype; 
                              begin
                                p_emp.empno :=  null; 
                                p_emp.ename := null; 
                                p_emp.job :=  'CLERK'; 
                                p_emp.mgr :=  null; 
                                p_emp.hiredate :=  null; 
                                p_emp.sal :=  null; 
                                p_emp.comm :=  null; 
                                p_emp.deptno := 20; 
                                case
                                  when p_emp.deptno is not null then
                                    l_sql := replace(l_sql, '--@DEPT ');
                                  dbms_output.put_line(l_sql);
                                  execute immediate l_sql
                                    bulk collect into l_emp
                                    using p_emp.empno, p_emp.empno, p_emp.ename, p_emp.ename, p_emp.job, p_emp.job, p_emp.mgr, p_emp.mgr, p_emp.hiredate, p_emp.hiredate, p_emp.sal, p_emp.sal, p_emp.comm, p_emp.comm,
                                    p_emp.deptno;
                                else
                                  dbms_output.put_line(l_sql);
                                  execute immediate l_sql
                                    bulk collect into l_emp
                                    using p_emp.empno, p_emp.empno, p_emp.ename, p_emp.ename, p_emp.job, p_emp.job, p_emp.mgr, p_emp.mgr, p_emp.hiredate, p_emp.hiredate, p_emp.sal, p_emp.sal, p_emp.comm, p_emp.comm;
                                end case;
                                for i in 1..l_emp.count
                                loop
                                  dbms_output.put_line(lpad('-', 10, '-'));     
                                  dbms_output.put('empno: '); 
                                  dbms_output.put_line(l_emp(i).empno); 
                                  dbms_output.put('ename: '); 
                                  dbms_output.put_line(l_emp(i).ename); 
                                  dbms_output.put('job: '); 
                                  dbms_output.put_line(l_emp(i).job); 
                                  dbms_output.put('mgr: '); 
                                  dbms_output.put_line(l_emp(i).mgr); 
                                  dbms_output.put('hiredate: '); 
                                  dbms_output.put_line(l_emp(i).hiredate); 
                                  dbms_output.put('sal: '); 
                                  dbms_output.put_line(l_emp(i).sal); 
                                  dbms_output.put('comm: '); 
                                  dbms_output.put_line(l_emp(i).comm); 
                                  dbms_output.put('deptno: '); 
                                  dbms_output.put_line(l_emp(i).deptno); 
                                end loop;
                              end;
                              /
                              
                              
                              -- search CLERKs
                              
                              
                              PL/SQL procedure successfully completed.
                              
                              
                                select emp.* from scott.emp
                                --@DEPT join scott.dept on emp.deptno = dept.deptno
                                where (:empno     is null or to_number(:empno) = emp.empno) 
                                  and (:ename     is null or :ename = emp.ename) 
                                  and (:job       is null or :job = job) 
                                  and (:mgr       is null or to_number(:mgr) = emp.mgr) 
                                  and (:hiredate  is null or to_date(:hiredate, 'DD-MON-YYYY', 'nls_date_language=english') = hiredate) 
                                  and (:sal       is null or to_number(:sal) = emp.sal) 
                                  and (:comm      is null or to_number(:comm) = emp.comm)
                                  --@DEPT and dept.deptno = to_number(:deptno)
                              
                              ----------
                              empno: 7369
                              ename: SMITH
                              job: CLERK
                              mgr: 7902
                              hiredate: 17.12.1980
                              sal: 800
                              comm:
                              deptno: 20
                              ----------
                              empno: 7876
                              ename: ADAMS
                              job: CLERK
                              mgr: 7788
                              hiredate: 23.05.1987
                              sal: 1100
                              comm:
                              deptno: 20
                              ----------
                              empno: 7900
                              ename: JAMES
                              job: CLERK
                              mgr: 7698
                              hiredate: 03.12.1981
                              sal: 950
                              comm:
                              deptno: 30
                              ----------
                              empno: 7934
                              ename: MILLER
                              job: CLERK
                              mgr: 7782
                              hiredate: 23.01.1982
                              sal: 1300
                              comm:
                              deptno: 10
                              
                              
                              -- search CLERKs from department 20
                              
                              
                              PL/SQL procedure successfully completed.
                              
                              
                                select emp.* from scott.emp
                                join scott.dept on emp.deptno = dept.deptno
                                where (:empno     is null or to_number(:empno) = emp.empno) 
                                  and (:ename     is null or :ename = emp.ename) 
                                  and (:job       is null or :job = job) 
                                  and (:mgr       is null or to_number(:mgr) = emp.mgr) 
                                  and (:hiredate  is null or to_date(:hiredate, 'DD-MON-YYYY', 'nls_date_language=english') = hiredate) 
                                  and (:sal       is null or to_number(:sal) = emp.sal) 
                                  and (:comm      is null or to_number(:comm) = emp.comm)
                                  and dept.deptno = to_number(:deptno)
                              
                              ----------
                              empno: 7369
                              ename: SMITH
                              job: CLERK
                              mgr: 7902
                              hiredate: 17.12.1980
                              sal: 800
                              comm:
                              deptno: 20
                              ----------
                              empno: 7876
                              ename: ADAMS
                              job: CLERK
                              mgr: 7788
                              hiredate: 23.05.1987
                              sal: 1100
                              comm:
                              deptno: 20
                              
                              
                              1 person found this helpful
                              • 13. Re: Dynamic SQL and avoiding SQL Injection
                                DevGuy

                                Thanks all for your comments. I really appreciate the feedback. Some added information below.

                                 

                                The proc returns a REF_CURSOR with the results of the query.

                                The front end is JSP with Java layer in between, but I am trying to solve this issue from the back end only.

                                From a operator perspective things are easier since we are using equals "=" for all conditions.

                                In the example above where each parameter is included in the where clause even if NULL, I'm pretty sure performance will be a big problem because some of the tables are big.

                                 

                                Below is part of the code that constructs the where clause, so you can see what I am up against.

                                 

                                      --If order num  is not null append to where clause

                                      IF (order_num_i IS NOT NULL)

                                      THEN

                                         v_string_where :=

                                            v_string_where || ' AND o.order_num = ' || order_num_i;

                                      END IF;

                                 

                                      --If account num is not null append to where clause

                                      IF (account_num_i IS NOT NULL)

                                      THEN

                                         v_string_where :=

                                               v_string_where

                                            || ' AND a.account_num = '

                                            || account_num_i;

                                      END IF;

                                 

                                      --If reason is not null append to where clause

                                      IF (reason_i IS NOT NULL)

                                      THEN

                                         v_string_where :=

                                               v_string_where

                                            || ' AND o.reason_id = '

                                            || reason_i;

                                      END IF;

                                 

                                      --If status is not null append to where clause

                                      IF (status_i IS NOT NULL)

                                      THEN

                                         v_string_where :=

                                               v_string_where

                                            || ' AND e.stts_cd = '''

                                            || status_i

                                            || '''';

                                      END IF;

                                 

                                      --If order type is not null append to where clause

                                      IF (order_type_i IS NOT NULL)

                                      THEN

                                         v_string_where :=

                                               v_string_where

                                            || ' AND o.ORDER_TYPE = '

                                            || order_type_i;

                                      END IF;

                                 

                                      --If name is not null append to where clause

                                      IF (name_i IS NOT NULL)

                                      THEN

                                         v_string_where :=

                                               v_string_where

                                            || ' AND UPPER ( a.name ) LIKE ''%'

                                            || name_i

                                            || '%''';

                                      END IF;

                                 

                                      --If street is not null append to where clause

                                      IF (street_i IS NOT NULL)

                                      THEN

                                         v_string_where :=

                                               v_string_where

                                            || ' AND UPPER ( e.street ) LIKE ''%'

                                            || street_i

                                            || '%''';

                                      END IF;

                                 

                                      --If city is not null append to where clause

                                      IF (city_i IS NOT NULL)

                                      THEN

                                         v_string_where :=

                                               v_string_where

                                            || ' AND UPPER ( e.city ) LIKE ''%'

                                            || city_i

                                            || '%''';

                                      END IF;

                                 

                                      --If Zip code is not null append to where clause

                                      IF (zip_code_i IS NOT NULL)

                                      THEN

                                         v_string_where :=

                                               v_string_where

                                            || ' AND UPPER( e.zip_code ) LIKE ''%'

                                            || zip_code_i

                                            || '%''';

                                      END IF;

                                • 14. Re: Dynamic SQL and avoiding SQL Injection
                                  Jarkko Turpeinen

                                  DevGuy kirjoitti:

                                   

                                  Thanks all for your comments. I really appreciate the feedback. Some added information below.

                                   

                                  The proc returns a REF_CURSOR with the results of the query.

                                  The front end is JSP with Java layer in between, but I am trying to solve this issue from the back end only.

                                  From a operator perspective things are easier since we are using equals "=" for all conditions.

                                  In the example above where each parameter is included in the where clause even if NULL, I'm pretty sure performance will be a big problem because some of the tables are big.

                                   

                                  Below is part of the code that constructs the where clause, so you can see what I am up against.

                                   

                                        --If order num  is not null append to where clause

                                        IF (order_num_i IS NOT NULL)

                                        THEN

                                           v_string_where :=

                                              v_string_where || ' AND o.order_num = ' || order_num_i;

                                        END IF;

                                   

                                        --If account num is not null append to where clause

                                        IF (account_num_i IS NOT NULL)

                                        THEN

                                           v_string_where :=

                                                 v_string_where

                                              || ' AND a.account_num = '

                                              || account_num_i;

                                        END IF;

                                   

                                        --If reason is not null append to where clause

                                        IF (reason_i IS NOT NULL)

                                        THEN

                                           v_string_where :=

                                                 v_string_where

                                              || ' AND o.reason_id = '

                                              || reason_i;

                                        END IF;

                                   

                                        --If status is not null append to where clause

                                        IF (status_i IS NOT NULL)

                                        THEN

                                           v_string_where :=

                                                 v_string_where

                                              || ' AND e.stts_cd = '''

                                              || status_i

                                              || '''';

                                        END IF;

                                   

                                        --If order type is not null append to where clause

                                        IF (order_type_i IS NOT NULL)

                                        THEN

                                           v_string_where :=

                                                 v_string_where

                                              || ' AND o.ORDER_TYPE = '

                                              || order_type_i;

                                        END IF;

                                   

                                        --If name is not null append to where clause

                                        IF (name_i IS NOT NULL)

                                        THEN

                                           v_string_where :=

                                                 v_string_where

                                              || ' AND UPPER ( a.name ) LIKE ''%'

                                              || name_i

                                              || '%''';

                                        END IF;

                                   

                                        --If street is not null append to where clause

                                        IF (street_i IS NOT NULL)

                                        THEN

                                           v_string_where :=

                                                 v_string_where

                                              || ' AND UPPER ( e.street ) LIKE ''%'

                                              || street_i

                                              || '%''';

                                        END IF;

                                   

                                        --If city is not null append to where clause

                                        IF (city_i IS NOT NULL)

                                        THEN

                                           v_string_where :=

                                                 v_string_where

                                              || ' AND UPPER ( e.city ) LIKE ''%'

                                              || city_i

                                              || '%''';

                                        END IF;

                                   

                                        --If Zip code is not null append to where clause

                                        IF (zip_code_i IS NOT NULL)

                                        THEN

                                           v_string_where :=

                                                 v_string_where

                                              || ' AND UPPER( e.zip_code ) LIKE ''%'

                                              || zip_code_i

                                              || '%''';

                                        END IF;

                                  please change that to use bind variables instead of concatenating values to SQL. Performance is one concern (excessive parsing). Other is the topic of your question. And to me that is a hell at the end, sorry

                                   

                                  And most importantly don't be pretty sure about things but test it wrong or right and then be sure

                                   

                                  edit: As i said, start from SQL and when you are happy with it (performance and requirements) then make it dynamic into your code. Not the other way round!

                                  1 2 Previous Next