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

Question

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

Answer 1

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("\"", "\"\"")}\"");
}