December 5, 2012 at 7:31 pm
ALTER PROCEDURE [dbo].[usp_delete] @tablename sysname, @pid int
,@pidname varchar(10)
AS
DECLARE @sql varchar(500)
SET @sql = 'delete from ' + @tablename + ' where '+ @pidname + ' = '+ @pid
Conversion failed when converting the nvarchar value 'delete from m_customer where cid = ' to data type int.
i get the above error
if i convert pid int to varchar i dont get error but the recod is not deleted
whts the best way toprevent sql injection and have database secured
December 6, 2012 at 12:02 am
To make your statement work you have to convert @pid in the string concatenation.
SET @sql = 'delete from ' + @tablename + ' where '+ @pidname + ' = '+ cast( @pid as varchar(50) )
To avoid sql injection you should not use dynamic statements like this at all. Write a separate delete procedure for every table where you want to delete data from.
December 6, 2012 at 3:39 am
first ensure that the value you are getting in @pid is int or varchar. If column data type is varchar then use CAST for it.
Hope, It will works for you 😛
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 11, 2012 at 3:32 am
Sean Pearce (12/11/2012)
ALTER PROCEDURE [dbo].[usp_delete]
@tablename sysname,
@pid int,
@pidname varchar(10)
AS
DECLARE @sql NVARCHAR(MAX),
@params NVARCHAR(MAX);
SELECT@sql = 'DELETE FROM [' + @tablename + '] WHERE ' + @pidname + ' = @pid;',
@params = '@pid INT';
Don't forget to test your sp by executing:
EXEC dbo].[usp_delete]
@tablename = 'sometable',
@pid int = 1,
@pidname = '1 = 1 OR 1 '
😎
It's a good idea to always protect your dynamic sql from injection
December 11, 2012 at 3:36 am
I strongly recommend you do not go this approach of generic delete procedures.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 11, 2012 at 7:15 am
Eugene Elutin (12/11/2012)
Sean Pearce (12/11/2012)
ALTER PROCEDURE [dbo].[usp_delete]
@tablename sysname,
@pid int,
@pidname varchar(10)
AS
DECLARE @sql NVARCHAR(MAX),
@params NVARCHAR(MAX);
SELECT@sql = 'DELETE FROM [' + @tablename + '] WHERE ' + @pidname + ' = @pid;',
@params = '@pid INT';
Don't forget to test your sp by executing:
EXEC dbo].[usp_delete]
@tablename = 'sometable',
@pid int = 1,
@pidname = '1 = 1 OR 1 '
😎
It's a good idea to always protect your dynamic sql from injection
You can offer some protection by wrapping the execution statement.
ALTER PROCEDURE [dbo].[usp_delete]
@tablename sysname,
@pid int,
@pidname varchar(10)
AS
DECLARE @sql NVARCHAR(MAX),
@params NVARCHAR(MAX);
SELECT@sql = 'DELETE FROM [' + @tablename + '] WHERE ' + @pidname + ' = @pid;',
@params = '@pid INT';
IF EXISTS(SELECT * FROM sys.columns WHERE name = @pidname AND OBJECT_NAME(object_id) = @tablename)
BEGIN
EXEC sp_executesql @sql, @params, @pid = @pid;
END;
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply