Error concatenating component value in query

Asked

Viewed 132 times

1

As darlings in my C# Winforms project are being carried out through StringBuilder. It was suggested here in the O.R. query of Consulta.Append("Select * from...") for @"select * from..." for being somewhat redundant. Below is a fragment of the code after the change:

string Consulta = @"select nome as Nome,  
       endereco as Endereço,
       telefone as Telefone,
       dataCadastro as [Data de Cadastro]
    From clientes
       where dataCadastro='"+ Convert.toString(dePeriodo.Text) +"'
       and status<>0";
foreach (DataRow iRow in Dados.SQLData.dsData(Consulta).Tables[0].Rows)
  {
     //restante aqui
  }

It turns out that when using the reported method it is not possible to concatenate the component parameter DateEdit (dePeriodo) because the double quotes that are in ...dataCadastro='"+ dePeriodo..., which opens the concatenation, is actually closing the query. How should I proceed to concatenate the value of the component with a query without causing this error?

c# string query ado.net string-concatenation

  • Did any of the answers solve your question? Do you think you can accept one of them? Check out the [tour] how to do this, if you haven’t already. You would help the community by identifying what was the best solution for you. You can accept only one of them. But you can vote on any question or answer.

3 answers

4

Imagining you’re wearing one Command it is right to do this:

comando.CommandText = @"select nome as Nome,  
        endereco as Endereço,
        telefone as Telefone,
        dataCadastro as [Data de Cadastro]
    from clientes
    where dataCadastro = @Periodo and status<>'0'";
comando.Parameters.Add("@Periodo", OleDbType.VarChar, 10).Value = dePeriodo.Text;

I put in the Github for future reference.

I can’t guarantee it’s exactly this because I don’t have all the necessary information, but that’s about it.

Or could it be this:

comando.Parameters.Add(new SqlParameter("@Periodo", dePeriodo.Text));

If you do not do so you will have SQL injection security problem, a problem that should affect about 90% of sites because today most people do not learn before doing. Do not use interpolation of string as shown in the other answer. A more complete example in Any ASP.NET MVC solution for SQL injection?.

  • @Manieiro, whether or not to fill a DataSet ?

  • Any database query that has parameters.

0

Just take the simple quotes from the 0 (zero).

string Consulta = @"select nome as Nome,  
   endereco as Endereço,
   telefone as Telefone,
   dataCadastro as [Data de Cadastro]
From clientes
   where dataCadastro='"+ Convert.toString(dePeriodo.Text) +"'
   and status<> 0 ";

foreach (DataRow iRow in Dados.SQLData.dsData(Consulta).Tables[0].Rows)
{
   //restante aqui
}

  • Well noted Marcelo, but actually this was a copy error my... I simplified the query because the original has 78 lines

0

If you are using the core framework, the latest you can do so:

string Consulta = $@"select nome as Nome,  
           endereco as Endereço,
           telefone as Telefone,
           dataCadastro as [Data de Cadastro]
        From clientes
           where dataCadastro='{Convert.toString(dePeriodo.Text)}'
           and status<> 0 ";

        foreach (DataRow iRow in Dados.SQLData.dsData(Consulta).Tables[0].Rows)
        {
            //restante aqui
        }

The concatenation happens as the string.format.

The case that to avoid SQL Injection, one should do as Maniero said. It is the safest way to work.

Browser other questions tagged c# string query ado.net string-concatenation

You are not signed in. Login or sign up in order to post.