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)
OPEN RC_FIND_THAT FOR 'SELECT * FROM [table] WHERE ID = '''||V_ID||'''';
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]
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).