locked
MySQL Parameters.AddWithValue fails/not working in c# RRS feed

  • Question

  • User1036972001 posted

    Hi all,

    I'm trying to select values to a MySQL table.

    I use the AddWithValue method, but it doesn't substitute what I have in the command string.

    Here is some sample code that fails and the return is empty.

    private DataSet RetrieveProducts()
    {
       DataTable dt1 = new DataTable();
       DataTable dtProducts = new DataTable();
       DataSet dsProducts = new DataSet();
    
        var paramValuenamens = string.Join(", ", Masterpage.Container.TheObjectPropertyNameNs);
        var strEmail = Masterpage.Container.TheObjectPropertyNameEmail.ToString();
    
        string sql = @String.Format(" SELECT * FROM ");
        sql += String.Format(" doTable ");
        sql += String.Format(" WHERE 1 ");
    
        if (Masterpage.Container.theObjectPropertyAut == 1)
        {
            sql += String.Format(" AND doTableEmail = ? ");
        }
    
        sql += String.Format(" AND doTableNameNs IN ({0}) ", paramValuenamens.ToString());
    
    
        using (MySqlConnection myConnectionString =
          new MySqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
        {
            using (MySqlCommand cmd =
                new MySqlCommand(sql, myConnectionString))
            {
                cmd.Parameters.AddWithValue("param1", strEmail.ToString());
    
                foreach (var param in paramValuenamens)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("param2", param.ToString());
                }
    
                MySqlDataAdapter adapter =
                    new MySqlDataAdapter(cmd);
    
                adapter.Fill(dsProducts);
    
                if (dsProducts.Tables.Count > 0)
                {
                    dt1 = dsProducts.Tables[0];
                }
    
                dtProducts = dt1;
            }
        }
    
        return dsProducts;
    }

    But if change my code from

    if (Masterpage.Container.theObjectPropertyAut == 1)
    {
        sql += String.Format(" AND doTableEmail = ? ");
    }

    To

    if (Masterpage.Container.theObjectPropertyAut == 1)
    {
        sql += String.Format(" AND doTableEmail = '" + strEmail.ToString() + "' ");
    }

    The return is correct...

    I have tried this suggestion without success

    How to do resolve this?

    Can you help me, please?

    Thank you in advance for any help

    Saturday, September 12, 2020 6:29 PM

Answers

  • User314352500 posted

    Hi Kooper,

    You are trying to use parameters without setting the proper placeholder in the query text. So change the condition to....

    if (Masterpage.Container.theObjectPropertyAut == 1)
    {
        sql += " AND doTableEmail = @email ");
    }
    
    ....

    Now add the parameter using the same name used for the placeholder

    cmd.Parameters.AddWithValue("@email", strEmail);

    Then you have a useless loop that clear the parameter collection at each loop.
    You don't need it because you have already added the values directly through the string format above.

    // foreach (var param in paramValuenamens)
    // {
    //     cmd.Parameters.Clear();
    //     cmd.Parameters.AddWithValue("param2", param.ToString());
    // }

    Now this should work, but of course, this is not a complete parameterized query because the IN clause for the doTableNameNs still uses a string concatenation (Format is still a concatenation and doesn't keep you safe from sql injection). This is acceptable only if you are absolutely sure that the value used don't come from user input or other media that you cannot control. Otherwise you need to create a collection of parameters and strings placeholders to insert in the IN statement for doTableNameNs

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 13, 2020 7:50 PM