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.
User contributions licensed under CC BY-SA 3.0