March 12, 2012 at 7:28 am
Hi Friends,
exec sp_coded '', '', 2009, 99, 2,'', '3', '', '', '',
'SELECT Distinct 0 as PnxJurisID , 2009 as CodedID, PSAJCK.AJDVLS as SrcCodeValueRef, CASE WHEN SCTABE.TECOD# IS NULL THEN '' '' ELSE LTRIM(RTRIM(SCTABE.TECOD#)) END as SrcCodeValue, SCTABE.TEDESC as SrcCodeDescr
From dbo.PSAJCK
left outer join SCTABE on rtrim(ltrim(SCTABE.TECOD#)) = rtrim(ltrim(PSAJCK.AJDVLS)) and SCTABE.TEMST# = 0022
union
SELECT Distinct 0 as PnxJurisID , 2009 as CodedID, PSCHAR.ACBSTA as SrcCodeValueRef, CASE WHEN SCTABE.TECOD# IS NULL THEN '' '' ELSE LTRIM(RTRIM(SCTABE.TECOD#)) END as SrcCodeValue, SCTABE.TEDESC as SrcCodeDescr
From dbo.PSCHAR
left outer join SCTABE on rtrim(ltrim(SCTABE.TECOD#)) = rtrim(ltrim(PSCHAR.ACBSTA)) and SCTABE.TEMST# = 0022'
i created a stored procedure..and
i am trying to pass a some value through parameter..but i get error below
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'ELSE'.
whether this is an single quote problem or something..? Here i pass two select statements with union... if i pass single select statement it works fine..but when i pass with union, it gives error...how to pass the single quotes value in parameter?
Any suggestions would be really appreciated
Thanks,
Charmer
March 12, 2012 at 7:44 am
Let me get this straight, you're passing in the entire SELECT statement as a parameter?
If that's the case, check the length for your datatype on the parameter for the SQL statement. The current length of the code in the last parameter is 681 characters. Check your varchar() or nvarchar datatype's length
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 12, 2012 at 7:49 am
MyDoggieJessie (3/12/2012)
Let me get this straight, you're passing in the entire SELECT statement as a parameter?If that's the case, check the length for your datatype on the parameter for the SQL statement. The current length of the code in the last parameter is 681 characters. Check your varchar() or nvarchar datatype's length
Thank God....You are Correct....i did not check this out....THANKS A LOT Jessie.....
Thanks,
Charmer
March 12, 2012 at 7:53 am
I have to agree with Jessie here....WHY are you passing sql as a parameter???? I am going to take a guess that you will now execute the sql passed as a parameter? You should probably take a step back and look at how incredibly dangerous that is.
_______________________________________________________________
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/
March 12, 2012 at 7:59 am
Yes, be very careful...using a parameter for something like this can leave you wide open for malicious attack. Search this site for SQL Injection (or google it)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 12, 2012 at 8:01 am
Sean Lange (3/12/2012)
I have to agree with Jessie here....WHY are you passing sql as a parameter???? I am going to take a guess that you will now execute the sql passed as a parameter? You should probably take a step back and look at how incredibly dangerous that is.
Yes Sean....I agree with this..it's going to be a problem some time. but i don't pass SQL statement as parameter as often....very rarely and only if my stored procedure does not suite for some criteria...
could you tell me What's the way to pass SQL statement as a parameter safely? or in a better condition?
Thanks,
Charmer
March 12, 2012 at 8:11 am
I don't think the question is how to pass SQL as a parameter. The question is why would you ever pass SQL as a parameter? What are you accomplishing by doing this?
Jared
CE - Microsoft
March 12, 2012 at 8:15 am
Charmer (3/12/2012)
Sean Lange (3/12/2012)
I have to agree with Jessie here....WHY are you passing sql as a parameter???? I am going to take a guess that you will now execute the sql passed as a parameter? You should probably take a step back and look at how incredibly dangerous that is.Yes Sean....I agree with this..it's going to be a problem some time. but i don't pass SQL statement as parameter as often....very rarely and only if my stored procedure does not suite for some criteria...
could you tell me What's the way to pass SQL statement as a parameter safely? or in a better condition?
It is quite simple. Don't do it. If you find yourself in a situation where you think you need to pass sql as a parameter it is a pretty good sign that your process is not designed very well. I can't even come up with something where this would be required.
Maybe if you can explain what you are trying to do we can come with a better way of doing it.
_______________________________________________________________
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/
March 12, 2012 at 9:10 am
But the thing is i did not create the stored procedure...
it was previously created by some one....Now i changed it as you guys told that there would be some injection...
Thank guys...
Thanks,
Charmer
March 12, 2012 at 9:14 am
Charmer (3/12/2012)
But the thing is i did not create the stored procedure...it was previously created by some one....Now i changed it as you guys told that there would be some injection...
Thank guys...
SQL statement as parameter ...like some times i need to pass with union operation, some times i need to join with two tables or three tables...some times i don't need to join...
this is the criteria that i pass SQL ...
this is due to stupid kinda database structure of the customer..and their data's...
What can i do?:unsure:
Thanks,
Charmer
March 12, 2012 at 9:37 am
Charmer (3/12/2012)
Charmer (3/12/2012)
But the thing is i did not create the stored procedure...it was previously created by some one....Now i changed it as you guys told that there would be some injection...
Thank guys...
SQL statement as parameter ...like some times i need to pass with union operation, some times i need to join with two tables or three tables...some times i don't need to join...
this is the criteria that i pass SQL ...
this is due to stupid kinda database structure of the customer..and their data's...
What can i do?:unsure:
I understand you inherited it. Just because you inherit horribly design does not mean you have to keep doing it.
There is not database structure that forces you to pass a query as a parameter. That is poor architecture and should be fixed. I couldn't begin to tell how to fix it because I have no idea what it is doing. Passing a query to a proc just screams of bad design. If this is 100% impossible for an end user to have any input it might, and I mean MIGHT, be currently ok for sql injection. If there is anything the user can enter that gets passed along you are wide open to sql injection attack.
Again if you can explain what you are trying to accomplish we can figure out a much better method than passing a query as a parameter that you later have to execute.
_______________________________________________________________
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/
March 12, 2012 at 9:55 am
I am sorry..yes, this is a poor architecture, not poor design .....
the good thing is end user never going to touch this....
only by myself...
I did not understand the customer database completely...that's why i am not able to explain what my exact requirement is...
the Sp is used for getting the records by comparing their tables with my tables....
using some conditions...
3 conditions totally....third one is passing SQL...
i am not able to explain to you since i did not get it well yet...sorry friend, once i understood, il let you know my requirements, i would ask your help for sure..
Thanks,
Charmer
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply