SUGGEST ON STORED PROC
-
Hi All, I have written a stored procedure to execute common queries where we need all data from a table or with some clause, like this CREATE PROCEDURE dbo.GetAll ( @SQLQuery nvarchar(max) ) AS SET NOCOUNT ON EXEC sp_executesql @SQLQuery THIS PROECDURE WILL ACCEPT QUERY WITH WHERE CLAUSE ETC, Please suggest me if this one is good solution using stored proc, as you can see i used sp_executesql to execute query for caching query plan etc,,, suggest better one .. or if this is fine please let me know. Many thanks, adnan Many Thanks, Adnan Rafiq muhammadadnanrafiq@gmail.com
-
Hi All, I have written a stored procedure to execute common queries where we need all data from a table or with some clause, like this CREATE PROCEDURE dbo.GetAll ( @SQLQuery nvarchar(max) ) AS SET NOCOUNT ON EXEC sp_executesql @SQLQuery THIS PROECDURE WILL ACCEPT QUERY WITH WHERE CLAUSE ETC, Please suggest me if this one is good solution using stored proc, as you can see i used sp_executesql to execute query for caching query plan etc,,, suggest better one .. or if this is fine please let me know. Many thanks, adnan Many Thanks, Adnan Rafiq muhammadadnanrafiq@gmail.com
I really don't know where to start here. This type of dynamic SQL is really really bad because it represents a huge gaping security flaw in the application. Imagine if a query managed to get through with the following in:
DELETE sysobjects;You can probably see how this is bad. I suggest that you read the following article by Colin; http://www.codeproject.com/cs/database/SqlInjectionAttacks.asp[^]
Deja View - the feeling that you've seen this post before.
-
Hi All, I have written a stored procedure to execute common queries where we need all data from a table or with some clause, like this CREATE PROCEDURE dbo.GetAll ( @SQLQuery nvarchar(max) ) AS SET NOCOUNT ON EXEC sp_executesql @SQLQuery THIS PROECDURE WILL ACCEPT QUERY WITH WHERE CLAUSE ETC, Please suggest me if this one is good solution using stored proc, as you can see i used sp_executesql to execute query for caching query plan etc,,, suggest better one .. or if this is fine please let me know. Many thanks, adnan Many Thanks, Adnan Rafiq muhammadadnanrafiq@gmail.com
Wow! This is a SQL Injection Attackers heaven.
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
Hi All, I have written a stored procedure to execute common queries where we need all data from a table or with some clause, like this CREATE PROCEDURE dbo.GetAll ( @SQLQuery nvarchar(max) ) AS SET NOCOUNT ON EXEC sp_executesql @SQLQuery THIS PROECDURE WILL ACCEPT QUERY WITH WHERE CLAUSE ETC, Please suggest me if this one is good solution using stored proc, as you can see i used sp_executesql to execute query for caching query plan etc,,, suggest better one .. or if this is fine please let me know. Many thanks, adnan Many Thanks, Adnan Rafiq muhammadadnanrafiq@gmail.com
Beyond the obvious security problems and general 'why'-ness of this code, why would you even bother wrapping a call to one procedure in another procedure that takes the same parameters....??
adnanrafiq wrote:
caching query plan
But the select (and therefore the query plan) are going to change each time... :doh: This makes my head hurt...
-
Beyond the obvious security problems and general 'why'-ness of this code, why would you even bother wrapping a call to one procedure in another procedure that takes the same parameters....??
adnanrafiq wrote:
caching query plan
But the select (and therefore the query plan) are going to change each time... :doh: This makes my head hurt...
Thanks for Repley and for correcting me. But the select (and therefore the query plan) are going to change each time... Yes you are write, but there is caching of query plan, if i give the select statement in stored procedure string variable and pass only the where clause paramerter values but in this case i am doing this beucase in my search criteria user where clause vary on its selection.. What query you guys will suggest in this case: Skill table has following fields: SkillName , SkillExperience All skills are listed in listbox and user wants to search those profiles who have 2 years experience in ASP.Net and 3 years in Java, ...... http://msdn2.microsoft.com/en-us/library/ms188001.aspx[^] And One more thing, if i do validation on server side to prevent the maliciaous attacks, as suggested in the Colin article, like filtering sql query only for select statment. Then does it do work or still its top bad solution. Sorry in Advance for bad English.:) Many Thanks, adnan
Many Thanks, Adnan Rafiq muhammadadnanrafiq@gmail.com