How to prevent MySql Injection in dynamic where?

I have an API in my C# WEBApi project which return some items from my MySQL DB.

The data to return has a visibility set in a table and the params set in the API call says which kind of data should be shown, like if i’m in TAKEAWAY route of my website i send to API “TAKEAWAY” and i should SELECT data only where flag TAKEAWAY is on.

So i have my method where i get the data but i think it’s suggested to MySQL injection as of string concat in the query, so based on type i’m setting my where like this:

        switch (type)
        {
            case "TAKEAWAY":
                VISIBILITY = "vis.TAKEAWAY_VIS = 'SI' ";
                break;
            case "ASPORTO":
                VISIBILITY = "vis.ASPORTO_VIS = 'SI' ";
                break;
            case "ECOM":
                VISIBILITY = "vis.ECOMMERCE_VIS = 'SI' ";
                break;
        }

And setting it in query like this:

            var query_plu = @"SELECT 
    ID_MENU_PRP,
    ID_PLUREP,
    CODICE_PRP,
    des.ESTESA_DES,
    UM_PRP
FROM
    vo_plurep plu
        INNER JOIN
    vo_visibility vis ON plu.ID_PLUREP = vis.ID_PLUREP_VIS
WHERE " + VISIBILITY + @"
        AND plu.ATTIVO_PRP = 'True'
ORDER BY MENU_PRP ASC, des.ORDER_DES ASC;";

And then i execute that query as the following:

MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(query_plu, connection);

But how can i prevent to concat the string in the query for dynamic where?

Answer

Your code looks safe. Although you are dynamically generating part of the query string, the switch statement on application side ensures that no malicious value can go through.

You could, however, move the logic to the query itself, which would avoid the neeed to dynamically build the query.

SELECT 
    ID_MENU_PRP,
    ID_PLUREP,
    CODICE_PRP,
    des.ESTESA_DES,
    UM_PRP
FROM vo_plurep plu
INNER JOIN vo_visibility vis ON plu.ID_PLUREP = vis.ID_PLUREP_VIS
WHERE 
    plu.ATTIVO_PRP = 'True'
    AND (
           (@type = 'TAKEAWAY' AND vis.TAKEAWAY_VIS = 'SI')
        OR (@type = 'ASPORTO'  AND vis.ASPORTO_VIS  = 'SI')
        OR (@type = 'ECOM'     AND vis.ECOM_VIS     = 'SI')
    )
ORDER BY MENU_PRP ASC, des.ORDER_DES ASC

@type represents a query parameter, that you can pass to the query from your application.