2 Replies Latest reply: Aug 4, 2010 12:26 PM by 89521 RSS

    Oracle Text Contains Strings and SQL Injection

    89521
      The following is a query that is contained in a stored procedure. The value p_last_name is passed to the stored procedure. It is a VARCHAR2(40) value that may be input by a user via a web interface. To what extent is this query vulnerable to SQL injection? Is there a preferred way to code a query like this to minimize SQL injection -- for example, making the 'FUZZY({' || p_last_name || '},,, WEIGHT)' string a bind value?

      Thanks

      SELECT first_name,
      last_name,
      SCORE(1) AS score
      FROM student
      WHERE CONTAINS(last_name, 'FUZZY({' || p_last_name || '},,, WEIGHT)', 1) > 0
      ORDER BY
      SCORE(1) DESC,
      last_name;
        • 1. Re: Oracle Text Contains Strings and SQL Injection
          Tubby
          smartini@ccpace.com wrote:
          The following is a query that is contained in a stored procedure. The value p_last_name is passed to the stored procedure. It is a VARCHAR2(40) value that may be input by a user via a web interface. To what extent is this query vulnerable to SQL injection? Is there a preferred way to code a query like this to minimize SQL injection -- for example, making the 'FUZZY({' || p_last_name || '},,, WEIGHT)' string a bind value?

          Thanks

          SELECT first_name,
          last_name,
          SCORE(1) AS score
          FROM student
          WHERE CONTAINS(last_name, 'FUZZY({' || p_last_name || '},,, WEIGHT)', 1) > 0
          ORDER BY
          SCORE(1) DESC,
          last_name;
          Unless this is dynamically generated (which it doesn't look like) PL/SQL will create a bind variable out of P_LAST_NAME.

          So there's no worry.

          You can read about SQL Injection here...
          http://www.oracle.com/technology/oramag/oracle/05-jan/o15asktom.html
          • 2. Re: Oracle Text Contains Strings and SQL Injection
            89521
            Thanks for the quick reply.