How to provide the values corresponding to the placeholders in soci?

Question

I'm using soci 3.2.2. I'm looking for a way to provide multiple placeholders and corresponding values.

std::vector<std::string> vs;
vs.push_back("key1");
vs.push_back("key2");
sql << "select * from mytable as t where t.field1 = :f1 and t.field2 = :f2", use(vs[0]), use(vs[1]);

Let's say my table has many columns. For example field1, field2, ... The placeholders :f1 and :f2 is corresponding to filed1 and field2. The number of placeholders changes dynamically. So I create query string that contains placeholders dynamically. It is a simple string manipulation. So far, so good. However, I couldn't find a way to provide multiple values that is corresponding to placeholders. use(vs[0]), use(vs[1]), ... are not string but C++ code. So I can't generate it on run time.

I found a way to solve it but it's not elegant. The way is that giving up to use the function use() and insert the actual value such as "key1" directly using string manipulation. It's not safe. I need to implement to avoid SQL injection. It is achieved by use() function.

I'm looking for a better way.

Updated

Solution1 use Core interface

Thanks to the following comments: https://github.com/SOCI/soci/issues/354#issuecomment-115658512 https://github.com/SOCI/soci/issues/354#issuecomment-115662758

the problem has been solved using 'Core' interface.

http://soci.sourceforge.net/doc/3.2/interfaces.html

Here is the code using 'Core' interface:

    session sql(sqlite3, "./test");

    std::vector<std::string> qs { "v1", "v2", "v3" }; // determined on run time

    int count;

    // Create query string dynamically
    std::stringstream ss;
    ss << "select count(*) from mytable as t where t.field1 = :f1";
    for (std::size_t i = 1; i < qs.size(); ++i) {
        ss << " and t.field" << i+1 << " = :f" << i+1;
    }

    // Give the values corresponding to the placeholders in the query string
    statement st(sql);
    for (auto const& e : qs) {
        st.exchange(use(e));
    }
    st.exchange(into(count));
    st.alloc();
    st.prepare(ss.str());
    st.define_and_bind();
    st.execute(true);

    std::cout << count << std::endl;

Solution2 define custom mapping

std::vector is reserved by the soci library. I need to define teh different type. MyVectorOfStrings is that. Then define the custom conversion using type_conversion class template specialization.

#include <soci.h>
#include <sqlite3/soci-sqlite3.h>
#include <iostream>

using namespace soci;

struct MyVectorOfStrings : public std::vector<std::string> {
    using std::vector<std::string>::vector;
};

namespace soci
{
    template<>
    struct type_conversion<MyVectorOfStrings>
    {
        typedef values base_type;

        static void from_base(values const& v, indicator /* ind */, MyVectorOfStrings &p)
        {}

        static void to_base(const MyVectorOfStrings& p, values& v, indicator& ind) {
            for (auto s : p) v << s;
            ind = i_ok;
        }
    };
}

int main()
{
    try {
        session sql(sqlite3, "./test");

        MyVectorOfStrings qs { "v1", "v2", "v3" }; // determined on run time

        int count;

        sql << "select count(*) from mytable as t where t.field1 = :f1 and t.field2 = :f2 and t.field3 = :f3", use(qs), into(count);

        std::cout << count << std::endl;
    }
    catch (std::exception const &e) {
        std::cerr << "Error: " << e.what() << '\n';
    }
}

Answer 1

(As you have also asked this question on SOCI@GitHub, I copied my answer from there).

AFAIU, you want to pass vector<string> into query for, let's call it, vertical or column-wise expansion. AFAICT, it is not possible, vector<T> can be used with, again, horizontal or row-wise, expansion as a row data carrier.

Typically, the protocol is that number of placeholders must match number of use occurrences. User-defined data and ORM is an exception, where, N placeholders match 1 use occurrence.

You may try ORM with generated placeholders

namespace soci
{
    template<>
    struct type_conversion<MyVectorOfStrings>
    {
        typedef values base_type;

        static void from_base(values const & v, indicator /* ind */, MyVectorOfStrings & p)
        { ... }

        static void to_base(const MyVectorOfStrings & p, values & v, indicator & ind)
        {
            int i = 0;
            for (auto s : p)
            { 
                // generate placeholders from V0...Vn where n is size of p
                v.set("V" + std::to_string(i);, s);
                i++;
            }
            ind = i_ok;
        }
    };
}

Then try something along these lines:

MyVectorOfStrings p = ...;
std::string query = "select * from mytable as t where ";
int i = 0;
for (auto s : p)
{ 
    if (i > 0) query += " and ";
    std::string si = std::to_string(i);
    query += "t.field" + si + "=:f" + si;
}
sql << query, use(p);

TBH, I have never tried to run it, so no idea if it would even work :-)

Answer 2

this is simple example:

std::vector<std::string> ids;
soci::session s;

auto p = (s.prepare << "select id from mytable as t where false ");

for (auto & i : ids)
{ 
    p << " or id = :v", use(i);
}

soci::statement stmt{p};

stmt.execute();