Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27

    Unanswered: Dynamic SQL, REF Cursors and SQL Injection -- Help

    to get to the point...

    I created a package with a number of procedures performing selects that are commonly done by our developers here in their web application. Very simple stuff:

    TYPE RC_FOUND_THAT is REF CURSOR;
    ...
    PROCEDURE SP_FIND_THAT (
    V_ID IN VARCHAR2,
    RC_FIND_THAT OUT RC_FOUND_THAT)
    IS
    BEGIN
    OPEN RC_FIND_THAT FOR 'SELECT * FROM [table] WHERE ID = '''||V_ID||'''';
    END SP_FIND_THAT;

    See, I told you, simple stuff. Anyway, this is a job I started last week and I came to discover that they have some serious concerns about SQL Injection attacks. And for good reason, it's a gov't project and sure they gotta keep it safe.

    Thing is, that simple example above is the least of my worries. I also spent 2 days coding a search procedure that uses, yup, dynamic sql in the same manner as above. It's a lot longer, but the same general idea...takes about 10 params in, uses those params to build a select statement and returns a ref cursor.

    I really really really don't want to recode that guy.

    So, what are my options? I've done a little reading and read about a comment trick, putting a '--' somewhere in the select statement and using bind variables...what methods do you normally use to avoid sql injection? I'm thinking the consensus will be bind variables, but..and I'm ashamed to admit this, I need a bit of a tutorial..so code examples would be great too.

    THANKS IN ADVANCE.

    Oh BTW: Oracle 8.1.7 with a .NET app sitting on top of it [C#.NET]

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Bind variables are the way to do this:

    OPEN RC_FIND_THAT FOR 'SELECT * FROM [table] WHERE ID = :id' USING v_id;

    This will prevent the SQL Injection issue, as well as performance and scalability issues that will hit you if you continue to hard-code values into your SQL. It also has the side-effect of making your code look a whole lot more readable, and avoids problems when users type apostrophes in their text (name: O'REILLY for example).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •