Re: [Cppcms-users] C++DB question regarding "IN"

[Jon F. <jon...@jf...>]

Thanks Martin!

But I like your first solution better. It too uses C++DB's built in 
escaping to prevent SQL injections, but it would be much faster than 
sending multiple queries to the server. Currently in the one piece of code 
I'm doing this (new to C++DB & C++CMS) I still do as I stated in my first 
post. I haven't changed it because I'm performing more rigorous testing 
than simply escaping the incoming data.

This method you mention could be useful if someone needs to validate each 
and every item and return the individual statuses to the browser.

Thanks again!
Jon


Martin Bürgmann wrote:
> Hi Jon!
>
> I have another way of doing things.
>
> I suspect this will be slightly less performant but removes the dynamic
> string building.
>
> Just prepare the statement, and then loop over the IDs and reexec the
> same statement for each ID.
>
>      cppdb::statement stmt = db_handle << "UPDATE my_table "
>          "SET field_1 = \"my val\" WHERE id = ?";
>
>      for (int id : <the id vector or whatever>) {
>          stmt.reset();
>          stmt << id << cppdb::exec;
>      }
>
> This will remove additional burden to conciously NOT introduce
> injection vulnerabilities.
>
> Greetings,
> Martin
>
> Am Montag, den 13.07.2020, 16:30 -0700 schrieb Jon Foster:
>> Hi Martin!
>>
>> Thanks for that. I think I like your idea better than mine. I'll have
>> to
>> noodle this some. Your assumption is correct. Typical examples would
>> be a
>> form that lists a series of DB records of some kind, with check boxes
>> along
>> one side. The user checks the boxes and then selects a bulk action
>> (like
>> delete, publish, unpublish, ...) and then clicks the "GO" button.
>> Although
>> I was asking in general terms since an "IN" operator has other uses.
>>
>> THX - Jon
>>
>> Martin Bürgmann wrote:
>>> Hi Jon,
>>>
>>> I don't fully get what you are doing but I'm assuming you want to
>>> update rows which the user selected previously on the HTML form.
>>>
>>> Indeed this does sound like a bit of a pain. And I don't know of
>>> any
>>> cppdb feature that could help here.
>>>
>>> I'd suggest using prepared statements like this:
>>>
>>> Start with a std::stringstream with your query and one placeholder
>>>
>>>       UPDATE my_table SET field_1="my val" WHERE id IN (?
>>>
>>> Next, since you know how many rows N the user selected, append N-1
>>> times another placeholder
>>>
>>>       ,?
>>>
>>> So you now have your query with N placeholders
>>>
>>>       UPDATE my_table SET field_1="my val" WHERE id IN (?,?,?,?
>>>
>>> And then finish it with a paren and semicolon and make a prepared
>>> statement from it
>>>
>>>       cppdb::statement stmt = session_handle << strsteam.str();
>>>
>>> Now put all the row IDs in
>>>
>>>       for (int rid : vector_of_row_ids)
>>>           stmt << rid;
>>>
>>> And finally execute it
>>>
>>>       stmt.exec();
>>>
>>> Hope I helped at least a little!
>>>
>>> Martin
>>>
>>> Am Freitag, den 10.07.2020, 09:48 -0700 schrieb Jon Foster:
>>>> I've skimmed over the C++DB docs a couple of times and I don't
>>>> see
>>>> anything
>>>> about the best way to build queries using the "in" operator. My
>>>> use
>>>> case is
>>>> doing a bulk "UPDATE" command using "in" to target the set of
>>>> records
>>>> I
>>>> want by a list of integer IDs.  Example query:
>>>>
>>>>       UPDATE my_table SET field_1="my val" WHERE id IN (1,3,9);
>>>>
>>>> Right now I'm scrubbing strings coming back from an HTML form
>>>> (via
>>>> C++CMS)
>>>> and then building them direct into query string. Seems like C++DB
>>>> probably
>>>> already has better way. It seems to be well thought out.
>>>>
>>>> TIA - Jon
>>>>
>>> _______________________________________________
>>> Cppcms-users mailing list
>>> Cpp...@li...
>>> https://lists.sourceforge.net/lists/listinfo/cppcms-users
>> -- 
>> Sent from my Devuan Linux workstation -- https://devuan.org/
>> "Init Freedom", Yeah!
>>
>> Jon Foster
>> JF Possibilities, Inc.
>> jo...@jf...
>> 541-410-2760
>> Making computers work for you!
>>
>>
>
>
> _______________________________________________
> Cppcms-users mailing list
> Cpp...@li...
> https://lists.sourceforge.net/lists/listinfo/cppcms-users

-- 
Sent from my Devuan Linux workstation -- https://devuan.org/
"Init Freedom", Yeah!

Jon Foster
JF Possibilities, Inc.
jo...@jf...