Escpe single quotes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Escpe single quotes

Author

Topic

ravysters
Starting Member

1 Post

Posted - 2010-08-17 : 14:18:32

Hi.. I am relatively new to sql server programming. I have written a stored procedure which looks like this..

If Exists (Select Name From SysObjects Where Upper(Name) = 'InsertText' And Type = 'P')
Drop Procedure InsertText

Go

CREATE PROCEDURE InsertText( @vKeyWord varchar(200))
As
Set NoCount On

Declare
@vStrSql nvarchar(4000)

Begin

SET QUOTED_IDENTIFIER ON
SET @vStrSql = 'Insert into testtab values(1,''' + @vKeyWord + ''')'

exec sp_executesql @vStrSql

End
Go

i just want to insert some text into a table.. this text might contain single quotes and i do want to insert singles into my database.. how can i do that in sql server..

thanks in advance for your replies...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2010-08-17 : 16:26:13

use quotename...

quotename('text','''')

use replace function
replace the single quote with two single quotes...

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2010-08-18 : 06:14:50

Your method is open to SQL Injection abuse (although if you use quotename on EVERY SINGLE PARAMETER, EVERY TIME, WITHOUT FAIL!!!, you'll be fine.

Either of these methods would be preferable to the route you are headed down IMHO

Simple INSERT statement:


CREATE PROCEDURE InsertText(	@vKeyWord	 varchar(200))
As
Set NoCount On

Begin	

	Insert into testtab values(1, @vKeyWord)

End 
Go

Parametrise dynamic SQL


CREATE PROCEDURE InsertText(	@vKeyWord	 varchar(200))
As
Set NoCount On

Declare 
@vStrSql	 nvarchar(4000)

Begin	

SET QUOTED_IDENTIFIER ON
SET @vStrSql = 'Insert into testtab values(1, @vKeyWord)'

exec sp_executesql @vStrSql, N'@vKeyWord varchar(200)', @vKeyWord

End 
Go

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-19 : 09:00:47

quote:
Originally posted by jen

use quotename...

quotename('text','''')

use replace function
replace the single quote with two single quotes...

--------------------
keeping it simple...

Note that Quotename wont work for data having more than 128 characters. Better approach is to replace single quote with double single quotes

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-08-19 : 10:57:48

Better approach, IMHO, is not to do it that way!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-19 : 11:15:40

quote:
Originally posted by Kristen

Better approach, IMHO, is not to do it that way!

Yes. Re-reading the original question, it is as simple as

Insert into testtab values(1,@vKeyWord)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources