CODEWITHSUNDEEP
pythonpostgresqlunicodepsycopg2
Kevin Dolan
Kevin Dolan

Reputation: 5097

Python unicode string rejected by psycopg

I've received a unicode string from the wild that causes some of our psycopg2 statements to fail.

I have reduced the problem down to a SSCE:

import psycopg2
conn = psycopg2.connect(...)
cur = conn.cursor()
x = u'\ud837'
cur.execute("SELECT %s", (x,))
print cur.fetchone()

Running this gives the following exception:

Traceback (most recent call last):
  File ".../run.py", line 65, in <module>
    cur.execute("SELECT %s AS test", (x,))
psycopg2.DataError: invalid byte sequence for encoding "UTF8": 0xed 0xa0 0xb7

Based on some of the comments, it has become clear that this particular character is one half of a surrogate pair, making it invalid to live on its own.

Specifically then, I am looking for a mechanism to detect when a string contains an incomplete surrogate pair in Python 2.

One such method I have found that leads to an exception is trying x.encode('utf16').decode('utf16'), however, since I don't totally understand the risks associated, I would be somewhat concerned here.

Edit: Reduced SSCE string to single character causing the problem, added information based on comments.

Upvotes: 2

Views: 3722

Answers (2)

user4815162342
user4815162342

Reputation: 154916

The string u'\ud837' consists of a lone member of a surrogate pair, two physical characters that appear in sequence to form a logical character. As such, it does not define a Unicode code point - instead, it is an implementation detail of the UTF-16 encoding which uses it to pack the full code point range into 16-bit code units. Python 3 correctly rejects attempts to encode lone surrogates in any byte encoding, including the UTF-* variants.

The string probably originated from a system that internally uses UTF-16 (such as Java, C#, Windows, or Python 2 built with 16-bit Py_UNICODE) that naively shortened the string without taking care of surrogates.

Taking the regex from this answer, it should be possible to efficiently detect such strings using code such as:

import re

lone = re.compile(
    ur'''(?x)            # verbose expression (allows comments)
    (                    # begin group
    [\ud800-\udbff]      #   match leading surrogate
    (?![\udc00-\udfff])  #   but only if not followed by trailing surrogate
    )                    # end group
    |                    #  OR
    (                    # begin group
    (?<![\ud800-\udbff]) #   if not preceded by leading surrogate
    [\udc00-\udfff]      #   match trailing surrogate
    )                    # end group
    ''')

def invalid_unicode(s):
    assert isinstance(s, unicode)
    return lone.search(s) is not None

Upvotes: 2

lucasnadalutti
lucasnadalutti

Reputation: 5948

To detect that the string is invalid utf-8, just wrap an attempt to encode it inside a try/except before executing it in psycopg2.

As for what caused the problem, there is a specific character in the middle of the string that is utf-16 encoded: \U000d8a85. So it's not that Postgres does not consider it utf-8, it really isn't.

Upvotes: 2

Related Questions