How to perform IN in sql query using pqxx in c++ for postgresql?

Question

How to perform IN in sql query using pqxx in c++ for postgresql ? I have vector<long> of ids and I need to update every row in table students ( to set faculty_id to some new value). I want to avoid loop, new value (faculty_id) I get when I insert faculty with prepared INSERT statement. Is possible at all to pass so iterable structure or create prepared IN query using pqxx ?

void prepareChangeFaculty(connection_base &c){
    const std::string sql =
      "UPDATE students SET faculty_id=$2 WHERE id IN $1"; // here is a problem
    c.prepare("change_faculty", sql);
}

$1 I have like vector of id of rows which I need to update

@Alexandros I have added some code with problematic part – Damir May 26 '14 at 17:43 — Damir, May 26 '14 at 17:43

score 0 · Answer 1 · edited May 23 '17 at 12:11

0

Why not something like that (How to concatenate a std::string and an int?) in C++11

string a="";
for (int k=0;k<myVector.size();k++){
    a += string(myVector[k]);
    if (k<myVector.size()-1){
        a += ",";
    }
}

std::string sql = "UPDATE students SET faculty_id=$2 WHERE id IN (" + a + ")";

edited May 23 '17 at 12:11

Community

1
1

answered May 26 '14 at 17:52

Alexandros

2,160
4
27
52

3

Q: "why not something like that"? A: "because SQL injection"! (_Yes_, I know there will only be integers in myVector; _at least, on this development iteration_...) – Massa May 26 '14 at 18:35
@Massa The OP does not say anywhere that this app is going to used by other users except him, so what SQL injection you are talking about, when it is his DB server and his app? – Alexandros May 26 '14 at 18:39
2

I don't do SQL-injections (if I can avoid them) by principle. As I said, I _know_ `myVectors` is an integer vector (difficult to inject poisonous data on those) _for now_. The guy who will inherit the code can change it to a `string` vector, or `boost::variant` record, and the next guy in line with the same code five years from now makes the code into a library and link it to a web service and there is no budget to do a proper code review and the product has to be lauched ASAP and **BAM!**.... – Massa May 26 '14 at 18:44
Then lets write all code in a way that makes it impossible for stupid monkeys to BAM in all possible futures ... lol – knivil May 27 '14 at 12:20

score 0 · Answer 2 · answered Aug 05 '20 at 09:12

I understand the concerns of @Massa, however I could not find a different solution than the @Alexandros one.

So, a bit improvement to this solution can be the use of std::copy

std::stringstream params;

std::copy(
    myVector.begin(), myVector.end(),
    std::ostream_iterator<std::string>(params, ","));

std::string sql = "UPDATE students SET faculty_id=$2 WHERE id IN ('"
    + params.str() + "')";

How to perform IN in sql query using pqxx in c++ for postgresql?

2 Answers2