On 23 February 2011 15:34, Merlin Moncure <mmonc...@gmail.com> wrote:
> 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.
Sounds tedious.
> 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.
The fact that libpqtypes does everything in binary mode is
interesting, but doesn't really help me.
Variadic template support is still quite patchy, and I don't think
that it is of particular use here. My proof-of-concept implementation
uses recursive template instantiation and type traits, and just uses
C++98 features. I've attached it for your information. I might be able
to use partial template specialisation to support regular arrays too.
That hasn't been a priority, because C++ generally discourages their
use, and because it's trickier. Arrays don't "know their own size",
and I want to provide a uniform, simple interface. On the other hand,
I've seen interesting things done with template specialisation on
static integral values, such as the size of arrays on the stack, so
perhaps it's possible to support arrays while having a uniform
interface.
To be clear: I don't want to take responsibility for correctly
escaping the array literal. The user has a responsibility to use a
prepared statement/explicit escaping to do that, just as they do with
a regular text value, for example. There is no additional threat of a
traditional SQL injection attack, because we cannot break out of the
array literal itself. However, within the array literal, it is
currently possible to break out of a constant/value literal using a
double quote, to perhaps inject additional values (more than
intended), or to cause malformed array literal errors. Sure, I could
write my own function to escape the constant which is wary of double
quotes, but that would have many of the same challenges as writing a
general purpose drop-in replacement for PQescapeStringConn(). It might
be just as misguided.
--
Regards,
Peter Geoghegan
#include <sstream>
#include <iostream>
#include <string>
#include <vector>
#include <deque>
#include <list>
#include <set>
#include <pqxx/pqxx>
using namespace std;
using namespace pqxx;
struct true_type
{
};
struct false_type
{
};
template<typename T>
struct is_container:public false_type
{
};
template<typename T, typename U>
struct is_container<vector<T, U> >:public true_type
{
};
template<typename T, typename U>
struct is_container<deque<T, U> >:public true_type
{
};
template<typename T, typename U>
struct is_container<list<T, U> >:public true_type
{
};
template<typename T, typename U, typename V>
struct is_container<set<T, U, V> >:public true_type
{
};
template<typename T>
void do_pg_empty_element(const T&, stringstream&, false_type)
{
// do nothing; there is no element/constant
}
template<typename container>
void do_pg_empty_element(const container& elm, stringstream& append, true_type)
{
typedef typename container::value_type contained_type;
// empty inner array
append << "{";
do_pg_empty_element(contained_type(), append, is_container<contained_type>());
append << "}";
}
// terminating condition: individual elements
template<typename T>
void do_pg_array_element(const T& elm, stringstream& append, false_type)
{
append << "\"" << elm << "\"";
}
// append outer part of nested array
template<typename container>
void do_pg_array_element(const container& cnt, stringstream& append, true_type)
{
typedef typename container::const_iterator it;
typedef typename container::value_type contained_type;
it last = cnt.end();
if(!cnt.empty())
{
// there is no neater way of determining if
// an element is the last that works across
// std lib containers
--last;
}
else
{
do_pg_empty_element(cnt, append, true_type());
return;
}
append << "{";
for(it i = cnt.begin(); i != cnt.end(); ++i)
{
do_pg_array_element(*i, append, is_container<contained_type>());
if(i != last)
append << ", ";
}
append << "}";
}
template<typename container>
string to_pg_array_constr(const container& cnt)
{
stringstream result;
do_pg_array_element(cnt, result, true_type());
return result.str();
}
int main()
{
vector<string> a;
list<vector<int> > b;
for(int i = 0; i < 10; ++i)
{
a.push_back("Peter's vector");
vector<int> sss;
sss.push_back(0);
sss.push_back(1);
sss.push_back(2);
b.push_front(sss);
}
cout << "a: " << to_pg_array_constr(a) << endl << endl;
cout << "b: " << to_pg_array_constr(b) << endl << endl;
connection conn("dbname=postgres");
work test(conn, "test");
conn.prepare("unnest", "SELECT unnest($1::text[][])")("text[][]");
result r = test.prepared("unnest")(to_pg_array_constr(a)).exec();
for(result::const_iterator c = r.begin(); c != r.end(); ++c)
{
cout << c[0].as(string()) << endl;
}
return 0;
}
// c++ to_pg_array.cpp -L/usr/local/pgsql/lib -lpqxx -lpq
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
