Doubt with SQL injection

Question

Doubt with SQL injection

Navigation

#1 by (4 votes)

2

I have an internal method that is called based on another query, it is this way down. I would like to know if this would be a "string.Format" error, if it would be possible to send a SQL injection?

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 the procedure has a variable @codeproduct char (20), if it is an error what is the best correction?

c# asp.net-mvc

asked by anonymous 13.06.2017 / 05:00

1 answer

Number of items to replace is not a multiple of substitute length Go through an array where the index is a Date

score 4 · Answer 1

SIM! Using string concatenation is the main way to create a SQL Injection opportunity. It does not matter whether 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 the SqlCommand with StorageProcedure in the correct 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 : Read ORM Dapper . I think it will help you a lot.