What is the best way to set up a query?

Question

What is the best way to set up a query?

Navigation

#1 by (7 votes)

5

What is the best way to set up a query in C #? Is it advisable to use concatenation with the + operator? For example:

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

Here is a% w of% that 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 contains many rows, it's the right way to use the query operator to break the query into several parts concatenating, I have some doubts about it.

sql c# .net query ado.net

asked by anonymous 18.12.2015 / 00:24

1 answer

Aligns navbar bootstrap text vertically What are the advantages of using directives and macros?

score 7 · Accepted Answer

Definitely not. At least not this way. You're using ADO.Net, right? Then mount the query with the existing component to do this build. In this case, 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 assembling the basic text of the fixed part is not a big problem. Of course, if you have too much concatenation it's best to use a StringBuilder to avoid large copies of data from one string to another , already that this type 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

What you can not do is concatenate the variable part, because the SQL Injection can occur there. You need to leave the insertion of the part coming externally to a method that knows how to deal with this type of problem.

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

This may not solve all security issues, but it is already a breakthrough.