CODEWITHSUNDEEP
sqloracle-databaseplsqlsql-injection
Dips
Dips

Reputation: 11

How to prevent SQL Injection in PL/SQL

We have some few packages where we need to resolve some SQL Injection issues. I need some help to rewrite sql statement or sanitize the inputs. Below is the line number where veracode throw the error.

open c_ccl (p_part_nr,p_ctry_cd);

// Source code

     CREATE OR REPLACE EDITIONABLE PACKAGE BODY "schema"."Test_PKG" AS

  v_data t_cla_class_data;

  FUNCTION nat_eccn_cd( p_part_nr  IN    t_part_nr, p_ctry_cd    IN     t_ctry_cd         )            
  RETURN t_us_eccn_cd IS
  CURSOR c_ccl(p_part_nr CHAR, p_ctry_cd CHAR)  IS
  SELECT NAT_CCL_CD  FROM CLSDBA.CLA_EXP_PART_CTRY e
     WHERE  e.PART_NR = p_part_nr  AND e.CTRY_CD = p_ctry_cd
      ORDER BY e.VAL_FROM_DT DESC;
  v_ctry_cd char(4) := p_ctry_cd;
  v_trf_cd char(4);
  BEGIN
    v_data.nat_eccn_cd := NULL;
    open c_ccl (p_part_nr,p_ctry_cd);
    fetch c_ccl INTO v_data.nat_eccn_cd;
    close c_ccl;
    return (trim(v_data.nat_eccn_cd));
    exception when others then return NULL;
  end;

Upvotes: 1

Views: 1144

Answers (1)

Jon Heller
Jon Heller

Reputation: 36807

I don't see any SQL injection issues with your code - there is no dynamic code where the user inputs could be evaluated and escape out of the expected code flow. Unless your code snippet is generated somewhere else, or one of the column names is really a function that calls dynamic SQL, your code looks safe.

You used the phrase "sanitize the inputs", which is terrible advice for database programming. As much as I love the comic strip XKCD, Randall got this one wrong.

Bind variables are the best solution to avoiding SQL injection. I'll take this opportunity to (poorly) change his comic:

enter image description here

Upvotes: 1

Related Questions