Why does postgresql fail on single quote

Question

I am trying to programmatically create the postgresql (9.2) "pg_hba.conf" file during our install.

I have tried to create the file using the following:
(yes, the Windows 8.1 User Name has a space and an apostrophe)

private static string[] CreatePgHbaContents()
{
    string[] configLines =
        {
            "# TYPE  DATABASE      USER      ADDRESS                   METHOD",
            string.Format("host    all           \"Mi k'e\"        127.0.0.1/32        sspi"),
            string.Format("host    all           \"Mi K'e\"        ::1/128             sspi"),
            "",
            string.Format("host    all           \"SYSTEM\"        127.0.0.1/32        sspi"),
            string.Format("host    all           \"SYSTEM\"        ::1/128             sspi"),
        };

    return configLines;
}

However, I am getting an error when I try to create the database:

[PostgreConfig:878ab49a][2015-06-11T13:00:08.500-07:00][Error][5b4:1]:Npgsql.NpgsqlException:
unterminated quoted string at or near "e')::name);"
Severity: ERROR
Code: 42601

Any suggestions as to how the user name can be formatted in the configuration file to allow a single quote would be greatly appreciated.

Answer 1

The unterminated quoted string at or near "e' is due to PostGres seeing the single quote, and terminating the statement.

When entering text into a VARCHAR field, I usually replace the single quote with a backtick (` rather than '), which stops this from cropping up at all, but if the quote is in the user name, it's a significant character, so swapping it would break the authentication.

The solution is to escape the single quote with another single quote.

private static string[] CreatePgHbaContents()
{
    string[] configLines =
        {
            "# TYPE  DATABASE      USER      ADDRESS                   METHOD",
            "host    all           \"Mi k''e\"        127.0.0.1/32        sspi",
            "host    all           \"Mi K''e\"        ::1/128             sspi",
            "host    all           \"SYSTEM\"        127.0.0.1/32        sspi",
            "host    all           \"SYSTEM\"        ::1/128             sspi",
        };

    return configLines;
}

I'm a little puzzled by why you have a String.Format in there. You are not actually formatting the string, so it's not necessary.

If you did want to use String.format...

String cleanString = cleanString.Replace("'", "''");

Answer 2

Most likely is an issue with escaping characters. I'd recommend to use @"" to avoid any escaping inconsistency.

string[] configLines =
        {
            @"# TYPE  DATABASE      USER      ADDRESS                   METHOD",
            string.Format(@"host    all           ""Mi k'e""        127.0.0.1/32        sspi"),
            string.Format(@"host    all           ""Mi K'e""        ::1/128             sspi"),
            "",
            string.Format(@"host    all           ""SYSTEM""        127.0.0.1/32        sspi"),
            string.Format(@"host    all           ""SYSTEM""        ::1/128             sspi"),
        };