如何将此查询用作MSSQL [英] how to use this query as MSSQL

问题描述

"SELECT (invoiceNo) as [Invoice No],(InvoiceDate) as [Invoice Date],(Sales.CustomerID) as [Customer ID],(CustomerName) as [Customer Name],SubTotal as [SubTotal],VATPercentage as [Vat+ST %],VATAmount as [VAT+ST Amount],(GrandTotal) as [Grand Total],(TotalPayment) as [Total Payment],(PaymentDue) as [Payment Due],Remarks from Sales,Customer where Sales.CustomerID=Customer.CustomerID and InvoiceDate between #" + dtpInvoiceDateFrom.Text + "# And #" + dtpInvoiceDateTo.Text + "# order by InvoiceDate desc", con);

推荐答案

不要。

无论你从哪里复制，这是一种危险的做事方式：不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。



具体如何操作取决于您使用的语言来访问SQL服务器 - 而且该片段看起来可能是C＃。 />
所以它会是这样的：

Don't.
Wherever you copied that from, it's a dangerous way to do things: Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

Exactly how you do that will depend on the language you are using to access SQL server - and that fragment looks like it probably C#.
So it'll be something like:

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT (invoiceNo) as [Invoice No],(InvoiceDate) as [Invoice Date],(Sales.CustomerID) as [Customer ID],(CustomerName) as [Customer Name],SubTotal as [SubTotal],VATPercentage as [Vat+ST %],VATAmount as [VAT+ST Amount],(GrandTotal) as [Grand Total],(TotalPayment) as [Total Payment],(PaymentDue) as [Payment Due],Remarks from Sales,Customer where Sales.CustomerID=Customer.CustomerID and InvoiceDate between @FROM And @TO", con))
        {
        cmd.Parameters.AddWithValue("@FROM", dtpInvoiceDateFrom.Value);
        cmd.Parameters.AddWithValue("@TO", dtpInvoiceDateTo.Value);
        using (SqlDataReader reader = cmd.ExecuteReader())
            {
            while (reader.Read())
                {
                ...
                }
            }
        }
    }

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
StringBuilder sb = new StringBuilder();
            sb.Append("SELECT invoiceNo as [Invoice No],InvoiceDate as [Invoice Date] ");
            sb.Append(" ,Sales.CustomerID as [Customer ID],CustomerName as [Customer Name] ");
            sb.Append(" ,SubTotal as [SubTotal],VATPercentage as [Vat+ST %],VATAmount as [VAT+ST Amount] ");
			sb.Append(" ,GrandTotal as [Grand Total],TotalPayment as [Total Payment] ");
            sb.Append(" ,PaymentDue as [Payment Due],Remarks ");
			sb.Append(" from Sales,Customer ");
            sb.AppendFormat(" Sales.CustomerID= {0}", CustomerID);
            sb.AppendFormat(" AND InvoiceDate BETWEEN '{0}' AND '{1}' ",startDate,endDate ); 

    sqlCommand command = new sqlCommand (sb, con );
                sqlAdapter dataAdapter = new sqlAdapter (command);

                DataSet dataSet = new DataSet();
                dataAdapter.Fill(dataSet, "t");

                connection.Close();

                return dataSet;
    }

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