2 Replies Latest reply: Jul 6, 2014 10:10 AM by matthew_morris RSS

    A question from OCP 1z0-146 Guide

    2707373

      I have a doubt, (Hope Mathew will pick this)

       

      How does using Bind variables instead of concatenating User input (in dynamic SQL) reduce the risk of SQL injection ?

       

      I mean, even if we use bind variables, it is the same user input that is gonna go to dynamic SQL statement, right ?

       

      How does this effectively eliminate the risk ?

       

      Please correct me if I am wrong.

        • 1. Re: A question from OCP 1z0-146 Guide
          matthew_morris

          Let's imagine a user has access to part of the data in a Sales table based on what division they belong to.  GJONES is logged into the application and we know he is with the EASTERN division.  A given screen/query allows them to look for sales in a given month.  The query using bind variables looks like the following:

           

          v_sql :=

          'SELECT [sales columns]

          FROM [sales table]

          WHERE division = 'EASTERN'  <--- pulled from GJONES' data

          AND  month = :month_var';

           

          There is no way to alter the intent of this query.  GJONES can put in a valid month and get the data for that month for the Eastern division or he can put in an invalid month and get nothing.

           

          Let's say that  instead of using bind variables, we concatenated the text supplied by the user to the query, i.e.:

           

          v_sql :=

          'SELECT [sales columns]

          FROM [sales table]

          WHERE division = 'EASTERN'  <--- pulled from GJONES' data

          AND  month = ' || user_text;

           

          Now, the user can supply more to the WHERE clause than just a month.  For example, they could supply: " 'JULY' OR 1=1". When concatenated to the existing SQL, this results in a valid query that is always true because 1 always equals 1. It would return all rows in the table, including ones outside the Eastern region.  Had that text been supplied to the bind variable query, it would have results in no rows being returned.

          • 2. Re: A question from OCP 1z0-146 Guide
            matthew_morris

            There are also two articles on my page for 1Z0-146 about preventing SQL injection attacks that you may want to look at.  One of them is from the Oracle Learning Library.  These are some of the most valuable resources to prepare with (when they exist for a topic).

             

            Oracle Certification Prep: Exam details and preparation resources for 1Z0-146