Boost logo

Boost :

Subject: Re: [boost] sqlpp11: SQL for C++
From: Gavin Lambert (gavinl_at_[hidden])
Date: 2013-11-13 01:28:25

On 13/11/2013 18:24, Quoth Roland Bock:
> On 2013-11-13 00:48, Gavin Lambert wrote:
>> Provided that you do value insertion via actual parameters (either
>> named or positional, depending on the target SQL dialect) and not via
>> directly inserting constants into the SQL query itself, it should be
>> fairly straightforward to extend to a dialect that requires explicit
>> preparing; you'd just need to provide a syntax that allows you to
>> cache a query object in a variable and then execute it multiple times
>> with different actual values. (Using parameters is also preferred
>> over direct insertion because it lessens the risk of script injection.)
> That should be relatively easy to do (and if you encapsulate the query
> generation in a parametrized function it is already there).

I'm not sure I'm reading that last part right, but what I am talking
about is very different from having a C++ function that accepts
parameters and internally generates and runs the query.

I haven't looked at your code enough to formulate a sensible example
using its syntax, but if I can offer an analogy using Boost.Format's
syntax (don't take it too literally):

boost::format GenerateQuery()
     return boost::format("select * from T where a=%1% and b=%2%");
     // not intended to be fully safe SQL, it's just for show
     // also I'm not implying you should use strings to construct

std::string IndirectCall(const boost::format& query, int a, const
std::string& b)
     return (query % a % b).str(); // imagine this actually runs SQL

std::string DirectCall(int a, const std::string& b)
     IndirectCall(GenerateQuery(), a, b);

The GenerateQuery method returns a "query object" that defines the
action to be done but not the specific parameter values on which it
acts. It is expensive to create (because this is where all the parsing
happens) but is (fairly) cheap to copy, and reusable without copying.
This is analogous to preparing an SQL query.

Outside code could run GenerateQuery once (perhaps at program startup),
cache the result somewhere, and then use it repeatedly via IndirectCall,
thereby only paying the parsing cost once. (Boost.Format allows the
same object to be reused once str() is invoked on it, or if explicitly
cleared; or you can copy it prior to adding actual values to the copy.)
  This is analogous to running a prepared query with different actual
parameters. (The db connector backend usually has specific separation
between the SQL statement and the parameters -- you wouldn't actually
insert them into the string the way that this example implies. This
also typically means you don't have to escape values to prevent
injection, as they're already known to be data.)

The DirectCall method is the least performant case; it explicitly
regenerates the query each time. This is analogous to running an
unprepared query *or* actually inserting the real values directly into
the SQL instead of using the db-connector's parameter mechanism. (But
this is the most common way that SQL gets used, because it's "easier".
I haven't looked too closely but I get the impression that this is the
only way that sqlpp11 works at present too.)

The key point being that for prepared statements to work (whether
explicit as in Oracle or automatic as in SQL Server), you need to be
able to define the SQL statement itself using only constants and
placeholders where variables go, but not provide the actual values of
the variables. Then when you actually go to execute the query you pass
the constant SQL statement along with the collection of actual values to
fill in the placeholders for this particular invocation. Different
database backends support different syntaxes for placeholders; most
support positional placeholders and a few offer named placeholders.

(In your case I would recommend using only named placeholders on the
user side, because it will be very unobvious to the user what the "real"
positional order in the underlying SQL query will be. When interfacing
to a connector for a DB that only supports positional placeholders you
would have to convert them appropriately.)

Boost list run by bdawes at, gregod at, cpdaniel at, john at