Any ASP.NET MVC solution for SQL injection?

Question

I have in the application a code that validates the data entry, adjusting according to what is necessary, this would be considered a good or a bad option?

 public static string ValidaDados(string str)
 {
           //Função simples para evitar ataques de injeção SQL
           if (str == string.Empty || str == "")
               return str;

           string sValue = str; 

           //Valores a serem substituidos
           sValue = sValue.Replace("'", "''");
           sValue = sValue.Replace("--", " ");
           sValue = sValue.Replace("/*", " ");
           sValue = sValue.Replace("*/", " ");
           sValue = sValue.Replace(" or ", "");
           sValue = sValue.Replace(" and ", "");
           sValue = sValue.Replace("update", "");
           sValue = sValue.Replace("-shutdown", "");
           sValue = sValue.Replace("--", "");
           sValue = sValue.Replace("'or'1'='1'", "");
           sValue = sValue.Replace("insert", "");
           sValue = sValue.Replace("drop", "");
           sValue = sValue.Replace("delete", "");
           sValue = sValue.Replace("xp_", "");
           sValue = sValue.Replace("sp_", "");
           sValue = sValue.Replace("select", "");
           sValue = sValue.Replace("1 union select", "");

           //Retorna o valor com as devidas alterações
           return sValue;

 }

Example of use:

        var tbuscar = new UsuarioAplicacao();
        var retorno = tbuscar.ListarPorLoginSenha(ValidaDados(tabela.LOGIN), ValidaDados(tabela.SENHA));

Example of how it is today:

public TB_USUARIO ListarPorLoginSenha(string login, string senha)
{
    var strQuery = "";
    strQuery += " select  ";
    strQuery += "  b.DESCRICAO as PERFIL,  ";
    strQuery += "  b.ADMINISTRADOR as ADMINISTRADOR,  ";
    strQuery += "  c.DATA_FIM as DATAFINALASSINATURA, ";
    strQuery += "  c.SITUACAOASSINATURA,            ";
    strQuery += "  a.* ";
    strQuery += "  from TB_USUARIO a ";
    strQuery += "  inner join TB_PERFIL_ACESSO b on a.IDPERFIL = b.IDPERFIL ";
    strQuery += "  left join TB_ASSINATURA c on c.IDUSUARIO = a.IDUSUARIO ";
    strQuery += string.Format("  where a.login = '{0}' and a.senha = '{1}' ", login, senha);
    strQuery += "  and a.USUARIOATIVO = 'S' and a.USUARIOEXCLUIDO = 'N' ";
    strQuery += "  ORDER BY a.IDUSUARIO";

    using (contexto = new Contexto())
    {
        var retornoDataReader = contexto.ExecutaComandoComRetorno(strQuery);
        return TransformaReaderEmListaObjetos(retornoDataReader).FirstOrDefault();
    }

}

Answer 1

You need to use SQLCommand. Thus their query will be mounted by ADO.NET reliably. If you try to do the cleaning, you will certainly make a mistake, out of the huge work that it gives.

This class has a way to assemble texts with internal "variables" where the parameterized data will be placed cleanly. You at all times should use this class or any other that does the same (depends on the technology used).

Let’s take the example of the documentation:

private static void UpdateDemographics(int customerID, string demoXml, string connectionString) {
    using (var connection = new SqlConnection(connectionString)) {
        var command = new SqlCommand("UPDATE Sales.Store SET Demographics = @demographics WHERE CustomerID = @ID;", connection);
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;

        command.Parameters.AddWithValue("@demographics", demoXml);

        try {
            connection.Open();
            WriteLine($"RowsAffected: {command.ExecuteNonQuery()}");
        } catch (Exception ex) { //só para facilitar, normalmente é se usa assim
            WriteLine(ex.Message);
        }
    }
}

I put in the Github for future reference.

These "@" are the parameters of query. To pass the value to the query you use Parameters.Add() or its variations as demonstrated in the above code.

In your code after editing the question would be something like this:

public TB_USUARIO ListarPorLoginSenha(string login, string senha) { //esse retorno não parece certo
    var strQuery = @"select b.DESCRICAO as PERFIL, b.ADMINISTRADOR as ADMINISTRADOR, c.DATA_FIM as DATAFINALASSINATURA, c.SITUACAOASSINATURA, a.*
                      from TB_USUARIO a
                      inner join TB_PERFIL_ACESSO b on a.IDPERFIL = b.IDPERFIL
                      left join TB_ASSINATURA c on c.IDUSUARIO = a.IDUSUARIO
                      where a.login = @Login and a.senha = @Senha and a.USUARIOATIVO = 'S' and a.USUARIOEXCLUIDO = 'N'
                      order by a.IDUSUARIO";
    using (var conn = new SqlConnection(/* coloca aqui como pega a string */)) {
        var cmd = new SqlCommand(strQuery, conn);
        cmd.Parameters.Add(new SqlParameter("@Login", login));
        cmd.Parameters.Add(new SqlParameter("@Senha", senha));
        //a partir daqui eu não sei o que esse código faz, teria que adaptá-lo para usar o SQLCommand ou fazer de outra forma
        using (var contexto = new Contexto()) {
            return TransformaReaderEmListaObjetos(contexto.ExecutaComandoComRetorno(cmd)).FirstOrDefault();
        }
    }
}

Answer 2

Use Sqlcommands to enter the data, it already does this cleaning for you. Cleaning up the way you were suggesting would remove the words from any text, even if it wasn’t an attempt at SQL Injection.