MySQL Parameters.AddWithValue fails/not working in c#

Question

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

Ehm, no parameters don't substitute anything in your query text. They are sent to the database engine together with the command text where you put placeholders in the form of _@paramName_ etc. — Steve, Sep 12 '20 at 18:20
Also for an IN clause you can't give just one parameter for all the values that you want to check. You need a parameter (with a different name) for each value that you want to include in the IN clause. — Steve, Sep 12 '20 at 18:22
@Steve I have tried this https://mysqlconnector.net/overview/using-addwithvalue/ without success — , Sep 12 '20 at 18:27
as written in the dupolicate you need to add for every entry in the IN clause a parameter. — nbk, Sep 12 '20 at 18:34
sorry this is the correct answer https://stackoverflow.com/a/337792/5193536 — nbk, Sep 12 '20 at 18:41

score 0 · Accepted Answer · answered Sep 12 '20 at 18:33

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

MySQL Parameters.AddWithValue fails/not working in c#

1 Answers1