6.2 Handle Oracle Identifiers Carefully

As you have seen in lesson 5, Oracle identifier formats and usage contexts can pose some interesting challenges in input validation.

In addition to following the guidelines for filtering input with DBMS_ASSERT routines, here are a few other issues to pay particular attention:

• Explicitly validate and enforce identifier length limits. Where length limits are being enforced, these should be clearly marked so that they can be changed easily if the length limit does change in future database releases.
• Take care to filter out control characters in user-supplied identifier names. Control characters such as chr(10) (linefeed character) can generate spurious audit records, and so on.
• If an Oracle identifier needs to be generated based on a user-supplied identifier, avoid concatenating the supplied identifier or using sequences to construct the generated Oracle identifier name. Instead, consider looking up the object ID for the user-supplied identifier in the data dictionary and using the object ID in the generated Oracle identifier name.

Click here for an example of generating an Oracle identifier name using the object ID of the user-supplied identifier. In addition to being SQL injection-proof, this example meets the following requirements: The generated name is predictable and unique. The generated name is immune to object renaming.