CODEWITHSUNDEEP
c#oracle-database
middelpat
middelpat

Reputation: 2585

Change user password for Oracle database using sql statement in C#

I'm working on a requirement where I have to change the oracle connection password of the current user from my application.

I have found that I can use the following statement to achieve this task using SQL:

ALTER USER *username* IDENTIFIED BY *password*

However because the username and password aren't sent to the database as quoted strings, I can't use bind parameters. (Which is also stated by this answer)

I have a working solution when I concatenate a string and sent it as a regular sql query over my Entity Framework DbContext instance like this:

using (var context = _dbContextFactory.CreateContext())
{
    await context.Database.ExecuteSqlCommandAsync(
      $"ALTER USER {username} IDENTIFIED BY \"{newPassword}\"");
}

The downsides of this approach is that by concatinating the password in the string I have SQL injection vulnerabilities and the user can't use some reserved characters in their passwords, like e.g. ; and "

I'm not concerned about the username parameter because this is managed within the backend code, however the password is directly from user input.

Is there any way to change the current users password in the Oracle database from C# using a safe approach? I'm also open to other approaches like a diffrent method or creating a stored procedure in the database, as long as it can be implemented in a C# client application.

We are using Oracle version 12+, so I can't use the IDENTIFIED BY VALUES '' syntax

Upvotes: 3

Views: 8529

Answers (2)

Thomas N
Thomas N

Reputation: 633

This seems to be working on my local Oracle test db (from ODP.net driver). The important bit seemed to be BEGIN / END; (wouldn't work without it).

using (var con = (OracleConnection)db.Server.GetConnection())
{
    con.Open();

    //string pw = "'''';++";
    string pw = "newpass";

    var cmd = new OracleCommand(@"
BEGIN
EXECUTE IMMEDIATE CONCAT('ALTER USER B identified by ',:pw);
END;", con);
    cmd.CommandType = CommandType.Text;

    var p2 = cmd.CreateParameter();
    p2.ParameterName = "pw";
    p2.Value = pw;
    p2.DbType = DbType.String;
    cmd.Parameters.Add(p2);

    cmd.ExecuteNonQuery();
}

Upvotes: 0

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

For username we have to provide Oracle Identifier (in case we keep the original query) which is

  • Up to 30 characters in length
  • Must start with a letter
  • Can include $ (dollar sign), _ (underscore), and # (hash sign)

we can validate provided value via regular expressions:

if (!Regex.IsMatch(username, @"^[A-Za-z][A-Za-z0-9_#\$]{0,29}$")) {
  // username is invalid
}

For the password we can

  • Double all quotations: my"password -> my""password
  • Ensure that the password contains valid characters only (e.g. let's exclude unicode control characters like back space and other)

So the code will be something like this

if (!Regex.IsMatch(username, @"^[A-Za-z][A-Za-z0-9_#\$]$")) {
  // username is invalid
}

if (string.IsNullOrEmpty(newPassword) || newPassword.Any(c => char.IsControl(c))) {
  // password is invalid
}

using (var context = _dbContextFactory.CreateContext()) {
  await context.Database.ExecuteSqlCommandAsync(
    $"ALTER USER {username} IDENTIFIED BY \"{newPassword.Replace("\"", "\"\"")}\"");
}

Upvotes: 2

Related Questions