What is the advisable way to mount a query?

Question

What is the advisable way to mount a query in C#? It is advisable to use concatenation with the operator +? For example:

query += " WHERE nome LIKE '%" + nome + "%'"; 

Here follows a query I set as an example to illustrate the situation:

string query = "SELECT nome, idade FROM Pessoas ";

if (nome != "")
   query += " WHERE nome LIKE '%" + nome + "%'";

query += " ORDER BY idade";

I know I can use parameters too, however, and if my query contain many lines, it is the right way to use the operator + to break the query in several parts concatenating, I have some doubts about.

Answer 1

Definitely not. At least not this way. You’re using ADO.NET, right? Then assemble the query with the existing component to make this construction. In the case is the SQLCommand. You pass the parameters through it. Example:

using (var connection = new SqlConnection(connectionString)) {
    var query = "SELECT nome, idade FROM Pessoas ";
    if (nome != "") query += " WHERE nome LIKE '%@Nome%'";
    query += " ORDER BY idade"; //deixei mas poderia otimizar isto
    var command = new SqlCommand(query, connection);
    command.Parameters.Add("@Nome", SqlDbType.NVarChar);
    command.Parameters["@Nome"].Value = nome;
    try {
        connection.Open();
        int rowsAffected = command.ExecuteNonQuery();
    }
    catch (SQLException ex) {
        Console.WriteLine(ex.Message); //só exemplo, deveria fazer algo mais
    }
}

Note that mounting the basic text of the fixed part is not a big problem. Of course if you have too much concatenation it is better to use a StringBuilder for avoid large copies of data from a string to another, since this guy is immutable and can be very large.

var query = new StringBuilder("SELECT nome, idade FROM Pessoas ");
if (nome != "") query.Append(" WHERE nome LIKE '%@Nome%'");
query.Append(" ORDER BY idade"); //deixei mas poderia otimizar isto

I put in the Github for future reference.

What you can’t do is concatenate the variable part, because then the SQL Injection. Need to leave the insertion of the part that comes externally to a method that knows how to deal with this type of problem.

Note that you have to identify in the query what is the parameter and then send it, all by class SQLCommand.

This may not solve all the security problems, but it’s a breakthrough.