Oracle: correct way to prevent SQL Injection when using parameters is not possible

Question

I have a C# program which does some operations on Oracle database. One of the operations - create a new account.

CREATE USER {login} IDENTIFIED BY {password}

The problem is that I can't use Parameters here, because this type of query doesn't support it. I didn't find exact documentation about why it's not supported, but I suppose it's similar to what is described here (DDL operations don't support parameters): How to use SqlCommand to CREATE DATABASE with parameterized db name?

So my question would be - how to prevent SQL injection in the following case in Oracle specifically? Should it be some regex to validate login and password? If so, what can it be? It's not clear, because, as Oracle docs says "Nonquoted identifiers cannot be Oracle SQL reserved words. Quoted identifiers can be reserved words, although this is not recommended." How can I check that my login is not ANY of reserved words? And so on.

For MS SQL I have similar situation

CREATE LOGIN {login} WITH PASSWORD=N'{password}'

and I'm solving it in the following way: I escape login with

new SqlCommandBuilder().QuoteIdentifier(name);

and password with simple

password.Replace("'", "''");

because it's a literal in the query.

Answer 1

I think in this case you could use DBMS_ASSERT, following an example of a stored procedure using DBMS_ASSERT to check the name of the user:

create or replace procedure procedure_create_user
   (user_name IN varchar2 , u_password IN varchar2 )
  IS
    tmp_query varchar(150);
    user_name_upper varchar(30) := UPPER(user_name) ;
  BEGIN
    tmp_query := 'create user C##' || dbms_assert.simple_sql_name(user_name_upper) || ' identified by ' || u_password;
    EXECUTE IMMEDIATE ( tmp_query );

    tmp_query := 'grant create session to C##' || user_name_upper ;
    EXECUTE IMMEDIATE ( tmp_query );
  END;
  /

Then you can call this procedure and create the user:

exec procedure_create_user('alessio', 'llll');

From C# you should be able to call the stored procedure in this way:

 using (OracleConnection connection = new OracleConnection("ConnectionString"))
    using (OracleCommand command = new OracleCommand("procedure_create_user", connection))             
    {
          command.CommandType = CommandType.StoredProcedure;
          command.Parameters.Add("user_name", OracleDbType.Varchar2).Value = "alessio";
          command.Parameters.Add("u_password", OracleDbType.Varchar2).Value = "llll";

          connection.Open();
          command.ExecuteNonQuery();
    }

For the password field you should do the same, but you need to be careful if you want to allow some character that is not allowed by the simple_sql_name ... Probably it won't be necessary for the password since even if they are injection code there they shouldn't be able to do much harm since after the identification section(IDENTIFIED BY) the CREATE USER procedure should only accept a limited set of keywords, so if you set even the PASSWORD EXPIRE afterwards, there is probably not much they can inject without getting an error ...