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

How would you specify those special characters in SQL Developer? What special characters can you use in an Oracle password? If I remember correctly, the password requirements are so restrictive that you can't use " at all. Validating the user input could be enough to prevent SQL injection. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 10, 2019 at 12:07
You can try using execute immediate in a stored proc on the server? adp-gmbh.ch/ora/plsql/change_password.html You could then bind to the proc parameters — Thomas N
– Thomas N, Commented Jan 10, 2019 at 12:17
@ThomasN We've tried to create a proc like this, however we couldn't get that to work — middelpat
– middelpat, Commented Jan 10, 2019 at 12:32
Hey, I had a go and it looks like you don't even need a server side proc. You can declare a BEGIN / END; directly in your command. I've added an answer although it's in ODP.net... don't know if that would help you — Thomas N
– Thomas N, Commented Jan 10, 2019 at 13:53

Dmitrii Bychenko · Accepted Answer · 2019-01-10 13:22:04Z

2

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

edited Jan 10, 2019 at 13:22

answered Jan 10, 2019 at 12:17

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wernfried Domscheit Over a year ago

Instead of a home made regex you can also use DBMS_ASSERT.SQL_OBJECT_NAME

Thomas N · Accepted Answer · 2019-01-10 13:50:08Z

0

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

answered Jan 10, 2019 at 13:50

Thomas N

6431 gold badge4 silver badges14 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related