June 3, 2019 at 12:19 pm
Hi all
I've got a stored procedure that runs on a schedule.
It generates some dynamic SQL based on another table (it's run from SSIS and parameters are passed to it from there).
I'm trying to get around possible SQL injection by using "EXECUTE sys.sp_executesql" but it's not going quite as well as I wanted.
I can get two parameters to work but not the other two.
The stored procedure is here:-
USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[usp_SSRS_DR_Checker] Script Date: 03/06/2019 12:57:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*=====================================================================================================================
Reports latest record count and last update date/time for requested tables
=======================================================================================================================
InitialsDate/Time of ChangeChange Made
RG31/05/2019Initial Coding
=======================================================================================================================
Example call: exec [usp_SSRS_DR_Checker] 'AbsOperationProcedures','livedb','20190531'
=======================================================================================================================*/
ALTER PROCEDURE [dbo].[usp_SSRS_DR_Checker]
(
@tableNVARCHAR(MAX)
,@databasenameNVARCHAR(MAX)
,@UpdateDate DATETIME
)
AS
DECLARE
@localtable NVARCHAR(MAX)
,@localdatabasename NVARCHAR(MAX)
,@localupdatedate DATETIME
,@start DATETIME
,@end DATETIME
,@SQL NVARCHAR(MAX);
SET @localtable = @table;
SET @localdatabasename = @databasename;
SET @localupdatedate = @UpdateDate;
--SET @localstart='20170608'
--SET @localend='20170808'
BEGIN
BEGIN TRY
DROP TABLE #temp;
END TRY
BEGIN CATCH
END CATCH;
CREATE TABLE #temp
(
TableNameVARCHAR(50) NOT NULL
,StartDateTimeDATETIMENOT NULL
,EndDateTimeDATETIMENULL
,RowsAddedINTNULL
,LastUpdatedDATETIMENULL
);
SELECT
@end = CAST(CAST(@localupdatedate AS DATE) AS DATETIME) + CAST((
SELECT
dt.Interval15Min
FROM
DataWarehouse.dbo.Dim_Time AS dt
WHERE
dt.ActualTime = CONVERT(TIME(0), @localupdatedate)
) AS DATETIME);
SELECT
@start= DATEADD(MINUTE, -15, @end);
--/*
SELECT
@SQL = N'
SELECT
TableName = ''' + @localtable
+ N'''
,StartDateTime = @start
,EndDateTime = @end
,RowsAdded = COUNT(*)
,LatestUpdate = MAX(RowUpdateDateTime)
FROM
[TRNFT-DR01].' + @localdatabasename + N'.dbo.' + @localtable + N'
WHERE
RowUpdateDateTime BETWEEN @start AND @end
';
INSERT INTO #temp
(
TableName
,StartDateTime
,EndDateTime
,RowsAdded
,LastUpdated
)
EXECUTE sys.sp_executesql
@SQL
,N'@start datetime, @end datetime'
,@start
,@end;
--*/
MERGE Reporting.dbo.tbl_DR_Table_Checker AS target
USING #temp AS source
ON target.TableName = source.TableName
AND target.StartDateTime = source.StartDateTime
WHEN NOT MATCHED THEN
INSERT
(
TableName
,StartDateTime
,EndDateTime
,RowsAdded
,LastUpdated
)
VALUES
(
source.TableName
,source.StartDateTime
,source.EndDateTime
,source.RowsAdded
,source.LastUpdated
)
WHEN MATCHED THEN
UPDATE SET
EndDateTime = source.EndDateTime
,RowsAdded = source.RowsAdded
,LastUpdated = source.LastUpdated;
END;
As you can see, I've parameterised the start/end dates, but I also want to parameterise the database and table names.
This is where I get a bit unstuck. It looks like I can only use parameters where they would normally be expected (i.e. in the WHERE clause).
I get the feeling that I can't use parameters for these two items but I'm willing to be educated (as always).
Can anyone point me in the right direction please?
TIA
Richard
June 3, 2019 at 12:27 pm
It should work like that. You've probably got a little syntax error somewhere - that's all. What is the error message that you get?
John
June 3, 2019 at 12:36 pm
Hi John
The query works as-is but I want to include the database and table names in the parameters.
When I alter the code (just to keep from altering the stored procedure) to:-
DECLARE
@localtable NVARCHAR(MAX)
,@localdatabasename NVARCHAR(MAX)
,@localupdatedate DATETIME
,@start DATETIME
,@end DATETIME
,@SQL NVARCHAR(MAX);
SET @localtable = N'AbsOperationProcedures';
SET @localdatabasename = N'livedb';
SET @localupdatedate = '20190531';
--SET @localstart='20170608'
--SET @localend='20170808'
BEGIN
BEGIN TRY
DROP TABLE #temp;
END TRY
BEGIN CATCH
END CATCH;
CREATE TABLE #temp
(
TableNameVARCHAR(50) NOT NULL
,StartDateTimeDATETIMENOT NULL
,EndDateTimeDATETIMENULL
,RowsAddedINTNULL
,LastUpdatedDATETIMENULL
);
SELECT
@end = CAST(CAST(@localupdatedate AS DATE) AS DATETIME) + CAST((
SELECT
dt.Interval15Min
FROM
DataWarehouse.dbo.Dim_Time AS dt
WHERE
dt.ActualTime = CONVERT(TIME(0), @localupdatedate)
) AS DATETIME);
SELECT
@start= DATEADD(MINUTE, -15, @end);
--/*
SELECT
@SQL = N'
SELECT
TableName = @localtable
,StartDateTime = @start
,EndDateTime = @end
,RowsAdded = COUNT(*)
,LatestUpdate = MAX(RowUpdateDateTime)
FROM
[TRNFT-DR01].@localdatabasename.dbo.@localtable
WHERE
RowUpdateDateTime BETWEEN @start AND @end
';
INSERT INTO #temp
(
TableName
,StartDateTime
,EndDateTime
,RowsAdded
,LastUpdated
)
EXECUTE sys.sp_executesql
@SQL
,N'@start datetime, @end datetime,@localtable varchar(max),@localdatabasename varchar(max)'
,@start
,@end
,@localtable
,@localdatabasename;
--*/
SELECT
t.TableName
,t.StartDateTime
,t.EndDateTime
,t.RowsAdded
,t.LastUpdated
FROM
#temp AS t;
END;
I get the error:-
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '@localdatabasename'.
This appears to relate to the FROM clause as it's the only place it's used in the dynamic SQL.
Cheers
Richard
June 3, 2019 at 12:46 pm
Richard
Your hunch in your original post was correct - you can't parameterise the database name and table name. I don't understand why you want to change it from how it is, though, if it works?
By the way, one way of defending against SQL injection is to use appropriate data types. You should use sysname for object names, database names and so on.
John
June 3, 2019 at 12:56 pm
Object names cannot be passed into sp_execute_sql.
On another note, there is no need to use nvarchar(max) for your object types.
@table nvarchar(MAX) -- Should be sysname
, @databasenamenvarchar(MAX) -- Should be sysname
From nchar and nvarchar (Transact-SQL)
sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable. sysname is used to reference database object names.
June 3, 2019 at 1:07 pm
Thanks guys
That will do for me.
Completely forgot about using sysname (but I didn't think it would get around SQL injection so I've learned something).
Cheers
Richard
June 3, 2019 at 1:14 pm
Using sysname in this instance, and appropriate data types in general, won't in itself prevent SQL injection - it's just one more line of defence. It's more difficult for a hacker to inject SQL with only 128 characters to play with than it is if he can make his malicious code as long as he likes.
John
June 3, 2019 at 1:22 pm
Thanks John
Never thought of it like that so I've learned something else as well.
Cheers
Richard
June 3, 2019 at 9:01 pm
You were almost there. You correctly parameterized the @localtable variable in the first field of the select. However, to ensure that the variables that you concatenate for the object names in the FROM section use the function QUOTENAME around the variable names.
When you use the QUOTENAME() function it will place the square brackets around the value being concatenated and will ensure that SQL Server will treat the whole value as an object.
DECLARE
@localtable SYSNAME
,@localdatabasename SYSNAME
,@localupdatedate DATETIME
,@start DATETIME
,@end DATETIME
,@SQL NVARCHAR(MAX);
SET @localtable = N'AbsOperationProcedures';
SET @localdatabasename = N'livedb';
SET @localupdatedate = '20190531';
--SET @localstart='20170608'
--SET @localend='20170808'
BEGIN
BEGIN TRY
DROP TABLE #temp;
END TRY
BEGIN CATCH
END CATCH;
CREATE TABLE #temp
(
TableNameSYSNAME NOT NULL
,StartDateTimeDATETIMENOT NULL
,EndDateTimeDATETIMENULL
,RowsAddedINTNULL
,LastUpdatedDATETIMENULL
);
SELECT
@end = CAST(CAST(@localupdatedate AS DATE) AS DATETIME) + CAST((
SELECT
dt.Interval15Min
FROM
DataWarehouse.dbo.Dim_Time AS dt
WHERE
dt.ActualTime = CONVERT(TIME(0), @localupdatedate)
) AS DATETIME);
SELECT
@start= DATEADD(MINUTE, -15, @end);
--/*
SELECT
@SQL = N'
SELECT
TableName = @localtable
,StartDateTime = @start
,EndDateTime = @end
,RowsAdded = COUNT(*)
,LatestUpdate = MAX(RowUpdateDateTime)
FROM
[TRNFT-DR01].' + QUOTENAME(@localdatabasename) + N'.dbo.' + QUOTENAME(@localtable) + N'
WHERE
RowUpdateDateTime BETWEEN @start AND @end
';
INSERT INTO #temp
(
TableName
,StartDateTime
,EndDateTime
,RowsAdded
,LastUpdated
)
EXECUTE sys.sp_executesql
@SQL
,N'@start datetime, @end datetime,@localtable SYSNAME'
,@start
,@end
,@localtable
,@localdatabasename;
--*/
SELECT
t.TableName
,t.StartDateTime
,t.EndDateTime
,t.RowsAdded
,t.LastUpdated
FROM
#temp AS t;
END;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply