PostgreSQL adapter does not correctly quote bytea values

[d11wtq]

When trying to pass a value for a bytea field, that includes an ASCII backlash, DataObjects does not correctly escape the input.

conn.create_command("CREATE TABLE bob (salt bytea)").execute_non_query
conn.create_command("INSERT INTO bob (salt) VALUES (?)").execute_non_query("FJ\\")

ERROR:  invalid input syntax for type bytea (DataObjects::DataError)
LINE 1: INSERT INTO users (password_salt) VALUES ('FJ\')
                                                  ^

The backslashes need to be double-escaped for bytea casts to work. See http://www.postgresql.org/docs/8.1/static/datatype-binary.html

[dbussink]

If you want to use byte array's directly, you have to wrap the string in a ByteArray object:

conn.create_command("INSERT INTO bob (salt) VALUES (?)").execute_non_query(::Extlib::ByteArray("FJ\\"))

This is needed because PostgreSQL needs different escaping for strings and binary data, so the driver needs to be able to discern between these. This is done with this separate type.

[d11wtq]

Ah, I see, that makes sense, thanks!

BTW, any reason you're not just using prepared statements with bind values at the C API level?

[dbussink]

It doesn't support all the cases we want to support for various Ruby types. I've thought about it a few times but haven't come up with a satisfactory approach yet.

[d11wtq]

That's a shame, because it saves a lot of headaches and is a bit less hacky than doing string substitutions.

[dbussink]

It is, but it's actually the reason that a lot of database drivers have their own logic for it (for example all the Perl database drivers do it themselves too).

Another problem is that DO tries to unify the API's of different database drivers, and all the differences in prepared statement API's of databases doesn't help there either.