[Deeko]

I am having some trouble passing some parameters to a stored procedure. Basically I want to pass three values as seen below, with one of the values to act as a variable for a column. I cannot get it to work and need some help as I have been at it for most of the day. The error message I get when I test it with the values:

@id=10
@columnName = Eating
@facebookuser = Derek

Error message:

Conversion failed when converting the nvarchar value 'UPDATE ImageAttributes SET FacebookUser = Derek,[Eating] = 1 where ImageID = ' to data type int.

The datatypes in the database in relation to these parameters are as follows:

ImageID = int
Eating=varchar(50)
FaceBookUser=varchar(50)

Here is the stored procedure:

USE [CatCam]
GO
/****** Object:  StoredProcedure [dbo].[update_imagerecord]	Script Date: 06/15/2008 18:33:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[update_imagerecord]

@id int,
@columnName varchar(50),
@facebookuser varchar(50)

 
AS
DECLARE @command varchar(1000)


SET @command= 'UPDATE ImageAttributes SET FacebookUser = ' + @facebookuser + ',' + QUOTENAME(@columnName) + ' = 1 where ImageID = ' + @id + ''


EXEC (@command)

Any help or suggestions would be much appreciated.

Thanks

[baavgai]

I see three errors, but you're real close. One, use an nvarchar for your variable; not a big deal, but it's nice. Two, cast your int, which is the error you're getting. Three, quote your char value, which is the error you'll get after you fix two.

DECLARE @command nvarchar(1000)

SET @command= 'UPDATE ImageAttributes'
	+ ' SET FacebookUser = ''' + @facebookuser + ''''
	+ ',' + QUOTENAME(@columnName) + ' = 1'
	+ ' where ImageID = ' + cast(@id as nvarchar)

EXEC (@command)

Please note that dynamic sql is to be avoided if you can. Particularly if this is a web app, it's SQL injection waiting to happen. If that column name can be fixed, you'll be much better off. e.g.

UPDATE ImageAttributes
	SET FacebookUser = @facebookuser, Eating = 1
	WHERE ImageID = @id

Hope this helps.

This post has been edited by baavgai: 15 June 2008 - 12:41 PM

[Deeko]

Thank you for the help, I all up and running now, also I thought the use of QUOTENAME on the variable as the column name helped against SQL injection attacks?

This post has been edited by Deeko: 15 June 2008 - 01:48 PM

[baavgai]

QUOTENAME willl help with making sure the column name conforms to SQL Server naming conventions. However, what if I passed a name like so:

@facebookuser='DIE!''; delete ImageAttributes;'

More info here: http://xkcd.com/327/

You can can make a case that the name you pass has already been sanitized or the user never gets to change that bit. Still, it's something to be aware of.

[Deeko]

baavgai, on 15 Jun, 2008 - 02:08 PM, said:

QUOTENAME willl help with making sure the column name conforms to SQL Server naming conventions. However, what if I passed a name like so:

@facebookuser='DIE!''; delete ImageAttributes;'

More info here: http://xkcd.com/327/

You can can make a case that the name you pass has already been sanitized or the user never gets to change that bit. Still, it's something to be aware of.

Thanks for the heads-up on that Baavgai I wonder if I dare venture to ask for some more advice? - Can you sirect me on how to apply an 'IF' statement to the update command that checks if their is already a value in the facebookuser column for the current record and if so will then insert a new record with the same ImageID (the ID in this table is a foreign key and can be duplicated in this instance).

[baavgai]

You can use an if. I believe you're looking for something like this:

CREATE PROCEDURE [dbo].[update_imagerecord]
	@ImageID int,
	@FacebookUser varchar(50),
	@Eating bit
as begin
	if exists(
		select *  from ImageAttributes 
			WHERE ImageID = @ImageID and FacebookUser=@FacebookUser
		)
		UPDATE ImageAttributes
			SET Eating = @Eating
				WHERE ImageID = @ImageID and FacebookUser=@FacebookUser
	else
		INSERT into ImageAttributes(ImageID, FacebookUser, Eating)
			values (@ImageID, @FacebookUser, @Eating)
end
go

Depending on what you're doing, a slightly more elegant way to do this is to simply check if the update worked.

CREATE PROCEDURE [dbo].[update_imagerecord]
	@ImageID int,
	@FacebookUser varchar(50),
	@Eating bit
as begin
	UPDATE ImageAttributes
		SET Eating = @Eating
			WHERE ImageID = @ImageID and FacebookUser=@FacebookUser
	if @@ROWCOUNT=0
		INSERT into ImageAttributes(ImageID, FacebookUser, Eating)
			values (@ImageID, @FacebookUser, @Eating)
end
go