Writing SQL injection proof dynamic DDL in Oracle

Question

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?

Answer 1

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.

• beldaz: Excellent, looks exactly like what I need. Thanks!