Escape @ signs in query

[schultek]

I would like a way to escape '@' signs in a query that should not be treated as a substitution parameter.

Say I have an email like [email protected] in my query and use a substitution parameter @1: somevalue for something else. Then either the query fails with an error or I have some wrong data in the database.

• db.query('INSERT INTO "email" VALUES (@b), (\'[email protected]\')', substitutionValues: {'b': 'someval'}) leads to error PostgreSQLSeverity.error 08P01: bind message supplies 2 parameters, but prepared statement "000000000000" requires 1
• db.query('INSERT INTO "email" VALUES (@b), (\'a@@b.com\')', substitutionValues: {'b': 'someval'}) leads to no error, but a@@b.com is inserted into the database as is

It would be nice to have a way to escape @ signs independently of whether there is a parameter using the same name or not.

This is somewhat related to #72, as a more low-level api to define parameters would also be fine. I'm developing an ORM and currently can't use query parameters because of this.

[isoos]

I think this is a good candidate for the v3 API requirement list (see also #74).

At the moment there is no good solution for it. You may use another substituted value in place of '[email protected]', so that it gets recognized/escaped properly.

[simolus3]

In the V3 API, we will ignore at signs in comments, string literals and escaped identifiers. The API also allows not scanning the query for at signs at all, which may be helpful when writing an ORM that manages variables itself.

Do you see another use case for escaping at signs once they are ignored in those places?

[ailabs-software]

Will @> and <@ operators work in the V3 API (they did not work when I tested in the current API as they were escaped)?

[simolus3]

Thanks for the report. They should continue to work, and it seems we have a test for that as well:

https://github.com/isoos/postgresql-dart/blob/46c3ff86f7377015c453cd84ce123071a052ac63/test/variable_tokenizer_test.dart#L140-L148

Could you post the SQL or the Dart code you're using to run the statement that seems to escape these operators? Perhaps I've missed something, but that would be a bug we should fix :)

[ailabs-software]

@simolus3 I recreated my test and realised I'm actually wrong! Glad to see the v3 improvements coming though.

[isoos]

@schultek: could you please check if the current v3 prerelease does what you want it to do?

[isoos]

Closing as the v3 queries have better escape support now.