Mysql parameter automation does not work

Question

I’m trying to create a way to automate the business of adding parameters in the query to prevent sql inject, but it doesn’t work!

This is my code:

public static MySqlCommand criarQueryComParametros(string tabela, string condicao)
{
    List<string> parametros = Regex.Split(condicao, "'(.*?)'").ToList();
    parametros = parametros.Where(x => parametros.IndexOf(x) % 2 == 1).ToList();
    string sql = "SELECT * FROM " + tabela + " WHERE " + remodelarCondicao(condicao, parametros);
    MySqlCommand query = new MySqlCommand(sql, Database.conexao);
    montarListaDeParametros(condicao, parametros, query);
    return query;
}

public static void montarListaDeParametros(string condicao, List<string> parametros, MySqlCommand query)
{
    for (int i = 0; i < parametros.Count; i++)
    {
        query.Parameters.AddWithValue($"@p{i}p", parametros[i]);
    }
}

public static string remodelarCondicao(string condicao, List<string> parametros)
{
    for (int i = 0; i < parametros.Count; i++)
    {
        condicao = condicao.Replace(parametros[i], $"@p{i}p");
    }
    return condicao;
}

Parameters I am passing:

criarQueryComParametros("empresa", "email='teste@teste' AND senha='202CB962AC59075B964B07152D234B70'");

What happens is he’s not setting the parameters, when I give one Console.Log(query.CommandText), he returns:

SELECT * FROM empresa WHERE email='@p0p' AND senha='@p1p

What could I be doing wrong? Is there a better way to do this?

Answer 1

I adapted this method for you MontarSqlComCondicoes() and you can take as your starting point.

Behold:

static string MontarSqlComCondicoes(string tabela, string c1, string c2, bool and)
{
    var condicoes = new List<string>();

    var sql = new StringBuilder();
    sql.Append("select * from")
        .Append(" ")
        .Append(tabela)
        .Append(" ");

    if (!string.IsNullOrWhiteSpace(c1))
    {
        condicoes.Add(c1);
    }

    if (!string.IsNullOrWhiteSpace(c2))
    {
        condicoes.Add(c2);
    }       

    if (condicoes.Any())
    {
        sql.Append("where")
            .Append(" ");

        if (and) sql.Append(string.Join(" and ", condicoes.ToArray()));
        else sql.Append(string.Join(" or ", condicoes.ToArray()));              
    }       

    return sql.ToString();
}

See an example of its implementation:

var sql = MontarSqlComCondicoes("pessoa", "nome = @nome", "idade > @idade", true);

Exit:

select * from pessoa where nome = @nome and idade > @idade

It is noteworthy that this routine will not prevent you from an attack of SQL Injection, Perhaps the use of parameterized conditions may even make it a little difficult, however, there are other vulnerabilities that can be explored in the code, so I recommend that you adapt it according to your need. And still worth my recommendation to use a Sqlbuilder, of course, for your future projects ;).

See working on .Net Fiddle.

Source: https://stackoverflow.com/a/17321354/5429980

Answer 2

If the intention is to prevent SQL Injection, you’re on the wrong track.

Mysql Driver for . NET already has all the resources necessary for this, just use correctly.

Mysql driver has Mysqlparameter feature, and that’s more than enough to prevent SQL Injection, see:

var sql = "SELECT Id, Nome, Email FROM Empresa WHERE Email=@Email AND Senha=@Senha";

var cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@Email", email);
cmd.Parameters.AddWithValue("@Senha", senha);

 var dr = cmd.ExecuteReader();

Anything very different from that will be overheading, that is, it will increase complexity without bringing relevant benefits.

Now, if you really want to improve data consumption between your application and data persistence - mysql, sql, oracle, etc - consider using a Micro ORM - Object Relational Mapping.

Dapper, by Stackexchange

public class Cachorro
{
    public int? Idade { get; set; }
    public Guid Id { get; set; }
    public string Nome { get; set; }
    public float? Peso { get; set; }

    public int PropriedadeIgnorada { get { return 1; } }
}            

var guid = Guid.NewGuid();
var cachorro = connection.Query<Cachorro>("select Idade = @Age, Id = @Id", new { Idade = (int?)null, Id = guid });

Ormlite, by Servicestack

var dbFactory = new OrmLiteConnectionFactory(
    ":memory:", SqliteDialect.Provider);
using (IDbConnection db = dbFactory.Open())
{
    db.DropAndCreateTable<Todo>();
    var todo = new Todo
    {
        Content = "Learn OrmLite",
        Order = 1,
    };

    db.Save(todo);

    var savedTodo = db.SingleById<Todo>(todo.Id);
    savedTodo.Content = "Updated";
    db.Save(savedTodo);

    "Updated Todo:".Print();
    db.Select<Todo>(q => q.Content == "Updated").PrintDump();

    db.DeleteById<Todo>(savedTodo.Id);

    "No more Todos:".Print();
    db.Select<Todo>().PrintDump();
}