Doubt with SQL Injection

Asked

Viewed 143 times

3

I have an internal method that is called on the basis of another query, it is this way down. I wonder if this would be a "string.Format" error, if it would be possible to send an Injection SQL? 

public BuscaProdutosDermaClubeEntity ProdutoDermaClube(string codigoproduto)
{
    var strQuery = "";
    strQuery += " Usp_Site_BuscaProdutosDermaClub";
    strQuery += string.Format(" @codigoproduto = '{0}' ", codigoproduto);

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

public SqlDataReader ExecutaComandoComRetorno(string strQuery)
{
    var cmdComando = new SqlCommand(strQuery, minhaConexao);
    return cmdComando.ExecuteReader();
}

In the database Procedure has a variable @codigoproduto char(20), if it is an error, which is the best correction?

c# asp.net-mvc

  • Is a StoredProcedure? because it passes the value so if it is StoredProcedure if there is an ideal and correct way? what is inside ExecutaComandoComRetorno he treats the data?

  • adjusted the question

  • Yes has problems your code in security and other...

  • Complementing what has already been said: Besides using Parameters defining its query in an "immutable" way in its construction and use, validate the inputs and outputs, following the idea of "Least Privilege", giving the user only the necessary for that context. An example is a field that has the purpose of receiving only literal characters, why receive special characters? Or even if you need to, validate possible malicious entries. - https://www.owasp.org/index.php/Least_privilege

1 answer

5

YES! Using string concatenation is the main way to create opportunity over a SQL Injection. No matter if by explicit concatenation - "a" + "b" - with extensions - string.Format("{0}", "a") - or by interpolation - $"{a}".

To prevent this is quite simple, but use Sqlparameters, and use Sqlcommand with Storageprocedure in the right way:

public ProdutoEntity TrazProdutoPorCodigo(string codigoProduto)
{
    using (var db = new Contexto())
    {
        var cmd = db.CreateCommand();

        // Instruir seu command que irá executar uma SP.
        cmd.CommandType = CommandType.StorageProcedure;
        cmd.Command = "Usp_Site_BuscaProdutosDermaClub";

        // Aqui é onde se evita SQL Injection 
        cmd.Parameters.Add(new SqlParameter("codigoproduto", codigoProduto));

        // Executar a consulta
        var dr = cmd.ExecuteReader();
        return TransformaReaderEmListaObjetos(dr).SingleOrDefault();
    }
}

PS: Gives a read on ORM Dapper. I believe it will help you a lot.

Browser other questions tagged c# asp.net-mvc

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