sql - 参数化 SQL 中的子查询

Question

我正在使用以下代码将一些参数化值添加到 SQL 表。

'--Connect to datasource
Dim SqlconnectionString As String = "server=inlt01\SQLEXPRESS; database=DaisyServices; integrated security=yes"

'--Import selected file to Billing table and Master Services
Dim strSql As String = "INSERT INTO [" + FileNameOnly + "] (Site,CLI,FromDate,ToDate,Quantity,UnitCost,TotalCost,[Description],[User],Department,Filenameonly,billingmonth) VALUES (@Site,@CLI,@FromDate,@ToDate,@Quantity,@UnitCost,@TotalCost,@Description,@User,@Department,@filenameonly,(SELECT  ( CASE SUBSTRING(@filenameonly,1,3)WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 WHEN 'Jun' THEN 6 WHEN 'Jul' THEN 7 WHEN 'Aug' THEN 8 WHEN 'Sep' THEN 9 WHEN 'Oct' THEN 10 WHEN 'Nov' THEN 11 WHEN 'Dec' THEN 12 END ))); INSERT INTO [DaisyServicesMaster] (Site,CLI,FromDate,ToDate,Quantity,UnitCost,TotalCost,[Description],[User],Department,filenameonly,billingmonth) VALUES (@Site,@CLI,@FromDate,@ToDate,@Quantity,@UnitCost,@TotalCost,@Description,@User,@Department,@filenameonly,(SELECT  ( CASE SUBSTRING(@filenameonly,1,3)WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 WHEN 'Jun' THEN 6 WHEN 'Jul' THEN 7 WHEN 'Aug' THEN 8 WHEN 'Sep' THEN 9 WHEN 'Oct' THEN 10 WHEN 'Nov' THEN 11 WHEN 'Dec' THEN 12 END )))"

        Using connection As New SqlClient.SqlConnection(SqlconnectionString)

        Dim cmd As New SqlClient.SqlCommand(strSql, connection) ' create command objects and add parameters
        With cmd.Parameters
                  .Add("@Site", SqlDbType.VarChar, 30, "Site")
                  .Add("@CLI", SqlDbType.VarChar, 30, "CLI")
                  .Add("@FromDate", SqlDbType.Date, 30, "FromDate")
                  .Add("@ToDate", SqlDbType.Date, 30, "ToDate")
                  .Add("@Quantity", SqlDbType.Int, 3, "Quantity")
                  .Add("@UnitCost", SqlDbType.Float, 5, "UnitCost")
                  .Add("@TotalCost", SqlDbType.Float, 5, "TotalCost")
                  .Add("@Description", SqlDbType.VarChar, 100, "Description")
                  .Add("@User", SqlDbType.VarChar, 30, "User")
                  .Add("@Department", SqlDbType.VarChar, 30, "Department")
                  .AddWithValue("@filenameonly", FileNameOnly)

         End With

对于@CLI 值，我想使用子查询来截断字符串的第一个字符

SELECT RIGHT(CLI, LEN(CLI) - 1)

如何将子查询合并到我的参数化 SQL 中？

我对 VB 编码比较陌生，所以如果您能提供一些示例代码，我将不胜感激。

Answer 1

无法将代码作为参数传递。参数化查询的目的是防止代码通过参数传递，以防止 SQL 注入(inject)攻击。

有两种方法可以达到您想要的结果:

1. 在作为参数传递之前截断值
2. 在传递参数后截断值。

后者意味着将您的 sql 代码更改为以下内容:

Dim strSql As String = "INSERT INTO [" + FileNameOnly + "] (Site,CLI,FromDate,ToDate,Quantity,UnitCost,TotalCost,[Description],[User],Department,Filenameonly,billingmonth) VALUES (@Site,RIGHT(@CLI, LEN(@CLI) - 1),@FromDate,@ToDate,@Quantity,@UnitCost,@TotalCost,@Description,@User,@Department,@filenameonly,(SELECT  ( CASE SUBSTRING(@filenameonly,1,3)WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 WHEN 'Jun' THEN 6 WHEN 'Jul' THEN 7 WHEN 'Aug' THEN 8 WHEN 'Sep' THEN 9 WHEN 'Oct' THEN 10 WHEN 'Nov' THEN 11 WHEN 'Dec' THEN 12 END ))); INSERT INTO [DaisyServicesMaster] (Site,CLI,FromDate,ToDate,Quantity,UnitCost,TotalCost,[Description],[User],Department,filenameonly,billingmonth) VALUES (@Site,RIGHT(@CLI, LEN(@CLI) - 1),@FromDate,@ToDate,@Quantity,@UnitCost,@TotalCost,@Description,@User,@Department,@filenameonly,(SELECT  ( CASE SUBSTRING(@filenameonly,1,3)WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 WHEN 'Jun' THEN 6 WHEN 'Jul' THEN 7 WHEN 'Aug' THEN 8 WHEN 'Sep' THEN 9 WHEN 'Oct' THEN 10 WHEN 'Nov' THEN 11 WHEN 'Dec' THEN 12 END )))"

您可能会考虑的一种选择是不使用参数化查询，而是将代码放入存储过程并使用参数调用该存储过程。在数据库应用程序中通常有一组 CRUD 存储过程 - 创建、读取、更新、删除。甚至还有脚本可以根据您的表结构生成基本模板。这是一个例子:

http://www.sqlbook.com/SQL-Server/Auto-generate-CRUD-Stored-Procedures-40.aspx