通过 C# 避免带有参数的 SQL 注入？

Question

我最近调整了我的代码以避免对 maria db 进行 SQL 注入，并在添加参数方面得到了帮助，当我使用参数方法页面时出现运行时错误

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));

    }

}

我对使用 maria db 比较陌生，所以感谢您的帮助

Answer 1

如果你想避免 SQL 注入，除了参数化查询之外的另一种方法是存储过程。您可以从此处阅读它 => https://www.techonthenet.com/mariadb/procedures.php 或者您可以自己研究。在 ASP.NET 应用程序中调用存储过程的演示方法：using (MySqlConnection con = new MySqlConnection(constr)){&nbsp; &nbsp; using (MySqlCommand cmd = new MySqlCommand("Customers_GetCustomer", con))&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; cmd.CommandType = CommandType.StoredProcedure;&nbsp; &nbsp; &nbsp; &nbsp; cmd.Parameters.AddWithValue("@CustId", customerId);&nbsp; &nbsp; &nbsp; &nbsp; using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd))&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DataTable dt = new DataTable();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sda.Fill(dt);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GridView1.DataSource = dt;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GridView1.DataBind();&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }}（代码取自https://www.aspsnippets.com/Articles/Call-MySql-Stored-Procedure-with-Parameters-in-ASPNet-C-and-VBNet.aspx）