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.