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.

Alex · Accepted Answer · 2015-06-11T20:31:51.120

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

The code was pulled from the area that polls the DNS server for the UPN and then formats the string using UPN... I forgot to remove the string.format from the sample code. :-) — MrLister, Jun 11 '15 at 20:36

score 0 · Answer 2 · answered Jun 11 '15 at 20:30

0

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

answered Jun 11 '15 at 20:30

jfeston

1,522
1
9
11

1

Using an @ here would pass the string on to PostGres literally, but the formatting issue would still remain. The problem is that the single quote is not escaped in PostGres rather than a character not being escaped in C#. The clue is in the error itself. PostGres mentioned an unterminated string. When it reads the user name, it sees the single quote and assumes that it's the end of the string. Everything following the single quote is therefore not being treated as a string, so PostGres tries to parse it as a command. – Alex Jun 11 '15 at 20:46

Why does postgresql fail on single quote

2 Answers2