In situations where we cannot use bind variables, such as when our dynamic queries have to execute ddl statements, is the following list of defenses enough?

  1. Never use anonymous blocks in dynamic queries so that only one statement can be executed by execute immediate. This stops code injection attacks.

  2. Escape all single quotes using replace function. This stops statement modification attacks.

What characters other than single quote can be used for quoting and how can they be escaped?

How to prevent statement modification through AND, UNION etc attacks?

How to prevent function calling attacks so that user cannot call built-in functions? Every user has rights of calling those functions and calling of those functions can cause denial of service and buffer over flow attacks. How to save from that?

I prefer allowing gui to take single quote character means not check that in client side and server side validation in a web application. This is to allow names like O'Brian. At the database level, just before the execute immediate statement escape the single quotes. Do you know of any better approach?

Solution to any other vulnerabilities not listed above.

Note: I have already gone through about a dozen questions related to SQL injection on this site. I still posted this question because:

  1. It's specific to oracle. Most questions I found on this site on the topic are related to MySQL, SQL server etc.

  2. It's specific to situations where bind variables cannot be used. If one can use bind variables then that is enough and no other defense is needed.

  3. It's better to list down all needed methods at one place.

  4. Some advanced techniques of SQL injection like function calling are not discussed in detail and I cannot find any solution against that.


Following may be a viable solution.

I think I have a solution. Its in addition of using usual defenses such as static statements, bind variables etc. Its particularly useful in situations where the usual defenses cannot be used. Note that the only situation where bind variables cannot be used is ddl statements. For such statements:

  1. Verify existence of database object using static sql. This solves half of the problem.

  2. The other half is related to the new value we want to put in the database object. For example when changing password of a user: the first half is username, the second half is password. One should encrypt the new value at front-end and save encrypted value in database. The encrypted value encrypted as sql code cannot do any damage to database (cannot call any functions for example).

  3. Never change user input because of various reasons such as confusing user for example in passwords, the value may be valid in some situations such as it can be a valid html etc. It means let the ['], [\'], [#] pass through all validations unchanged. It's the static SQL or the encryption that is supposed to handle it.

Is there any situation where we cannot encrypt the new value?

  • 2
    Look into the DBMS_ASSERT package, it has functions that are very useful for helping to prevent SQL Injection attacks if you have to use dynamic SQL. See this article: oracle-base.com/articles/10g/dbms_assert_10gR2.php
    – Ollie
    Apr 26, 2012 at 6:36
  • I have seen that but not sure how can they prevent function-call, and union attacks. They seems like replacing single quotes with two single quotes, which is not good for various reasons.
    – Atif
    Apr 26, 2012 at 8:29
  • 2
    It is used as part of crafting your dynamic SQL statements. It is not a "cure all", but if there was one then SQL Injection would be instantly redundant. The main factor in preventing SQL injection is ensuring you design your dynamic SQL carefully. There are many techniques used to prevent it, all depending upon your circumstances. It's a skill to know which to use when.
    – Ollie
    Apr 26, 2012 at 8:34
  • How to "design your dynamic sql carefully" is the question. It can't also be the answer :) because then its circular logic. "There are many techniques used to prevent it" such as?
    – Atif
    Apr 26, 2012 at 8:55
  • it is the answer unfortunately. It is not a question that can be answered easily here on SO. If you give an exact scenario then it can be answered but such a general question would take too long. Hence you have had no answers. The many techniques depend upon the exact situation you are facing until you spell out an exact situation you'll not get an exact answer. I know it's not what you wanted to hear but that's the way it is.
    – Ollie
    Apr 26, 2012 at 9:31


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.