参数化SQL中的子查询
问题描述:
我正在使用以下代码向SQL表中添加一些泛化值。参数化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)
如何我纳入一个子查询的第一个字符截断了我的Paramitized SQL?
我比较新的VB编码,所以如果你可以提供一些示例代码,将不胜感激。
答
无法将代码作为参数传递。参数化查询的目的是防止代码通过参数传递,以防止SQL注入攻击。
有两种方法来实现你想要的结果:
- 作为参数传递
- 参数已过去后,截断值之前截断值。
后者将意味着改变你的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
+1
非常感谢你,之后截断完美:) – user3580480 2014-12-19 11:16:44
做,在VB和它当作一个额外的参数到您的SQL。由于您使用的是嵌入式SQL,因此没有理由在sql中而不是在vb中执行此操作。使用vb:它更容易,更快,更清洁。 – Paolo 2014-12-19 10:56:04