Parameterized queries to create a user throwing missing user or role name exception

0

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?

c#
.net
oracle
named-parameters
asked on Stack Overflow Nov 22, 2018 by Mark Baijens • edited Nov 22, 2018 by Mark Baijens

1 Answer

0

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.

answered on Stack Overflow Nov 22, 2018 by Wernfried Domscheit • edited Nov 22, 2018 by Mark Baijens

User contributions licensed under CC BY-SA 3.0