如何在SQL查询使用多个参数 [英] How to use multiple parameters in a SQL queries

问题描述

我最近碰到一个声明是如何prevent SQL注入，所以我改变了我的code本（注释掉都是老codeS）：

I recently came across a statement on how to prevent SQL injection, so I changed my code to this (commented out are the old codes):

nameE = txtName.Text;

//sqlCode = "SELECT * FROM [db].[dbo].[TablePDFTest] WHERE [name] = '" + nameE + "'";
sqlCode = "SELECT * FROM [db].[dbo].[TablePDFTest] WHERE [name] = @name";

using (SqlCommand command = new SqlCommand(sqlCode, Conn))
{
      //command.CommandType = CommandType.Text;
      command.Parameters.AddWithValue("name", nameE);

      using (reader = command.ExecuteReader())
      {
        // some action goes here...
      }
 }

我怎么可以做同样多的参数？

How can I do the same with multiple parameters?

我的code是这个，我现在用一个函数填充两个参数从另一个函数的变量：

My code is this where I am using as a function padding the two parameters as a variable from another function:

public void writeData(string k, string c)
{
    Conn = new SqlConnection(cString);
    Conn.Open();

    //MessageBox.Show(k);
    //MessageBox.Show(c);

    var pdfPath = Path.Combine(Server.MapPath("~/PDFTemplates/fw9.pdf"));

    // Get the form fields for this PDF and fill them in!
    var formFieldMap = PDFHelper.GetFormFieldNames(pdfPath);

    //if more than multiple entries, verify by name and the last four ssn
    //sqlCode = "SELECT * FROM [db].[dbo].[TablePDFTest] WHERE [name] = '" + k + "' AND [ssn3] = " + c + "";
    sqlCode = "SELECT * FROM [db].[dbo].[TablePDFTest] WHERE [name] = @name2 AND [ssn3] = @ssnnum";
    //MessageBox.Show("" + sqlCode.ToString());

    using (SqlCommand command = new SqlCommand(sqlCode, Conn))
    {
        //command.CommandType = CommandType.Text;
        command.Parameters.AddWithValue("name2", k);
        command.Parameters.AddWithValue("ssnnum", c);

        using (reader = command.ExecuteReader())
        {
            if (reader.HasRows)
            {
                if (reader.Read())
                {
                    MessageBox.Show(reader.GetValue(0).ToString());
                    /*formFieldMap["topmostSubform[0].Page1[0].f1_01_0_[0]"] = reader.GetValue(0).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].f1_02_0_[0]"] = reader.GetValue(1).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].f1_04_0_[0]"] = reader.GetValue(2).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].f1_05_0_[0]"] = reader.GetValue(3).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].f1_07_0_[0]"] = reader.GetValue(4).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].social[0].TextField1[0]"] = reader.GetValue(5).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].social[0].TextField2[0]"] = reader.GetValue(6).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].social[0].TextField2[1]"] = reader.GetValue(7).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].social[0].TextField2[2]"] = reader.GetValue(8).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].social[0].TextField2[3]"] = reader.GetValue(9).ToString();*/
                }
            }
        }
    }

    // Requester's name and address (hard-coded)
    //formFieldMap["topmostSubform[0].Page1[0].f1_06_0_[0]"] = "Medical Group\n27 West Ave\nPurchase, NY 10577";

    //var pdfContents = PDFHelper.GeneratePDF(pdfPath, formFieldMap);

    //PDFHelper.ReturnPDF(pdfContents, "Completed-W9.pdf");
}

推荐答案

像之前您可以添加parammeter。
这是你的code将如何魔神这样的：

You can add parammeter as you did before. This is how your code will loke like:

sqlCode = "SELECT * FROM [db].[dbo].[TablePDFTest] WHERE [name] = @name AND [ssn3] =@ssn3";

using (SqlCommand command = new SqlCommand(sqlCode, Conn))
{
      //command.CommandType = CommandType.Text;
      command.Parameters.AddWithValue("@name", nameE);
      command.Parameters.AddWithValue("@ssn3", c);

      using (reader = command.ExecuteReader())
      {
        // some action goes here...
      }
 }

这篇关于如何在SQL查询使用多个参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！