Subquery in parameterized SQL

Question

Subquery in parameterized SQL

I am using the following code to add some parameterized values to a SQL table.

'--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

For @CLI value, I would like to use an auxiliary query to trim the first character of the string

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

How do I include an auxiliary query in my parameterized SQL?

I am relatively new to VB coding, so if you could provide some example code that would be much appreciated.

+3

sql sql-server vb.net subquery

user3580480 Dec 19. 14 at 10:41

source to share

1 answer

SQLDiver · Accepted Answer · 2014-12-19T11:13:43+0000

Cannot pass code as parameter. The purpose of parameterized queries is to prevent code from being passed through a parameter in order to protect against SQL injection attacks.

There are two ways to achieve the desired result:

Truncating a value before passing it as a parameter
Truncating the value after passing the parameter.

The latter would mean changing your sql code like this:

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

One option you may want to consider is instead of using parameterized queries to put the code in a stored procedure and call that stored procedure with parameters. A database application typically uses a set of CRUD stored procedures - create, read, update, delete. There are even scripts to create basic templates from your table structure. Here's one example:

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

Subquery in parameterized SQL

More articles: