0

I have a need to write dynamic DDL statements such as CREATE USER ?. I know that I can use EXECUTE IMMEDIATE to do this, but so far I have found no way to incorporate dynamic parameters without simply concatenating strings, which leaves me open to SQL injection.

Coming from PostgreSQL I an used to being able to quote identifiers or using FORMAT to safely put identifiers into a formatted string that can then be executed. Is there anything like this in Oracle (particularly 12c)? If not, how does one perform such dynamic SQL safely?

4

DBMS_ASSERT might be what you're looking for:

╔═════════════════════════════╦══════════════════════════════════════════════════════════════════════════════════════════════════╗
║         Subprogram          ║                                           Description                                            ║
╠═════════════════════════════╬══════════════════════════════════════════════════════════════════════════════════════════════════╣
║ ENQUOTE_LITERAL Function    ║ Enquotes a string literal                                                                        ║
║ ENQUOTE_NAME Function       ║ Encloses a name in double quotes                                                                 ║
║ NOOP Functions              ║ Returns the value without any checking                                                           ║
║ QUALIFIED_SQL_NAME Function ║ Verifies that the input string is a qualified SQL name                                           ║
║ SCHEMA_NAME Function        ║ Verifies that the input string is an existing schema name                                        ║
║ SIMPLE_SQL_NAME Function    ║ Verifies that the input string is a simple SQL name                                              ║
║ SQL_OBJECT_NAME Function    ║ Verifies that the input parameter string is a qualified SQL identifier of an existing SQL object ║
╚═════════════════════════════╩══════════════════════════════════════════════════════════════════════════════════════════════════╝

That same page also links to some examples on how you can avoid SQL injection.

  • Excellent, looks exactly like what I need. Thanks! – beldaz Jun 2 '17 at 19:45

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.