Hi
I am trying to create a stored procedure. I am getting an error. Is someone able to help me with this? Thank you
USE MyDB
GO
CREATE PROCEDURE UpdateTable @FILENAME varchar(200)
AS
BULK INSERT dbo.TableTemp
FROM @FILENAME
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', MAXERRORS = 0);
--uncomment below code when ready for production
/*
INSERT INTO DBO.Table
SELECT *, GETDATE() AS TIME_STAMP
FROM dbo.TableTemp
*/
GO
Error
Msg 102, Level 15, State 1, Procedure UpdateTable, Line 6 [Batch Start Line 2]
Incorrect syntax near '@FILENAME'.
Msg 319, Level 15, State 1, Procedure UpdateTable, Line 7 [Batch Start Line 2]
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Completion time: 2022-02-02T14:22:05.3535583-08:00
SQL Server does not all "FROM @FILENAME", only "FROM 'data_file_name'". Google the syntax of the command.
You'll need to use dynamic SQL if you want to feed in a FROM file name.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 3, 2022 at 5:43 am
ScottPletcher wrote:SQL Server does not all "FROM @FILENAME", only "FROM 'data_file_name'". Google the syntax of the command.
You'll need to use dynamic SQL if you want to feed in a FROM file name.
thank you so much! I got it working
If you used dynamic SQL to do it, please post the code so we can help you make sure that you didn't just open the door to a world of hurt known as "SQL Injection".
Seriously... post the code. SQL Injection is still one of the leading attack vectors used by hackers.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2022 at 3:24 pm
water490 wrote:ScottPletcher wrote:SQL Server does not all "FROM @FILENAME", only "FROM 'data_file_name'". Google the syntax of the command.
You'll need to use dynamic SQL if you want to feed in a FROM file name.
thank you so much! I got it working
If you used dynamic SQL to do it, please post the code so we can help you make sure that you didn't just open the door to a world of hurt known as "SQL Injection".
Seriously... post the code. SQL Injection is still one of the leading attack vectors used by hackers.
Good point. I never considered that. Here is my code. How can I protect against a sql injection attack? Thank you
USE [MyDB]
GO
/****** Object: StoredProcedure [dbo].[UpdateTable] Script Date: 2022-02-03 7:21:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UpdateTable] @FILENAME varchar(200)
AS
DECLARE @FILEPATH VARCHAR(200)
SET @FILEPATH = @FILENAME
EXEC('BULK INSERT dbo.TempTable
FROM ''' +
@FILEPATH +
''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', MAXERRORS = 0);
--uncomment below code when ready for production
/*
INSERT INTO DBO.Table
SELECT *, GETDATE() AS TIME_STAMP
FROM dbo.TempTable
TRUNCATE TABLE dbo.TempTable
*/'
)
February 3, 2022 at 4:40 pm
Ahhhh. SQL Injection at it's finest!
Take a look here:
https://www.sqlshack.com/dynamic-sql-in-sql-server/
https://www.sommarskog.se/dynamic_sql.html
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 3, 2022 at 5:11 pm
Ahhhh. SQL Injection at it's finest!
Take a look here:
Thanks for the links. I read them over. This raises some questions and I need to better understand this. I will explain the proposed design.
USE [MyDB]
GO
/****** Object: StoredProcedure [dbo].[CheckImportHistory] Script Date: 2022-02-03 9:08:26 AM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CheckImportHistory] (@FILENAME VARCHAR(100), @NUM_RECORDS INT OUTPUT)
AS
BEGIN
SELECT@NUM_RECORDS = COUNT(*)
FROMDBO.DataImportFileImportHistory AS T1
WHERET1.FILENAME LIKE @FILENAME;
END;
I will make noted modification to the stored procedure with EXEC code. After doing this, how vulnerable is my SSIS package to a sql injection attack?
February 3, 2022 at 7:31 pm
It's not how you're using it or the fact that supposedly nothing can get to it. Not all attacks are from the outside world and there's no guarantee that someone won't use or move your code in the future.... even if it's you.
At the very least, you should check for illegal characters in the path name and a couple of the more common attack words. I make absolutely no claims that the following will catch everything because I'm NOT an SQL Injection expert but it will give you some ideas. Once you've done your own research, you could build a function to check the filepaths (and maybe other things).
Again, the following is just an example and hasn't been tested by the "pros" in SQL Injection. If you use this code and someone is still able to get in, I am NOT responsible because I told you that you need to get a pro at SQL Injection to have a look at it and approve it for use. Even a table name can be changed to have an index maintenance routine provide sysadmin privs to someone.
--===== This is the parameter
DECLARE @FileName VARCHAR(200) = 'C:\Temp\Encoding Time.csv' --Add some injection to this and see what happens
;
--===== This is similar to the code provided with some injection proofing added.
DECLARE @FilePath VARCHAR(200)
SELECT @FilePath = @FileName
WHERE @FileName NOT LIKE '%[^a-zA-Z0-9:\*. ]%'
AND CHARINDEX('REM ' , @FileName) = 0
AND CHARINDEX(' OR ' , @FileName) = 0
AND CHARINDEX(' UNION ', @FileName) = 0
;
--===== This displays the results for both variables
SELECT [@FileName] = @FileName
,[@FilePath] = ISNULL(@FilePath,'SQL Injection Attempt')
;
Now, go read Erland's article on the subject. Search for Injection there to begin to understand the issues and then get a pro to help you.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2022 at 7:44 pm
Thanks Michael and Jeff. I will do more research on this.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply