Npgsql CREATE USER using ExcuteNonQuery parameter

Question

Hi I am trying to use SQL CREATE USER with NpgsqlParameter (to prevent sql injection):

var p = new NpgsqlParameter("p1", "testuser");

using (var cmd = new NpgsqlCommand("CREATE USER @p1", (NpgsqlConnection)sqlConn))
{
    cmd.Parameters.Add(p)
    cmd.ExecuteNonQuery();
}

I get a run time error

syntax error at or near $1

Can anyone help me out please?

Answer 1

Alas, you can't use binding variables with CREATE USER. To prevent sql injection use quotation: "me; delete from myTable" -> "'me; delete from myTable'":

string userName = ...

using (var cmd = new NpgsqlCommand(
  $"CREATE USER '{userName.Repace("'", "''")}'", 
    (NpgsqlConnection)sqlConn)) {

  cmd.ExecuteNonQuery();
}

Here we double each apostrophe which is within userName and then wrap chnaged name into apostrophes