Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

error in stored procedure Expand / Collapse
Author
Message
Posted Wednesday, December 5, 2012 7:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, Visits: 207
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
Post #1393292
Posted Thursday, December 6, 2012 12:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 5:21 AM
Points: 197, Visits: 730
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.
Post #1393340
Posted Thursday, December 6, 2012 3:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,893, Visits: 2,329
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/
Post #1393413
Posted Tuesday, December 11, 2012 1:15 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:57 AM
Points: 906, Visits: 2,868
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';

EXEC sp_executesql @SQL, @Params, @pid = @pid;





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1394927
Posted Tuesday, December 11, 2012 3:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:50 AM
Points: 2,856, Visits: 5,124
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';

EXEC sp_executesql @SQL, @Params, @pid = @pid;




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



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1394998
Posted Tuesday, December 11, 2012 3:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 39,866, Visits: 36,206
I strongly recommend you do not go this approach of generic delete procedures.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1395002
Posted Tuesday, December 11, 2012 7:15 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:57 AM
Points: 906, Visits: 2,868
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';

EXEC sp_executesql @SQL, @Params, @pid = @pid;




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;





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1395111
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse