Any ASP.NET MVC solution for SQL injection?

1

I have in the application a code that validates the data entry, adjusting according to what is needed, would this 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 usage:

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

Example of how you are 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();
    }

}
    
asked by anonymous 14.02.2017 / 13:10

2 answers

6

You need to use SQLCommand . This way your query will be reliably mounted by ADO.NET. If you try to do the cleaning, you will certainly make a mistake, apart from the enormous work that you give.

This class has a way of assembling the texts with internal "variables" where the parameterized data will be placed cleanly. You always should use this class or some other class that does the same (depending on the technology used).

Let's look at the documentation example:

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);
        }
    }
}

These "@" are the query parameters. 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 look 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();
        }
    }
}
    
14.02.2017 / 13:35
0

Use SQLCommands to do the data insertion, it already does this cleaning for you. Wiping the way you were suggesting would remove words from any text, even if it was not an SQL Injection attempt.

    
14.02.2017 / 14:20