C# PostgreSQL Create Schema with Parameter

Question

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?

Shay Rojansky · Accepted Answer · 2019-06-10 13:11:32Z

3

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.

answered Jun 10, 2019 at 13:11

Shay Rojansky

16.9k3 gold badges44 silver badges73 bronze badges

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

2 Comments

Brian S Over a year ago

What would be the best way to sanitize a string input for postgresql? I'm having a hard time finding a proper example with postgresql in C#, self-sanitizing is pretty easy to get wrong. Thank you!

Brian S Over a year ago

I'm currently looking at the following: safeString = unsafeString.Replace("'","''"); along with quoting all of my variable inserts. Would this be sufficient?

Collectives™ on Stack Overflow

C# PostgreSQL Create Schema with Parameter

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related