overwrite single quote in sql server 2005

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 Programming

overwrite single quote in sql server 2005

Author

Topic

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2010-08-12 : 07:25:45

Hi
i use this code:
declare @pname varchar(600)
set @pname='''OTC Ph2C GoLive-System Prep'','' OTC Phase 2C Rollouts-I'''
print @pname
select * from CSPO_ProjectMonthEndFinancials where TrackingNumber='LG891' and ProjectName in (@pname)

though @print contain 'OTC Ph2C GoLive-System Prep',' OTC Phase 2C Rollouts-I' value but it does not work in select query...can someone plz help me...

kalyan Ashis Dey

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-12 : 07:34:47

try this:


declare @values varchar(600)
set @values='''1'',''2'''
print @values

declare @sql nvarchar(max)
set @sql = 'select * from master.dbo.spt_values where type = ''P'' and number in (' + @values + ')'
exec sp_executesql  @sql

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2010-08-12 : 08:33:20

Thanx for ur reply...it works for my query..but actually i need your help to solve the below issue:

Alter Procedure sp_test
(
@tn as varchar(240),
@pn as varchar(600)
)
As

Select
TaskID,
Type,
TaskName,
From table_test
where TN=@tn
and PN in (' + @pn + ')

Group by

TaskID,
Type,
TaskName,
FixedCost,

execution of sp...
Exec sp_test 'abc', '''xyz'',''mno'''

but it does not work....can u plz tell me what i should do

kalyan Ashis Dey

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2010-08-12 : 08:47:23

Hey the above issue is also solved using the way u showed me....but is there any other way to solve the issue.

kalyan Ashis Dey

Kristen
Test

22859 Posts

Posted - 2010-08-12 : 09:05:24

Beware of SQL Injection risk when using dynamic SQL in this way.

"is there any other way to solve the issue"

You can use a "splitter" function to convert an @Variable into rows, and the JOIN that to your query. Also avoids the SQL Injection risk.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-12 : 10:41:14

Kristen,

how and where could in my example query injection happen? :)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-12 : 10:46:59

quote:
Originally posted by slimt_slimt

Kristen,

how and where could in my example query injection happen? :)

Refer this link

http://www.sommarskog.se/dynamic_sql.html

Read the section about "SQL Injection – a Serious Security Issue"

Kristen
Test

22859 Posts

Posted - 2010-08-12 : 11:12:55

quote:
Originally posted by slimt_slimt
how and where could in my example query injection happen? :)

declare @sql nvarchar(max)
DECLARE @values varchar(1000)
SET @values = '123,456); SELECT ''Something malicious'';--'
set @sql = 'select * from master.dbo.spt_values where type = ''P'' and number in (' + @values + ')'
PRINT @sql

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-13 : 10:09:17

These may help
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/14/derived-table-new-approach-to-avoid-sql-injection.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources