Parameter automation Mysql does not work

If you intend to prevent SQL Injection , you are on the wrong path.

The MySQL Driver for .NET already has all the features needed for this, just use it correctly.

In the MySQL driver you have the MySQLParameter feature, and this is 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 this will be overheading , meaning 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();
}

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

See:

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 implementation of it:

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

Output:

  

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

It's worth noting that this routine will not prevent you from a SQL Injection attack , perhaps the use of parameterized conditions may even make it difficult, but there are other vulnerabilities that can be exploited 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 at .Net Fiddle .

Source: link