How to pass string to where clause - mySQL

Question

I hope someone could help.

From a C# app, I need to get some data from mySQL db. There is no procedure, so unfortunately I have to write the query inside the code...

I have a list of ids (guid), which I am sending through postman

{
   "meetingIds":[
      "9a4059c9-8c41-0afp-245e-9b9147bd5549",
      "b90oz505-9adb-ca9c-7791-5b9133bb7a1c",
      "9ghfc65b-1abb-88u6-8584-5e8p8af61050"
   ]
}

afer received into the app, I am adding the single quote to each of them

var singleQuote = "";
string stringOfIds = string.Join(", ", request.meetingIds.Select(x => singleQuote + x + singleQuote));

so I am getting a string which looks like this:

'9a4059c9-8c41-0afp-245e-9b9147bd5549', 'b90oz505-9adb-ca9c-7791-5b9133bb7a1c', '9ghfc65b-1abb-88u6-8584-5e8p8af61050'

and I am passing that to a query as string (meetingIds), but I am getting no results... if I pass just one id, it works fine.

This is the part of the query

MySqlCommand cmd = new MySqlCommand(
@"select     u.user_id          as userId,
             u.first_name       as firstName,
             u.last_name        as lastName,
             u.date_inserted    as dateModified, 
             u.campaign         as campaignId, 
             u.status           as status, 
             from meetings u
             where m.id in (@meetingIds);", conn);

             cmd.Parameters.AddWithValue("@meetingIds", meetingIds);

I know about the danger of SQL injection, but I dont see any other way to do this... and even this one is not working...

I am open for all suggestions, thank you.

You could use table-valued parameters (if MySQL supports these), but another approach would be to ensure each parses to a GUID before passing it to SQL (and call `.ToString()` on that GUID), which would protect against SQL injection. — ProgrammingLlama, Oct 12 '22 at 06:26
@ProgrammingLlama probably that's the best for mysql (not really sure). Obviously OP looked up regular SQL approach https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause and figured that it did not work either... — Alexei Levenkov, Oct 12 '22 at 06:27
@ProgrammingLlama, my first approach was exactly using the data table, but unfortunately mySQL dont support it... — sosNiLa, Oct 12 '22 at 06:31
I'm not familiar enough with MySQL to know if there's a better solution for it there. Hopefully someone who is more acquainted with it can answer. :) — ProgrammingLlama, Oct 12 '22 at 06:32

score 2 · Accepted Answer · answered Oct 12 '22 at 06:36

2

I think you run into the same issue as: Add List<int> to a mysql parameter. The answer using FIND_IN_SET function should solve your problem as well. Hope it helps.

answered Oct 12 '22 at 06:36

Can Bilgin

471
2
6

1

yes, the same problem, and I have managed to get the results, by constructing the query, my where looks like this now: where m.id in (" + meetingIds+ ");", conn); and by @ProgrammingLlama suggestion, I am checking does string parse to guid, so there is no fear about the sql injection :) thanks guys – sosNiLa Oct 12 '22 at 06:54

How to pass string to where clause - mySQL

1 Answers1