June 5, 2020 at 12:22 pm
All,
My code is as follows:
declare @xml xml
declare @filename varchar(255)
declare @bulkcommand nvarchar(1000)
set @filename='\\storage\GroupC\data.gml'
set @bulkcommand='SELECT @xml=bulkcolumn FROM OPENROWSET (BULK ' + QUOTENAME(@filename,'''') + ', SINGLE_CLOB) AS xCol;'
print @bulkcommand
--option A
SELECT @xml=bulkcolumn FROM OPENROWSET (BULK '\\storage\GroupC\data.gml', SINGLE_CLOB) AS xCol;
--option B
exec sp_executesql @bulkcommand, N'@xml xml output, @filename varchar(255)',@xml=@xml,@filename=@filename
--Rest of t-sql
create table #xmldata(roadid varchar(255), name varchar(255),ItemType varchar(255), startnode varchar(255), endnode varchar(255),roadlength integer, CentreLinePoints varchar (4000));
WITH XMLNAMESPACES('http://namespaces.os.uk/product/1.0' as os,'http://namespaces.os.uk/Open/Roads/1.0' as road, 'http://www.opengis.net/gml/3.2' as gml,'urn:x-inspire:specification:gmlas:Network:3.2' as net,'http://www.w3.org/1999/xlink' as xlink )
SELECT
T.c.value ('@gml:id[1]','varchar(255)') AS id,
T.c.value ('road:name1[1]','varchar(255)') AS name,
T.c.value ('road:roadFunction[1]','varchar(255)') AS ItemType,
right(T.c.value ('net:startNode[1]/@xlink:href','varchar(255)'),len(T.c.value ('net:startNode[1]/@xlink:href','varchar(255)'))-1) AS StartNode,
right(T.c.value ('net:endNode[1]/@xlink:href','varchar(255)'),len(T.c.value ('net:endNode[1]/@xlink:href','varchar(255)'))-1) AS EndNode,
T.c.value ('road:length[1]','varchar(255)') AS length,
T.c.value ('net:centrelineGeometry[1]','varchar(1000)') as CentreLinePoints
FROM @xml.nodes('/os:FeatureCollection/os:featureMember/road:RoadLink') T(c)
--insert into RoadPosPoints(roadid,easting,northing)
select dbo.splitroadpoints.id, easting,northing from #xmldata
cross apply dbo.splitroadpoints (#xmldata.roadid,#xmldata.centrelinepoints)
order by id asc
select roadid, name,itemtype,startnode,endnode, roadlength,CentreLinePoints from #xmldata
If I use option A it returns data.
if I use option B it returns empty tables
The output from the print statement is
SELECT @xml=bulkcolumn FROM OPENROWSET (BULK '\\storage\GroupC\data.gml', SINGLE_CLOB) AS xCol;
I can't work out why option B returns empty tables? Possibly something to do with the variable configuration on the sp_executesql statement but I can't work it out.
The eventual plan is that @Filename will be the parameter in an SSIS package so I don't think there is any risk of SQL injection through the use of dynamic SQL in this case?
Thanks
June 5, 2020 at 4:21 pm
I guess I am confused - if you are going to be using SSIS then why would you need to use OPENROWSET to load the data into a table? SSIS can be setup to load XML files and parse them as needed - which would be much easier than dynamically creating a BULK load in T-SQL.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 5, 2020 at 5:39 pm
Quick and easy fix to this - need to mark the variable as an OUTPUT variable in the parameter assignment part of sp_executesql.
In your EXEC statement, change:
@xml=@xml
to
@xml=@xml OUTPUT
Something to note though - you don't need to pass the second parameter to the sp_executesql command as that parameter isn't used in the query (as visible by the output of the PRINT statement).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 5, 2020 at 5:51 pm
The eventual plan is that @Filename will be the parameter in an SSIS package so I don't think there is any risk of SQL injection through the use of dynamic SQL in this case?
"It depends". How is that variable being populated? I know you said from the SSIS package but that's not what I mean. I'm talking about where the data that's being used to populate the variable is coming from.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 5, 2020 at 7:50 pm
All,
Thank you for your help. I've replied to each of you below. Apologises if I've missed anything.
Quick and easy fix to this - need to mark the variable as an OUTPUT variable in the parameter assignment part of sp_executesql.
In your EXEC statement, change:
@xml=@xml
to
@xml=@xml OUTPUT
Something to note though - you don't need to pass the second parameter to the sp_executesql command as that parameter isn't used in the query (as visible by the output of the PRINT statement).
Thank you. I tried several combinations of using output, before posting, but obviously I missed the correct one 🙁
I also hadn't noticed that I don't need the second parameter, I think I included it as part of one of my attempts to fix the issue and forgot to remove it. Thanks for advising me.
I guess I am confused - if you are going to be using SSIS then why would you need to use OPENROWSET to load the data into a table? SSIS can be setup to load XML files and parse them as needed - which would be much easier than dynamically creating a BULK load in T-SQL.
The XML file is too complicated for the XML source function (or I can't get it to accept it). I configured it using a script component and that worked. I was interested to see if it would be quicker in t-sql and, based on a few test files, it seems to be a lot quicker. I am therefore interested in seeing if using the above t-sql in an execute sql task would be quicker for all the files. It also gave me the chance to learn how to read XML files in T-SQL.
I'm not saying I've taken the right approach, I welcome any advice, the above is only meant as an explanation of my reasoning. I'm fairly new to SSIS so I'm working my way through different ways of doing things.
as_1234 wrote:The eventual plan is that @Filename will be the parameter in an SSIS package so I don't think there is any risk of SQL injection through the use of dynamic SQL in this case?
"It depends". How is that variable being populated? I know you said from the SSIS package but that's not what I mean. I'm talking about where the data that's being used to populate the variable is coming from.
Sorry I should have been more detailed in my question. The variable is the collection variable for a foreach loop which is looping through the files in a folder. I was thinking that as SSIS provides the content of the variable it is protected. However your question made me reaslise that it might be possible to use the filename to send something problematic into SQL? Advice is always appreciated but I will also do some more reading and see if I can answer my own question.
Thanks
June 5, 2020 at 10:06 pm
Sorry I should have been more detailed in my question. The variable is the collection variable for a foreach loop which is looping through the files in a folder. I was thinking that as SSIS provides the content of the variable it is protected. However your question made me reaslise that it might be possible to use the filename to send something problematic into SQL? Advice is always appreciated but I will also do some more reading and see if I can answer my own question.
I suppose someone could write such a file name although I don't know what would be necessary. I do reject anything that could contain SQL or DOS injection by checking that the file names only have letter, numbers, underscores, backslashes, and colons. On a rare occasion, depending on the situation, I'll also allow spaces or dashes. For anything else, I capture it and look at it later. I prefer to err on the side of caution for such things (I'd rather have a job fail that to suffer an attack) whether they're public facing or not (there IS such a thing as an "insider" attack).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply