How do I format a url string to pass into stored procedure-CodePudding

I created the below stored procedure in sql server that requires 3 parameters: Date, URL, & Table Name:

ALTER PROCEDURE [stg].[usp_Delete_Data] 
    (@DateLookBack date,
     @siteUrl nvarchar(100), 
     @tableName SYSNAME)
AS
BEGIN
    SET QUOTED_IDENTIFIER ON

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @sql NVARCHAR(MAX);
 
    SET @sql = N'DELETE FROM '   CONCAT('[stg].[',@tableName,']')  
                'WHERE date = '   FORMAT(@DateLookBack, 'yyyyMMdd')   
                'AND siteUrl = '   @siteUrl

    EXEC sp_executesql @Sql
END

When I pass in a url, like 'https://stackoverflow.com', I get an error message:

Incorrect syntax near 'https:'

How do I format the url string so that it can pass into the query successfully?

CodePudding user response：

I'd strongly advise against this method. Having so many tables of the same structure that it requires a single procedure where the table name is dynamic is a code smell in itself.

If you must use dynamic sql though, at least use parameters as much as possible and only inject your table name, i.e.

SET @sql = CONCAT(N'DELETE FROM [stg].' QUOTENAME(@tableName), 
                  ' WHERE Date = @Date AND SiteUrl = @SiteUrl;');

EXECUTE sp_executesql @sql, N'@Date date, @SiteUrl nvarchar(100)', @date, @SiteUrl;

CodePudding user response：

To find such issue, all you need is to PRINT the query before you use it! You could examine the query which is executed, if you printed it first.

Replace the commend Exec sp_executesql @Sql with the command PRINT @Sql and examine the query you get.

In your case, after you do it, then when you execute the procedure using the following command, then I can see all the issues.

EXECUTE dbo.[usp_Delete_Data] 
    @DateLookBack = '2022-02-27' ,@siteUrl = 'https://stackoverflow.com' , @tableName = 'c'
GO

The printed text which we get is: DELETE FROM [stg].[c]WHERE date = 20220227and siteUrl = https://stackoverflow.com

Now we can go over the errors (yes there are multiple errors here) one by one

(1) Notice that the 'WHERE date = ' missing a space before the "where" which might combine the word "where" with the table name that comes before it. You need to add space like ' WHERE date = '

same with the part after the and siteUrl - missing space before the and

(2) Notice this part: siteUrl = https://stackoverflow.com. in the query you are building you do not have quotation marks around the text of the URL => this lead to the error message.

instead of 'and siteUrl = ' @siteUrl it should be: 'and siteUrl = ''' @siteUrl ''''

(3) same issue you have with the date - you do not have quotation marks around the text of the date

instead of ' WHERE date = ' format(@DateLookBack,'yyyyMMdd') it should be ' WHERE date = ''' format(@DateLookBack,'yyyyMMdd') ''''

So, after adding these fixes, you get the following SP (I use PRING instead of execute but you can change this back)

CREATE OR ALTER PROCEDURE [usp_Delete_Data] (
    @DateLookBack date,@siteUrl nvarchar(100), @tableName SYSNAME
) AS BEGIN

    SET QUOTED_IDENTIFIER ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @sql NVARCHAR(MAX);

    SET @sql = N'DELETE FROM '   CONCAT('[stg].[',@tableName,']') 
    --  ' WHERE date = '    format(@DateLookBack,'yyyyMMdd') 
      ' WHERE date = '''    format(@DateLookBack,'yyyyMMdd')   ''''
      ' and siteUrl = '''   @siteUrl   ''''
    --  'and siteUrl = '   @siteUrl
    PRINT @Sql
    --Exec sp_executesql @Sql
END

and now if I execute the same query

EXECUTE dbo.[usp_Delete_Data] 
    @DateLookBack = '2022-02-27' ,@siteUrl = 'https://stackoverflow.com' , @tableName = 'c'
GO

It will print something that looks like:

DELETE FROM [stg].[c] WHERE date = '20220227'and siteUrl = 'https://stackoverflow.com'

BUT! NOW WE CAN GO TO THE MOST PROBLEMATIC ISSUE! Your procedure is open to SQL Injection! You should NOT use such code.

You should use parameters whenever you can when you use sp_executesql and not combine text text. Read the documentation of sp_executesql on how to use parameters as input: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql