MSsQL2005; OPENROWSET, BLOB/IMAGE and STORED PROCEDURE problems

All,

I work with Microsoft SQL Server 2005 on windows XP professional.
I'd like to create stored procdure to add image to my database (jpg file).
I managed to do it using VARCHAR variable in stored procedure
and then using EXEC, but it don't work directly.

My Table definiton:
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Login] [char](10),
[Password] [char](20),
[Avatar] [image] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

My working solution using stored procedure:
ALTER PROCEDURE [dbo].[AddUser]
@.Login AS VARCHAR(255),
@.Password AS VARCHAR(255),
@.AvatarFileLocation AS VARCHAR(255),
@.UserId AS INT OUTPUT
AS
BEGIN
SET @.Query = 'INSERT INTO USERS ' + CHAR(13)
+ 'SELECT '''+ @.Login + ''' AS Login, ' + CHAR(13)
+ '''' + @.Password + ''' AS Password,' + CHAR(13)
+ '(SELECT * FROM OPENROWSET(BULK ''' + @.AvatarFileLocation + ''', SINGLE_BLOB) AS OBRAZEK)'
EXECUTE (@.Query)
SET @.UserID = @.@.IDENTITY
END

I'd like to use statement in the stored procdure:
ALTER PROCEDURE [dbo].[AddUser]
@.Login AS VARCHAR(255),
@.Password AS VARCHAR(255),
@.AvatarFileLocation AS VARCHAR(255),
@.UserId AS INT OUTPUT
AS
BEGIN
DECLARE
@.Query AS VARCHAR(MAX)

SET @.AvatarFileLocation = 'C:\hitman1.jpg'
INSERT INTO USERS
SELECT @.Login AS Login,
@.Password AS Password,
(SELECT * FROM OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB) AS OBRAZEK)

SET @.UserID = @.@.IDENTITY

END

It generates error:
Incorrect syntax near '@.AvatarFileLocation'.

My question is:
Why it does not work and how to write the stored procedure code to run this code without errors.

Thanks for any reply

You can't use a variable inside OPENROWSET.

What you are doing, in any case, IS VERY DANGEROUS. There

are many ways in which dynamic SQL is vulnerable to SQL

injection. Please read about it, so that you don't lose

everything you have when a malicious user joins your

site/forum with a password like

O',0x; delete from USERS where Password <> 'O';return 0;--

You might start reading here:

http://www.sommarskog.se/dynamic_sql.html

Steve Kass

Drew University

Michal1979@.discussions.microsoft.com wrote:

> All,

>

> I work with Microsoft SQL Server 2005 on windows XP professional.

> I'd like to create stored procdure to add image to my database (jpg

> file).

> I managed to do it using VARCHAR variable in stored procedure

> and then using EXEC, but it don't work directly.

>

> My Table definiton:

> CREATE TABLE [dbo].[Users](

> [UserID] [int] IDENTITY(1,1) NOT NULL,

> [Login] [char](10),

> [Password] [char](20),

> [Avatar] [image] NULL,

> CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED

> (

> [UserID] ASC

> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

>

> My working solution using stored procedure:

> ALTER PROCEDURE [dbo].[AddUser]

> @.Login AS VARCHAR(255),

> @.Password AS VARCHAR(255),

> @.AvatarFileLocation AS VARCHAR(255),

> @.UserId AS INT OUTPUT

> AS

> BEGIN

> SET @.Query = 'INSERT INTO USERS ' + CHAR(13)

> + 'SELECT '''+ @.Login + ''' AS Login, ' + CHAR(13)

> + '''' + @.Password + ''' AS Password,' + CHAR(13)

> + '(SELECT * FROM OPENROWSET(BULK ''' + @.AvatarFileLocation + ''',

> SINGLE_BLOB) AS OBRAZEK)'

> EXECUTE (@.Query)

> SET @.UserID = @.@.IDENTITY

> END

>

> I'd like to use statement in the stored procdure:

> ALTER PROCEDURE [dbo].[AddUser]

> @.Login AS VARCHAR(255),

> @.Password AS VARCHAR(255),

> @.AvatarFileLocation AS VARCHAR(255),

> @.UserId AS INT OUTPUT

> AS

> BEGIN

> DECLARE

> @.Query AS VARCHAR(MAX)

>

> SET @.AvatarFileLocation = 'C:\hitman1.jpg'

> INSERT INTO USERS

> SELECT @.Login AS Login,

> @.Password AS Password,

> (SELECT * FROM OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB) AS

> OBRAZEK)

>

>

> SET @.UserID = @.@.IDENTITY

>

> END

>

>

> It generates error:

> Incorrect syntax near '@.AvatarFileLocation'.

>

> My question is:

> Why it does not work and how to write the stored procedure code to run

> this code without errors.

>

> Thanks for any reply

>

>

>

>|||

NNTP User

Lot thanks for the repy,

The article link is very interesting.

I understand risks from using dynamic sql, but I'm not goiing to allow user call my stored procedure directly.

All arguments of the stored procedure will be validated and constructed my my application.

My real question is why can I use OPENROWSET function in stored procedure when I build the query

using varchar variable and then execute it, although I can't do it direclty using OPENROWSET in

stored procedure.

|||You cannot use a variable like this:

OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB)

The file name must be hard-coded, like this:

OPENROWSET(BULK 'C:\picture.jpg', SINGLE_BLOB)

SK

Michal1979@.discussions.microsoft.com wrote:

> NNTP User

>

>

> Lot thanks for the repy,

>

> The article link is very interesting.

>

> I understand risks from using dynamic sql, but I'm not goiing to allow

> user call my stored procedure directly.

>

> All arguments of the stored procedure will be validated and constructed

> my my application.

>

> My real question is why can I use OPENROWSET function in stored

> procedure when I build the query

>

> using varchar variable and then execute it, although I can't do it

> direclty using OPENROWSET in

>

> stored procedure.

>

>

>

>

>

>|||

NNTP User,

Yes,I can not use directly OPENROWSET function in strored procedure, but I can use dynamic sql.

My problem is not how to store image using stored procedure (I can do it, however I don't like the way I do it)

but why can't I do it directly.

By the way I tried to use sp_executesql to run the code but it didn't work as well - so for now the only way

to store image or file using stored procedure is dynamic sql.

But I'm still wandering WHY? Is it a bug or something like that?

|||

As Steve indicated, you will have to specify the filename as a literal in the OPENROWSET call. Otherwise you will have to use dynamic SQL to form the entire statement and execute it. And with dynamic SQL you will have protect against SQL injection attacks. Optionally, you can do the following for a bulk import process:

1. Create temporary table to hold the user accounts

2. Use the new BulkCopy managed API to stream the user data from the client to server

3. Write SP to dump the rows from temporary table to the main table

For adding or modifying single values, you can just have a SP with image parameter and manipulate the data in the table. There is no need to use OPENROWSET which requires a file (creation of file by client, server having permissions to access file, etc) among other things and parameterization is not straight-forward.

|||

Umachandar Jayachandran - MS,

I'd like to know if there is other than OPENROWSET form of loading images or binary files into MSSQL,

I could missed it in documentation. It is sure that there will be no possibility to sql injection using dynamic sql

in my case.

Probably I should mentioned it before, the problem I described is not critical for me, becouse I found solution.

The solution may not be ideal (especialy for me) but it still works. Anyway probably I'll use C# and ADO.NET to

perform put and get image from database.

Maybe you know why the only way to use OPENROWSET in stored procedure is to use dynamic sql.

Thanks