Insert file to sql server without front end using Stored Procedure

Question

Insert file to sql server without front end using Stored Procedure

as773233

SSC Enthusiast

Points: 144
More actions
February 27, 2016 at 11:00 am

#324553

I am trying to insert file through sql. I use following query.
INSERT INTO [dbo].[Attachments]
(FileName, FileBinary)
SELECT 'non-date-in-sql-server-column',
BulkColumn FROM OPENROWSET(
Bulk 'C:\Users\Pictures\Picture.JPG', SINGLE_BLOB) AS BLOB
Its working fine.
I want to write the procedure that take dynamic path. Its giving me error that I cannot take Filebinary in addin. Which is datatype varbinary. What is the best way to do ?
I have done following but its not taking properly binary value.
DECLARE @SQLString NVARCHAR(MAX)
SET @SQLString = 'SELECT ' + '''' +@Filename +'''' + ' AS Name,' + 'FileBinary
FROM OPENROWSET(BULK N''' + @ImagePath + ''',SINGLE_BLOB) AS FileBinary(FileBinary);'
Insert Into Attachments
(
ApplicantID,
FileName,
FileBinary
)
Values
(
@ApplicantID ,
@FileName,
Convert(varbinary(max),@SQLString)
)

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

In the second query, you build a query string and then insert that string into a table. I assume that you actually meant to execute that string.

Before going down this path further, I urge you to read up on dynamic SQL. Here is a (lengthy but) great resource: http://www.sommarskog.se/dynamic_sql.html. If you are not aware of the potential problems with dynamic SQL, you are opening up a large security hole in your system.

Back to your question. Since the first T-SQL works fine, all you need to do is generate T-SQL that looks that way but with the filename replaced. So you could use something like this (untested):

SET @SqlString = 'INSERT INTO [dbo].[Attachments]

(FileName, FileBinary)

SELECT ''non-date-in-sql-server-column'',

BulkColumn FROM OPENROWSET(

Bulk ''' + @ImagePath + ''', SINGLE_BLOB) AS BLOB;';

PRINT @SqlString;

--EXEC (@SqlString);

The code as posted will only show the SQL to execute without executing it, this is always wise to do on a first try so you can verify that the generated SQL is correct. If it is, uncomment the EXEC line and it will execute the statement.

WARNING: If the source of @ImagePath is not fully controlled, then the question is not whether but when a hacker will show up and inject nasty T-SQL in your query, and it will be executed. For instance, entering ') x;shutdown with nowait;-- as the @ImagePath WILL shutdown your server - and that's just child's play compared to what actual hackers would do if they find this backdoor.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Jeff Moden SSC Guru Points: 1004616 More actions · Answer 2

Hugo Kornelis (2/28/2016)
Before going down this path further, I urge you to read up on dynamic SQL. Here is a (lengthy but) great resource: http://www.sommarskog.se/dynamic_sql.html. If you are not aware of the potential problems with dynamic SQL, you are opening up a large security hole in your system.
WARNING: If the source of @ImagePath is not fully controlled, then the question is not whether but when a hacker will show up and inject nasty T-SQL in your query, and it will be executed. For instance, entering ') x;shutdown with nowait;-- as the @ImagePath WILL shutdown your server - and that's just child's play compared to what actual hackers would do if they find this backdoor.

Dynamic SQL with concatenated replacements isn't just a security problem, it's also ugly and can be difficult to get the quotes right. With that in mind, here's how I develop dynamic SQL to work.

1. Write non-dynamic SQL first. Run it and debug it until you're sure it has no errors.

2. Tokenize places where single quotes are needed with double-quotes being careful to observe any places where double quotes are actually treated as required literals (very infrequent, BTW).

3. Tokenize places where you'd normally have to do some form of "broken literal concatenation".

4. Add the necessary REPLACEs to replace the tokens in the dynamic SQL. This can also help security a lot by using QUOTENAME in those places that require single quotes or brackets can/should be tolerated/used.

So, referring to Hugo's example, what we end up with would be something like this...

SELECT @SqlString = REPLACE(REPLACE('

INSERT INTO [dbo].[Attachments]

(FileName, FileBinary)

SELECT "non-date-in-sql-server-column",

.BulkColumn

FROM OPENROWSET(Bulk <<@ImagePath>>, SINGLE_BLOB) AS BLOB;'

,'"' ,'''') --End of second REPLACE

,'<<@ImagePath>>',QUOTENAME(@ImagePath,'''') --End of first REPLACE

;

PRINT @SqlString;

--EXEC (@SqlString);

To be sure, the use of QUOTENAME is not a panacea of proper security. It's just one piece. If you work with file paths and file names, you should also write a function to delouse any such inputs for possible DOS injection. While you're doing that, include things that would identify SQL injection as another layer of protection. Never return errors from such functions and don't let them fail. If some form of injection is present, return an empty string so that you don't give potential attackers any information that may help them with their attack.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)