-1

I've written simple stored procedure and executing using sp_executesql to tackle with quotation error and sql injection as well but when i pass a single quote in parameter still it shows me Unclosed quotation mark after the character string ''.

alter procedure dbo.quote_test
(
    @quoteid int
)
as
begin
declare @sqlstring as nvarchar(max)
declare @paramdef as nvarchar(100)
set @sqlstring = 'select * from quote where quote_id = @quoteid';
set @paramdef = N'@quoteid int';
exec sp_executesql @sqlstring,@paramdef, @quoteid
end

exec dbo.quote_test 10'
Vishwanath Dalvi
  • 35,388
  • 41
  • 123
  • 155
  • This page comes up high on Google searches... could you please select which of the below answered the problem... or tell us your solution? – Doug_Ivison Mar 10 '15 at 20:29

2 Answers2

0

I'm not sure what you are trying to accomplish, but the stored procedure takes an integer for an argument. So, this will work:

exec dbo.quote_test 10

This should not work:

exec dbo.quote_test 'abc'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • But what if I'm taking string as a parameter sp_execute will escape it? – Vishwanath Dalvi May 16 '14 at 03:08
  • Like this exec dbo.quote_test '10'' gives quotation error. – Vishwanath Dalvi May 16 '14 at 03:09
  • It looks like you have defined your function to accept an INT as the argument. Like @Gordon says, you would need to pass it in as an INT and not within quotes -> '10' – Matthew at Critical Cognition May 16 '14 at 03:21
  • @MatthewatCriticalCognition . . . Well, if you pass in `'10'`, then SQL Server will probably convert that to an int. If you pass in `'10a'`, you should get an error. – Gordon Linoff May 16 '14 at 03:22
  • @GordonLinoff - you are correct. mr_eclair - I think your issue might be that you have a single quote to begin your parameter but a double quote or double tick to end it. – Matthew at Critical Cognition May 16 '14 at 03:24
  • @mr_eclair . . . The quotes have to balance in a SQL expression. In `exec dbo.quote_test '10''`, you have an unclosed string. If you had `exec dbo.quote_test '10''a'`, then the argument to the function would be `10'a`, with a single quote in the string -- and an error because the function wants an integer. – Gordon Linoff May 16 '14 at 03:28
0

Could be due to your EXEC statement that has the quote.

exec dbo.quote_test 10'

Vishal Gamji
  • 59
  • 6