Re: Correctly producing array literals for prepared statements
От | Kenneth Marshall |
---|---|
Тема | Re: Correctly producing array literals for prepared statements |
Дата | |
Msg-id | 20110223154029.GC9375@aart.is.rice.edu обсуждение исходный текст |
Ответ на | Re: Correctly producing array literals for prepared statements (Merlin Moncure <mmoncure@gmail.com>) |
Ответы |
Re: Correctly producing array literals for prepared statements
(Andrew Dunstan <andrew@dunslane.net>)
|
Список | pgsql-hackers |
On Wed, Feb 23, 2011 at 09:34:06AM -0600, Merlin Moncure wrote: > On Tue, Feb 22, 2011 at 10:16 PM, Peter Geoghegan > <peter.geoghegan86@gmail.com> wrote: > > I'm investigating the possibility of developing a utility function for > > our C++ client library, libpqxx, that produces array literals that can > > be used in prepared statements. This problem appears to be a bit of a > > tar pit, so I'm hoping that someone can help me out. My goal is to > > produce a template function that accepts arbitrarily nested standard > > library containers, that contain at the most nested level > > constants/literals of some type that can be fed into a stream, such as > > an int or a std::string. > > > > I'm aware that I cannot assume that types are delimited by a single > > quote, even for built-in types. I thought that I would put the onus on > > the client to specify the correct delimiter, by checking pg_type > > themselves if necessary, but default to ',' . Is this a reasonable > > approach? > > > > Escaping/quoting individual elements seems tricky. I have produced a > > generic and superficially well behaved implementation by using double > > quotes for constants. However, I have now opened the door to malicious > > parties injecting multiple array elements where only one is allowed, > > or causing malformed array literal errors by simply including a double > > quote of their own. It's not clear where the responsibility should > > rest for escaping constants/ensuring that constants don't contain > > double quotes. Can someone suggest a better approach? I can't very > > well use single quotes, because they are escaped/doubled up when we > > pass the array literal to something similar to PQexecPrepared(), and > > they shouldn't be - strings end up looking like this: "'has errant > > single quotes on either side'". > > You can send nested arrays safely. You just have to be very formal > about escaping *everything* both as you get it and as it goes into the > container. This is what postgres does on the backend as it sends > arrays out the door in text. It might be instructive to see what the > server does in terms of escaping. Note that the way this works it's > not impossible to see 128+ consecutive backslashes when dealing with > arrays of composites. > > > Since Postgres only supports encodings that are ASCII supersets, I > > don't believe that I have to consider encoding - only my clients do. > > > > Can someone please point me in the direction of an established client > > library/driver where all corner cases are covered, or at least enough > > of them to produce a net gain in usefulness? There may well be > > additional subtleties that have not occurred to me. > > yes: libpqtypes. it manages everything in binary. i've been thinking > for a while that libpqtypes could be wrapped with variadic templates > or other c++ trickery. Because libpqtypes does everything in binary, > it completely sidesteps all the escaping nastiness. > > merlin > Avoiding the escaping by using binary parameter transmission is the best method. Shameless plug: libpqtypes is great! I hope that it can be eventually included in the core distribution. It is not uncommon to get "It's an add-on package???" and avoidance of pieces outside of the standard dist regardless of its value. Regards, Ken
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Andrew DunstanДата:
Сообщение: Re: Correctly producing array literals for prepared statements