How do I bind output from a SOCI query?

Question

The following code :generates error messages, which follow:

bool UACmUsers::GetUser(int userid) {
    soci::session sql(dbU::Connection());
    sql << "SELECT userid, username, password FROM uac_users WHERE userid = \":user\"",
        soci::use(userid),
        soci::into(iDuserid,   indUserid),
        soci::into(sDusername, indUsername), // This is line 61.
        soci::into(sDpassword, indPassword);

    return true;
}

dbU::Connection() is a function generating the connection string.

iDuserid, sDusername, and sDpassword are private members of UACmUsers, of types int and std::string.

indUserid, indUsername, andindPassword are private members of UACmUsers, of type soci::indicator.

There is a second, overloaded, function searching for the username. If I comment out this function so only the second one remains, the complier points to the same point in the second function.

bool UACmUsers::GetUser(const wxString& username);

I have tried removing two of the indicators, this does not solve the problem.

The errors:

E:\cpp\lib\soci\soci-4.0.0\include\soci/exchange-traits.h(41): error C2065: 'x_type': undeclared identifier
E:\cpp\lib\soci\soci-4.0.0\include\soci/bind-values.h(207): note: see reference to class template instantiation 'soci::details::exchange_traits<T>' being compiled
        with
        [
            T=wxString
        ]
E:\cpp\lib\soci\soci-4.0.0\include\soci/bind-values.h(143): note: see reference to function template instantiation 'void soci::details::into_type_vector::exchange_<wxString,soci::indicator>(const soci::details::into_container<wxString,soci::indicator> &,...)' being compiled
E:\cpp\lib\soci\soci-4.0.0\include\soci/statement.h(50): note: see reference to function template instantiation 'void soci::details::into_type_vector::exchange<wxString,soci::indicator>(const soci::details::into_container<wxString,soci::indicator> &)' being compiled
E:\cpp\lib\soci\soci-4.0.0\include\soci/statement.h(199): note: see reference to function template instantiation 'void soci::details::statement_impl::exchange<wxString,soci::indicator>(const soci::details::into_container<wxString,soci::indicator> &)' being compiled
E:\cpp\lib\soci\soci-4.0.0\include\soci/ref-counted-statement.h(92): note: see reference to function template instantiation 'void soci::statement::exchange<wxString,soci::indicator>(const soci::details::into_container<wxString,soci::indicator> &)' being compiled
E:\cpp\lib\soci\soci-4.0.0\include\soci/once-temp-type.h(49): note: see reference to function template instantiation 'void soci::details::ref_counted_statement::exchange<const soci::details::into_container<wxString,soci::indicator>>(T &)' being compiled
        with
        [
            T=const soci::details::into_container<wxString,soci::indicator>
        ]
E:\cpp\dev\Manager\src\UAC\models\UACmUsers.cpp(61): note: see reference to function template instantiation 'soci::details::once_temp_type &soci::details::once_temp_type::operator ,<wxString,soci::indicator>(const soci::details::into_container<wxString,soci::indicator> &)' being compiled

Can someone help me get SOCI to behave?

---

This is part of a project on GitHub. Links to the header and source files containing the above code.

Answer 1

Your strings aren't standard strings. wxString likely has no conversion support in SOCI.

Either do the conversions, or extend the type conversions. Perhaps not the most efficient but works:

namespace soci {
    template <> struct type_conversion<wxString> {
        typedef std::string base_type;

        static void from_base(base_type const& s, indicator ind, wxString& ws) {
            if (ind == i_null)
                ws.clear();
            else
                ws = s;
        }

        static void to_base(const wxString& ws, base_type& s, indicator& ind) {
            s = ws.ToStdString();
            ind = i_ok;
        }
    };
} // namespace soci

Full Demo

#include <iomanip>
#include <iostream>
#include <soci/boost-tuple.h>
#include <soci/mysql/soci-mysql.h>
#include <soci/soci.h>
#include <soci/use.h>
#include <wx/string.h>

namespace soci {
    template <> struct type_conversion<wxString> {
        typedef std::string base_type;

        static void from_base(base_type const& s, indicator ind, wxString& ws) {
            if (ind == i_null)
                ws.clear();
            else
                ws = s;
        }

        static void to_base(const wxString& ws, base_type& s, indicator& ind) {
            s = ws.ToStdString();
            ind = i_ok;
        }
    };
} // namespace soci

using namespace std::string_literals;

namespace dbU {
    static soci::connection_parameters
        parms("mysql://db=test user=root password='root'");

    auto Connection() { return soci::session(parms); }

    void setup() {
        try {
            dbU::Connection()
                .create_table("uac_users")
                .column("userid", soci::data_type::dt_integer)
                .primary_key("userid", "userid")
                .column("username", soci::data_type::dt_string)
                .column("password", soci::data_type::dt_string);
        } catch (soci::mysql_soci_error const& e) {
            std::cout << "Table already exists\n";
        }

        {
            using Rec = boost::tuple<int, std::string, std::string>;
            Rec rec;

            auto db = dbU::Connection();
            soci::statement st =
                (db.prepare << "insert into uac_users values (:id, :name, :pwd)",
                 soci::use(rec));

            for (auto vals : { Rec
                    { 11, "one"s, "bar"s },
                    { 12, "two"s, "gjb"s },
                    { 13, "three"s, "guerr"s },
                    { 14, "four"s, "sbhe"s },
                    { 15, "five"s, "svir"s }, })
            {
                try {
                    rec = vals;
                    st.execute(true);
                } catch (soci::mysql_soci_error const& e) {
                    std::cout << e.what() << "\n";
                }
            }
        }
    }
} // namespace dbU

struct X {
    int iDuserid;
    wxString sDusername, sDpassword;
    soci::indicator indUserid, indUsername, indPassword;

    bool foo(int userid) {
        soci::session sql(dbU::Connection());

        sql << R"(SELECT userid, username, password FROM uac_users WHERE userid = ":user")",
            soci::use(userid), soci::into(iDuserid, indUserid),
            soci::into(sDusername, indUsername), // This is line 27
            soci::into(sDpassword, indPassword);

        std::cout << "last: " << sql.get_last_query() << "\n";

        return true;
    }
};

int main() {
    dbU::setup();

    X x;
    x.foo(12);

    std::cout << x.iDuserid << " " << std::quoted(x.sDusername.ToStdString())
              << " " << std::quoted(x.sDpassword.ToStdString()) << "\n";
}

Prints

last: SELECT userid, username, password FROM uac_users WHERE userid = ":user"
12 "two" "gjb"

Or on a subsequent run:

Table already exists                                
Duplicate entry '11' for key 'PRIMARY' while executing "insert into uac_users values (:id, :name, :pwd)" with :id=11, :name="one", :pwd="bar".
Duplicate entry '12' for key 'PRIMARY' while executing "insert into uac_users values (:id, :name, :pwd)" with :id=12, :name="two", :pwd="gjb".
Duplicate entry '13' for key 'PRIMARY' while executing "insert into uac_users values (:id, :name, :pwd)" with :id=13, :name="three", :pwd="guerr".
Duplicate entry '14' for key 'PRIMARY' while executing "insert into uac_users values (:id, :name, :pwd)" with :id=14, :name="four", :pwd="sbhe".               
Duplicate entry '15' for key 'PRIMARY' while executing "insert into uac_users values (:id, :name, :pwd)" with :id=15, :name="five", :pwd="svir".               
last: SELECT userid, username, password FROM uac_users WHERE userid = ":user"                                                       
12 "two" "gjb"                                                                                                                       

And the table contains

NOTES

In the sample there appears to be a memory leak when the insert statements throw. I guess this need to be reported upstream.

THINK TWICE before storing passwords in a database. Proper security rules of thumb:

• Never store passwords. Period.
• Always store hashes
• Do NOT use fast hashes or non-cryptographically strong hashes (no MD5 or SHA bullshit, use PKDBF2, scrypt or similar)
• Always use a per-user salt