c# - Avoiding SQL Injections with Parameters by C#?

Question

I have recently adjusted my code to avoid getting SQL injections for maria db and got helped with adding parameters ,when I using parameters method page got running time error

strSQL = "SELECT * from user where uid = @uid AND start >= @StartDate AND end <= @EndDate ";
DataSet ds = QueryDataSet(strSQL, uid , StartDate, EndDate);


public DataSet QueryDataSet(string strSQL,string uid , string StartDate, string EndDate)
{
    try
    {
        MySqlDataAdapter da = new MySqlDataAdapter(strSQL, DBconn);
        da.SelectCommand.Parameters.AddWithValue("@uid", uid );
        da.SelectCommand.Parameters.AddWithValue("@StartDate", StartDate);
        da.SelectCommand.Parameters.AddWithValue("@EndDate", EndDate);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }
    catch (Exception ex)
    //catch
    {
        throw (new System.Exception(ex.Message));

    }
}

I am relatively new to using maria db so any help is appreciated

标签： c#sqlsql-injection

Answer 1

If you want to avoid SQL injections, another approach besides parametrized queries is stored procedures.

You can read it from here => https://www.techonthenet.com/mariadb/procedures.php or you can research on your own.

Demo way of calling a stored procedure in an ASP.NET application:

using (MySqlConnection con = new MySqlConnection(constr))
{
    using (MySqlCommand cmd = new MySqlCommand("Customers_GetCustomer", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@CustId", customerId);

        using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd))
        {
            DataTable dt = new DataTable();
            sda.Fill(dt);

            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
    }
}

(Code taken from https://www.aspsnippets.com/Articles/Call-MySql-Stored-Procedure-with-Parameters-in-ASPNet-C-and-VBNet.aspx)