Postgres bytearray escaping

Issue #262 invalid
Pavel K
created an issue

Easy example:

Zlib::Inflate.inflate(PG::Connection.unescape_bytea(PG::Connection.escape_bytea(Zlib::Deflate.deflate('["128,491,128,487"]'))))
Zlib::DataError: incorrect data check

This is not issue with zlib because following succeeds:

Zlib::Inflate.inflate(Zlib::Deflate.deflate('["128,491,128,487"]'))
=> "[\"128,491,128,487\"]"

Parsing fails/succeeds dependant on provided string:

Zlib::Inflate.inflate(PG::Connection.unescape_bytea(PG::Connection.escape_bytea(Zlib::Deflate.deflate('["128,491,128,487", "128,491,128,490", "38,465,40,463"]'))))
=> "[\"128,491,128,487\", \"128,491,128,490\", \"38,465,40,463\"]"

Am i doing something wrong or is it broken?

Tried on: Ruby 2.2.3p173, gem pg-0.18.4; Ruby 2.4.1p111, gem pg-0.21.0

Comments (2)

  1. Lars Kanis

    @Pavel K The description of (un)escape_bytea is misleading. It's not the exact opposite of each other.

    PG::Connection.escape_bytea uses the old and deprecated escaping mechanism, which must be double escaped - first as a BYTEA and secondary as a string literal (per adding ' before and after the string) for insertion into the SQL string. This is done in one step for convenience and performance reasons.

    On contrary PG::Connection.unescape_bytea does only BYTEA unescaping, because it is meant to decode column data retrieved by a query. This data is not escaped as a string literal.

    You now have two options to make the above work:

    enco = PG::TextEncoder::Bytea.new
    deco = PG::TextDecoder::Bytea.new
    Zlib::Inflate.inflate(deco.decode(enco.encode(Zlib::Deflate.deflate('["128,491,128,487"]'))))
    

    This makes use of the type encoders of the pg gem instead of libpq. The encoder uses the newer BYTEA escaping machanism and is a bit faster than libpq's functions. This is still independent of a server connection.

    conn = PG.connect
    Zlib::Inflate.inflate(conn.unescape_bytea(conn.escape_bytea(Zlib::Deflate.deflate('["128,491,128,487"]'))))
    

    This makes use of the connection bound escaping functions of libpq. They also use the newer escaping mechanism, so that no double escaping occurs.

  2. Log in to comment