Dynamic Sql - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic Sql

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-02-28 : 06:28:02

Hello everyone,

i have created a dynamic sql

create proc pr_dynSql
(
@Param nvarchar(200)
)
as
begin
set @query = 'select * from table1 where '
set @query = @query + @param
exec(@query)
end

this proc works fine. the input passed is the condition
exec pr_dynSql 'firstname like ''sabarish'''
works fine

but when i insert data as
exec pr_dynSql 'firstname like ''neil''obrien'''
i searched for name neil'obrien (note:single quotes over l)
gives error. probably some sort of sql injection how to overcome this .

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-28 : 06:32:04

Before executing query, print the query and see where lies the problem.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-02-28 : 06:58:34

The prob occurs because i have used neil'obrien as input

so single quotes makes the problem. pls tell how to solve

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-28 : 07:13:45

[code]exec pr_dynSql 'firstname like ''neil''''obrien'''[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-28 : 07:16:49

Try this:-
exec pr_dynSql 'firstname like ''nielo''''brien'''

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-02-28 : 07:22:36

thanks it works , but is not possible to do it in query

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-28 : 07:25:31

quote:
Originally posted by dineshrajan_it

thanks it works , but is not possible to do it in query

Can you elaborate on that?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-28 : 07:34:32

Make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-28 : 09:08:15

NEVER do what you are trying -- always use parameters and sp_executeSQL if you MUST use dynamic SQL. In this case, and in 99% of cases, you don't and you shouldn't.

- Jeff
http://weblogs.sqlteam.com/JeffS

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-28 : 09:11:05

this proc should be called pr_PleaseTakeOverMyServer instead of pr_dynSql

elsasoft.org

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-02-28 : 22:01:14

I agree. What's with all this dynamic SQL stuff? I've been doing database stuff for years and have rarely needed dynamic SQL. Where is it all coming from recently? Is it a web thing?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-29 : 05:49:19

quote:
Originally posted by LoztInSpace

I agree. What's with all this dynamic SQL stuff? I've been doing database stuff for years and have rarely needed dynamic SQL. Where is it all coming from recently? Is it a web thing?

Newbies start to write dynamic sql for conditional where clauses
Thereafter everything is dynamic sql to them

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources