Postgresql in JSON 'Escape sequence "\&" is invalid'

Question

I store HTML and various bits of text in a JSON structure in Postgresql, written using Python and the pg8000 library. I have found that I often get errors similar to the following:

pg8000.core.ProgrammingError: {'S': 'ERROR', 'V': 'ERROR', 'C': '22P02', 'M': 'invalid input syntax for type json', 'D': 'Escape sequence "\&" is invalid.', 'W': 'JSON data, line 1: ...Ga,"&#39;"));-1!=a.indexOf("\&...', 'F': 'json.c', 'L': '951', 'R': 'json_lex_string'}

So to strip these I have the following horrifically slow code that I keep having to add specific 'invalid escape sequences' to:

    def filter_values(value: Any) -> Any:
        if isinstance(value, list):
            return list(map(filter_values, value))
        if isinstance(value, dict):
            return {k:filter_values(v) for k, v in value}
        if isinstance(value, str):
            stripped = value.strip().replace('\u0000', '').replace('\u0000', '').replace(u'\u0000', '')
            while '\-' in stripped:
                stripped = stripped.replace('\-', '-')
            while '\+' in stripped:
                stripped = stripped.replace('\+', '+')
            while '\E' in stripped:
                stripped = stripped.replace('\E', 'E')
            while '\&' in stripped:
                stripped = stripped.replace('\&', '&')
            return stripped
        return value

If I can't just strip all \ as that will break the json (such as "). There has to be a better way to do this. What are the rules around correctly escaping characters in JSON Postgresql blobs?

Postgresql in JSON 'Escape sequence "\\&" is invalid'

0 Answers