Qfyilyi

I'm trying to run a CREATE USER query on an oracle 12 database using the Oracle.ManagedDataAccess library. This query works perfectly without using named parameters. But when i try to use named parameters it looks like the parameters are not passed to the query correctly.

I try the use the parameters with the following code (Note: Im using Oracle.ManagedDataAccess library):

using(OracleConnection con = new OracleConnection(_connectionString)) {

    con.Open();

    using(OracleCommand cmd = con.CreateCommand()) {

        cmd.CommandText = "CREATE USER :username IDENTIFIED BY :password";

        cmd.Parameters.Add("username", "A_TESTUSER");

        cmd.Parameters.Add("password", "A_PASSWORD");

        //cmd.Parameters.Add(":username", "A_TESTUSER"); <- also tried

        //cmd.Parameters.Add(":password", "A_PASSWORD"); <- also tried

        //cmd.Parameters.Add(":username", OracleDbType.Varchar2).Value = "A_TESTUSER"; <- also tried

        //cmd.Parameters.Add(":password", OracleDbType.Varchar2).Value = "A_TESTPASS"; <- also tried

        cmd.ExecuteNonQuery();

    }

}

which results in the following exception.

Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01935: missing user or role name.

The query works when I set when i run the query with the data inline like this

cmd.CommandText = "CREATE USER A_TESTUSER IDENTIFIED BY A_PASSWORD";

I also tried to use @ instead of : to indicate the parameters without succes. How should I pass the parameters to the query?

I don't think you can use parameterized queries for CREATE USER, so it should be

cmd.CommandText = String.Format("CREATE USER {0} IDENTIFIED BY "{1}"", "A_TESTUSER", "A_PASSWORD");

Note: Although this solution works, it does not protect you from SQL injection. You should do the needed checks on your inputs in addition to this.