forming a dynamic query
-
have a sp with 4 parameters the values of this parameters is obtained from application
currently i have
@transfrmdt date,
@transtodt date,
@cid integer,
@Type char(1)
DEClare @strqry varchar(max)
DEClare @STR varchar(max)
If (@Type<>'')
BEGIN
SET@strqry= @strqry +' and type='''+@Type+''''
END
If (@cname<>'')
BEGIN
SET@strqry= @strqry +'select name from Mas_C where cid='''+@cid+''''
END
SET @STR =' SELECT convert(varchar (10),transdt,111)as Transdt,amt
FROM Transcation WHERE 1=1
and Transdt between convert(varchar (10),@transfrmdt,111) and convert(varchar (10),@transtodt,111) '
+@strqry
print @STR
exec(@str)
SELECT sum(Amt) as TotalAmt
FROM Transcation WHERE Transdt between convert(varchar (10),@transfrmdt,111) and convert(varchar (10),@transtodt,111)
i am not getting the result
-
ssurekha2000 (5/1/2014)
have a sp with 4 parameters the values of this parameters is obtained from applicationcurrently i have
@transfrmdt date,
@transtodt date,
@cid integer,
@Type char(1)
DEClare @strqry varchar(max)
DEClare @STR varchar(max)
If (@Type<>'')
BEGIN
SET@strqry= @strqry +' and type='''+@Type+''''
END
If (@cname<>'')
BEGIN
SET@strqry= @strqry +'select name from Mas_C where cid='''+@cid+''''
END
SET @STR =' SELECT convert(varchar (10),transdt,111)as Transdt,amt
FROM Transcation WHERE 1=1
and Transdt between convert(varchar (10),@transfrmdt,111) and convert(varchar (10),@transtodt,111) '
+@strqry
print @STR
exec(@str)
SELECT sum(Amt) as TotalAmt
FROM Transcation WHERE Transdt between convert(varchar (10),@transfrmdt,111) and convert(varchar (10),@transtodt,111)
i am not getting the result
I take it that print @STR does not give you the query you are looking for. I suspect that you need to look at the order in which you are building up the string.
Initially you SET@strqry= @strqry +' and type='''+@Type+'''' then you set it to 'select name from Mas_C where cid='''+@cid+''''
From what I can see you need to swap the order of these, also it might help to set a value for @strqry then add any "where " filters later.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx -
swap what?
i am not getting any results
-
What is the result of print @STR ?
I think it will be empty as you are building @strqry up with a potential outcome that it will be null. When you add the to @STR you will end up with a null string and therefore no query to run.
The following is one way round the problem
@transfrmdt date,@transtodt date,
@cid integer,
@Type char(1)
DEClare @strqry varchar(max)
DEClare @STR varchar(max)
set @strqry= 'select name from Mas_C where 1 = 1 '
If (@Type<>'')
BEGIN
SET@strqry= @strqry +' and type='''+@Type+''''
END
If (@cname<>'')
BEGIN
SET@strqry= @strqry +' and cid='''+@cid+''''
END
SET @STR =' SELECT convert(varchar (10),transdt,111)as Transdt,amt
FROM Transcation WHERE 1=1
and Transdt between convert(varchar (10),@transfrmdt,111) and convert(varchar (10),@transtodt,111) '
+@strqry
print @STR
exec(@str)
SELECT sum(Amt) as TotalAmt
FROM Transcation WHERE Transdt between convert(varchar (10),@transfrmdt,111) and convert(varchar (10),@transtodt,111)
(Edited to add)
I ran the query above (with @type and @cname as empty) and got the following:-
SELECT CONVERT(VARCHAR(10), transdt, 111) AS Transdt ,amt
FROM Transcation
WHERE 1 = 1
AND Transdt BETWEEN CONVERT(VARCHAR(10), @transfrmdt, 111)
AND CONVERT(VARCHAR(10), @transtodt, 111)
SELECT name
FROM Mas_C
WHERE 1 = 1
I'm not sure if you wanted two separate queries, or if you wanted to have the second as a filter for name.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx -
Two major issues with the way you are writing this. The first and the biggest issue is this is wide open to sql injection. You should NEVER directly execute a parameter. I realize that with the datatypes of the parameters you are not at great risk currently but this approach is extremely dangerous. You can and should parameterize your dynamic sql instead of building up a string and executing it.
Secondly you have the potential for some performance issues. This is a type of "catch all" query. Take a look at this post from Gail. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
She explains clearly how to deal with this type of query and properly parameterize the dynamic sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
You forgot to initialise @strqry to something other than null. Since it ultimately gets concatenated to everything, the end result is null.
You've got three completely separate queries in there. What are you trying to do?
How about posting what you expect to see from the PRINT statement?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
i think query is unclear
Transcation tbl has transcationdate, amount and cid
Mas_C has cid and cname
Transcation date displayed will be from & todate passed from the application
1) the output needed is in all conditions
transcationdate, amount
if @<>type''
output shld be transcationdate, amount
if @cid<>''
output shld be transcationdate, amount cname
-
Please post your table structure and some sample data and what is your desired output
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
ssurekha2000 (5/1/2014)
i think query is unclearTranscation tbl has transcationdate, amount and cid
Mas_C has cid and cname
Transcation date displayed will be from & todate passed from the application
1) the output needed is in all conditions
transcationdate, amount
if @<>type''
output shld be transcationdate, amount
if @cid<>''
output shld be transcationdate, amount cname
It's unclear what you want to do. You almost certainly don't need dynamic sql. How many result sets are you expecting from this? Try to write the query(ies) without using dynamic sql and post back if it doesn't work.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
its not necessary to have dynamic sql
i had tried with normal query but didnot work so tried iwith dynamic sql
the out needed is display
1) date,amt with the given date range
2) conditional where clause ie if @type<>'' then
select * from tbl where date between @frmdt and @todt and type=@type
if @cid<>'' then
select * from tbl where date between @frmdt and @todt and cid=@cid
if both not blank
select * from tbl where date between @frmdt and @todt and cid=@cid and type=@type
and both blank then
select * from tbl where date between @frmdt and @todt
-
You have three options: dynamic sql, a catch-all query, or using IF blocks to test the parameters and run whichever query fits the parameters.
Catch-all queries are popular but come with a cost, which you can read about here[/url].
Using IF blocks is almost always the most performant method but you then have to maintain a number of queries each differing only in the WHERE clause.
Dynamic sql can certainly deal with your requirement but usually require a little more work than the other two methods. Try composing the different queries corresponding to the different parameters, something like your last post but with real column and table names, then use this as a template for building and testing your dynamic sql. Test each piece using PRINT.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Sean Lange (5/1/2014)
The first and the biggest issue is this is wide open to sql injection.In most cases, I'd strongly agree with you but... with two DATE, one INT, and one CHAR(1) parameters, I'd have to say that SQL Injection is impossible for the given code.
As to whether or not dynamic SQL is required for the original query or not goes, I'd have to agree with you and the others... NOT. 🙂
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (5/6/2014)
Sean Lange (5/1/2014)
The first and the biggest issue is this is wide open to sql injection.In most cases, I'd strongly agree with you but... with two DATE, one INT, and one CHAR(1) parameters, I'd have to say that SQL Injection is impossible for the given code.
As to whether or not dynamic SQL is required for the original query or not goes, I'd have to agree with you and the others... NOT. 🙂
The question is too vague and unstructured to provide anything more than a guess, so here goes:
IF @type <> '' AND @cid <> ''SELECT * FROM tbl
WHERE [date] BETWEEN @frmdt AND @todt
AND cid = @cid
AND [type] = @type
IF @type <> ''
SELECT * FROM tbl
WHERE [date] BETWEEN @frmdt AND @todt
AND [type] = @type
IF @cid <> ''
SELECT * FROM tbl
WHERE [date] BETWEEN @frmdt AND @todt
AND cid = @cid
IF @type = '' AND @cid = ''
SELECT * FROM tbl
WHERE [date] BETWEEN @frmdt AND @todt
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Jeff Moden (5/6/2014)
Sean Lange (5/1/2014)
The first and the biggest issue is this is wide open to sql injection.In most cases, I'd strongly agree with you but... with two DATE, one INT, and one CHAR(1) parameters, I'd have to say that SQL Injection is impossible for the given code.
As to whether or not dynamic SQL is required for the original query or not goes, I'd have to agree with you and the others... NOT. 🙂
Yes I even said as much in my post. However, the OP is struggling with a concept here and we all know what happens when you have dynamic sql that is working and somebody comes along and adds another value to the mix. At some point they will add a varchar to the mix and because the initial work was done in a format that allows it this will be wide open. Or the other side of that is that they will use this same technique on another process because it worked here. I am just trying to help the OP learn a better way of doing this so that in the future their code will be safe. 🙂
--edit--
fixed a spelling error.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Could someone include the "how" and "why" that dynamic SQL is vulnerable to SQL injection ?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply