title: Dynamic WHERE injection via AddWithValue not working in MySQLData
body: <p>I'm trying to add a dynamic where based on API call values by adding it via <code>MySqlCommand.Parameters.AddWithValue</code> but those are ignored and just return the data without that filter.</p>
  <p>My query contains tag values like <code>WHERE TYPE &lt;&gt; 'PR' @data @negozi ...</code></p>
  <p>Then i populate tags with values like this:</p>
  <pre><code>Dim query As String = &quot;SELECT SUM(IF(TIPODOC_PA = 'SCONTRINO' OR TIPODOC_PA = 'FATTURA', (IMPORTOPAG_PA - RESTO_PA), 0)) AS TOTPAG, DESCRPAG_PA, RISCPAG_PA, TIMESTAMP(DATA_PA, ORA_PA) AS DATA FROM pagamenti WHERE TIPODOC_PA &lt;&gt; 'VE' AND DESCRPAG_PA &lt;&gt; 'PR' AND DESCRPAG_PA &lt;&gt; 'NR' AND DESCRPAG_PA &lt;&gt; 'FC' AND DATA_PA BETWEEN @start AND @end AND @negozi GROUP BY DESCRPAG_PA ORDER BY TIMESTAMP(DATA_PA, ORA_PA)&quot;
  
  Using cn As New MySqlConnection(DB.getConnectionString(database))
      cn.Open()
      Using cmd As New MySqlCommand(query, cn)
          If dataInizio.Equals(dataFine) And Not periodo.Equals(&quot;giorno&quot;) Then
              Select Case periodo
                  Case &quot;anno&quot;
                      cmd.Parameters.AddWithValue(&quot;@start&quot;, New Date(dataInizio.Year, 1, 1))
                      cmd.Parameters.AddWithValue(&quot;@end&quot;, New Date(dataFine.Year, 1, 1))
                  Case &quot;mese&quot;
                      cmd.Parameters.AddWithValue(&quot;@start&quot;, New Date(dataInizio.Year, dataInizio.Month, 1))
                      cmd.Parameters.AddWithValue(&quot;@end&quot;, New Date(dataFine.Year, dataFine.Month, Date.DaysInMonth(dataFine.Year, dataFine.Month)))
              End Select
          Else
              cmd.Parameters.AddWithValue(&quot;@start&quot;, dataInizio)
              cmd.Parameters.AddWithValue(&quot;@end&quot;, dataFine)
          End If
          cmd.Parameters.AddWithValue(&quot;@negozi&quot;, $&quot;{negozi}&quot;)
  
          Try
              Using reader As MySqlDataReader = cmd.ExecuteReader()
                  Return ConvertReader(reader)
              End Using
          Catch ex As Exception
              Return New List(Of Totale)
          End Try
      End Using
  </code></pre>
  <p>In debug the Else statment is executed so the full query should be like <code>WHERE TYPE &lt;&gt; 'PR' AND DATA_PA BETWEEN '2022-02-03' AND '2022-02-03' AND (NPV_PA, NCASSA_PA) IN ((0, 1)) ...</code></p>
  <p>But it seems that cmd for @data and @negozi is not even added..</p>
  <p>C# answers are welcome.</p>
  <p>Table schema:</p>
  <pre><code>DROP TABLE IF EXISTS `02878100342`.`pagamenti`;
  CREATE TABLE  `02878100342`.`pagamenti` (
    `NPV_PA` int(11) NOT NULL default '0',
    `NCASSA_PA` int(11) NOT NULL default '0',
    `TIPODOC_PA` varchar(250) NOT NULL default '',
    `TIPOPAG_PA` varchar(250) NOT NULL default '',
    `DESCRPAG_PA` varchar(250) NOT NULL default '',
    `RISCPAG_PA` int(1) default '0',
    `DATA_PA` date NOT NULL default '2001-01-01',
    `ORA_PA` time default '00:00:00',
    `AZZ_PA` varchar(10) NOT NULL default '0000',
    `NSC_PA` int(11) NOT NULL default '0',
    `ID_PA` int(30) NOT NULL default '0',
    `IMPORTODOC_PA` float(10,3) default '0.000',
    `IMPORTOPAG_PA` float(10,3) default '0.000',
    `RESTO_PA` float(10,3) default '0.000',
    `OPERATORE_PA` int(10) default '0',
    `CODICEBUONO_PA` varchar(250) default '',
    `IDTICKET_PA` int(11) default '0',
    `TIPOOPER_PA` varchar(250) default '',
    PRIMARY KEY  USING BTREE (`NPV_PA`,`NCASSA_PA`,`TIPODOC_PA`,`DATA_PA`,`AZZ_PA`,`NSC_PA`,`ID_PA`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  </code></pre>
  <p>EDIT:
  thanks to all comments i've changed the code as following, the decision of concatenating <code>negozi</code> inside query was taken as that data is dynamic and could contain LOT of combinations, that data is yet SQL formatted and after CLIENT send the filter i format it for SQL so it should't be exposed to SQL injections.</p>
  <pre><code>Public Shared Function TotalePagamenti(database As String, filter As ModelConfig.Config, Optional periodo As String = &quot;giorno&quot;) As List(Of Totale)
      Dim dataInizio As Date = Date.Parse(filter.data.inizio)
      Dim dataFine As Date = Date.Parse(filter.data.fine)
      Dim negozi As String = ModelConfig.QueryParametri(filter.config, &quot;PA&quot;)
  
      Dim query As String = $&quot;SELECT SUM(IF(TIPODOC_PA = 'SCONTRINO' OR TIPODOC_PA = 'FATTURA', (IMPORTOPAG_PA - RESTO_PA), 0)) AS TOTPAG, DESCRPAG_PA, RISCPAG_PA, TIMESTAMP(DATA_PA, ORA_PA) AS DATA FROM pagamenti WHERE TIPODOC_PA &lt;&gt; 'VE' AND DESCRPAG_PA &lt;&gt; 'PR' AND DESCRPAG_PA &lt;&gt; 'NR' AND DESCRPAG_PA &lt;&gt; 'FC' AND DATA_PA BETWEEN @start AND @end AND {negozi} GROUP BY DESCRPAG_PA ORDER BY TIMESTAMP(DATA_PA, ORA_PA)&quot;
  
      Using cn As New MySqlConnection(DB.getConnectionString(database))
          cn.Open()
          Using cmd As New MySqlCommand(query, cn)
              If dataInizio.Equals(dataFine) And Not periodo.Equals(&quot;giorno&quot;) Then
                  Select Case periodo
                      Case &quot;anno&quot;
                          cmd.Parameters.AddWithValue(&quot;@start&quot;, Format(New Date(dataInizio.Year, 1, 1), &quot;yyyy-MM-dd&quot;))
                          cmd.Parameters.AddWithValue(&quot;@end&quot;, Format(New Date(dataFine.Year, 1, 1), &quot;yyyy-MM-dd&quot;))
                      Case &quot;mese&quot;
                          cmd.Parameters.AddWithValue(&quot;@start&quot;, Format(New Date(dataInizio.Year, dataInizio.Month, 1), &quot;yyyy-MM-dd&quot;))
                          cmd.Parameters.AddWithValue(&quot;@end&quot;, Format(New Date(dataFine.Year, dataFine.Month, Date.DaysInMonth(dataFine.Year, dataFine.Month)), &quot;yyyy-MM-dd&quot;))
                  End Select
              Else
                  cmd.Parameters.AddWithValue(&quot;@start&quot;, Format(dataInizio, &quot;yyyy-MM-dd&quot;))
                  cmd.Parameters.AddWithValue(&quot;@end&quot;, Format(dataFine, &quot;yyyy-MM-dd&quot;))
              End If
  
              Try
                  Using reader As MySqlDataReader = cmd.ExecuteReader()
                      Return ConvertReader(reader)
                  End Using
              Catch ex As Exception
                  Return New List(Of Totale)
              End Try
          End Using
      End Using
  
  End Function
  </code></pre>