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

TBH I don't know why this has been flagged, since there's not even a question to begin with ... — Arthur Attout, Apr 02 '19 at 06:58
@ZoharPeled may have gotten a bit hammery, this is a duplicate though, or just a bad question. However i am out of votes for this question now — TheGeneral, Apr 02 '19 at 06:59
@MichaelRandall I'm just wondering what makes you think that this is a duplicate of the question you've chosen. I mean, this isn't even a question - just a code dump - and clearly not about why to use parameters... If it needs to be closed, it's for being unclear. — Zohar Peled, Apr 02 '19 at 07:01
@ZoharPeled the topic/title was the determining factor for me — TheGeneral, Apr 02 '19 at 07:03
@MichaelRandall well, I've voted to close as unclear, hopefully the OP will edit the post to include an actual question before it gets closed. — Zohar Peled, Apr 02 '19 at 07:04
"got running time error" - usually those errors have, you know, *useful information in the error message*. You've not told us *what error you're getting*. — Damien_The_Unbeliever, Apr 02 '19 at 07:11
Why are you sending dates as strings to the database? Though I can't be sure without seeing the error message, and even if it's not the reason for your error, you shouldn't do that. Send dates as dates (instances of the DateTime struct) and not as strings. — Zohar Peled, Apr 02 '19 at 07:17

score 0 · Accepted Answer · edited Apr 16 '20 at 21:16

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)

Avoiding SQL Injections with Parameters by C#?

1 Answers1