dynamic SQL update command

Question

I am using MSSQL 2016,

I need to be able to update a row on a table dynamically.

I got a stored procedure :

CREATE PROCEDURE sp_lookupData_UpdatelookupValues
 (
 @FullTableName nvarchar(50), 
 @Id nvarchar(10),
 @Name nvarchar(50),
 @Description nvarchar(50)
 )
AS
BEGIN

DECLARE @Cmd nvarchar(150) = N'UPDATE ' + @FullTableName + ' SET Name = ' +  @Name  + ', Description = ' + @Description + ' WHERE ID = ' + @Id + '';

EXECUTE sp_executesql @Cmd;

END

The problem is that Name and Description values are passed into the @Cmd like this :

UPDATE TABLE_NAME SET Name = Private, Description = Default WHERE ID = 1

Instead of 'Private' and 'Default'.

The result is an error where Private is being counted as a column which doesnt exist ( because of the bad format ).

Invalid column name 'Private'.

Hybris95 · Accepted Answer · 2017-07-03 15:10:39Z

5

Put the quotes yourself

Use single quotes around Private and Default.
And since you are using dynamic querying, you have to double the single quotes to escape them.

DECLARE @Cmd nvarchar(150) = N'UPDATE ' + @FullTableName + ' SET Name = ''' +  @Name  + ''', Description = ''' + @Description + ''' WHERE ID = ' + @Id + '';

Also make sure you try the next solution, since the first one is SQL Injection compatible.

Use sp_executesql parameters

You can also use the parameters inside your @Cmd without doing the concatenation yourself but by passing the parameters to sp_executesql

Also I suggest you to QUOTENAME the @FullTableName parameter in case of spaces inside table's name.

DECLARE @Cmd nvarchar(150) = N'UPDATE QUOTENAME(@FullTableName) SET Name = @Name, Description = @Description WHERE ID = @Id;'
EXEC sp_executesql @Cmd, @FullTableName, @Name, @Description, @Id;

The advantage doing so, is you avoid any parameters not checked by the application to be able to do SQL Injection.

Reference :

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql

edited Jul 3, 2017 at 15:10

answered Jul 3, 2017 at 15:02

Hybris95

2,4202 gold badges20 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stefan Steiger Over a year ago

If you use such code, you should secure against SQL-injection by replacing ' with ''. And if your concept requires doing something like this, you should question the concept in the first place.

HoneyBadger · Accepted Answer · 2017-07-03 15:03:18Z

0

You have to add the quotes yourself. I prefer to use QUOTENAME to keep all the quotes recognizable:

QUOTENAME(@FullTableName, '''')

answered Jul 3, 2017 at 15:03

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2017-07-03 15:09:53Z

You cannot use parameters for identifiers. But, you can use parameters for values:

DECLARE @Cmd nvarchar(150) = N'
UPDATE ' + @FullTableName + '
    SET Name = @Name,
        Description = @Description 
WHERE ID = @Id';

EXECUTE sp_executesql @Cmd,
        N'@Name nvarchar(50), @Description nvarchar(50), @Id nvarchar(10)',
        @Name = @Name, @Description = @Description, @Id = @id;

Unfortunately, the dynamic table name still poses risks, both in terms of SQL injection and syntax errors. I am guessing this is "controlled" code, not code with user input, so the risks might be acceptable. However, you probably should use quotename().

That brings up another issue which is probably the crux of the problem. Why do you have multiple tables with the same columns? Could these -- should these -- all be stored in a single table? This type of code calls into question aspects of the data model.

Collectives™ on Stack Overflow

dynamic SQL update command

3 Answers 3

Put the quotes yourself

Use sp_executesql parameters

Reference :

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Put the quotes yourself

Use sp_executesql parameters

Reference :

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related