CODEWITHSUNDEEP
c#postgresqlnpgsql
Brian S
Brian S

Reputation: 405

C# PostgreSQL Create Schema with Parameter

I'm converting an existing C# application using MySQL over to PostgreSQL. In this application, I need to create new schemas with a variable name. However, for statements like "CREATE SCHEMA" the variable can't be added as a parameter.

In C# with the existing MySQL database I was able to use:

comm.CommandText = "CREATE DATABASE IF NOT EXISTS " + MySqlHelper.EscapeString(id);

This protected against an injection attack when creating a new database.

In PostgreSQL I am also trying to create a schema with a variable name:

NpgsqlCommand createSchema = new NpgsqlCommand("CREATE SCHEMA IF NOT EXISTS @schema", conn);
createSchema.Parameters.AddWithValue("schema", id);
createSchema.ExecuteNonQuery();

42601: syntax error at or near "$1"

I would also need this to sanitize the schema name in a connection string:

        public static string getConnStr(string id = null)
        {
            var connStringBuilder = new NpgsqlConnectionStringBuilder();
            connStringBuilder.Host = "localhost";
            connStringBuilder.Port = 5432;
            //connStringBuilder.SslMode = SslMode.Require;
            connStringBuilder.Username = "XXXX";
            connStringBuilder.Password = "XXXX";
            connStringBuilder.Database = "XXXX";
            connStringBuilder.SearchPath = id;
            return connStringBuilder.ConnectionString;
        }

The SearchPath needs to be sanitized for the connection string.

Is there an equivalent to MySqlHelper.EscapeString() for Postgresql?

Upvotes: 1

Views: 2171

Answers (1)

Shay Rojansky
Shay Rojansky

Reputation: 16682

PostgreSQL does not support parameters in DDL statements such as CREATE DATABASE or CREATE SCHEMA - only in SELECT, UPDATE and so on.

You will have to sanitize the names yourself (to avoid SQL injection) and use string concatenation.

Upvotes: 3

Related Questions