September 23, 2013 at 7:26 pm
Hi to all especially to those who always answer my questions wholeheartedly 🙂
I Have another question..and this is my Code.....
============================================================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SearchBiography]
@firstname varchar(50),
@middlename varchar(50),
@lastname varchar(50),
@sex varchar(50),
@status varchar(50)
-- @bioID int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlquery varchar(max) , @SqlQueryFirstName varchar(max),@SqlQueryMiddleName varchar(max), @SqlQueryLastName varchar(max), @SqlQueryStatus varchar(max), @SqlQueryFullName varchar(max)
SET @sqlquery = ''
IF LEN(@firstname) > 0
SET @SqlQueryFirstName = ' AND firstname like ''%' + @firstname + '%'''
ELSE
SET @SqlQueryFirstName = ''
IF LEN(@middlename) > 0
SET @SqlQueryMiddleName = ' AND middlename like ''%' + @middlename + '&'''
ELSE
SET @SqlQueryMiddleName = ''
IF LEN(@lastname) > 0
SET @SqlQueryLastName =' AND lastname like ''%' + @lastname + '%'''
ELSE
SET @SqlQueryLastName = ''
SET @sqlquery = 'SELECT * FROM TestMyView WHERE ' + ' sex like ''%' + @sex + '%''' + ' AND status like ''%' +@status + '%'''
SET @sqlquery = @sqlquery + @SqlQueryFirstName + @SqlQueryMiddleName + @SqlQueryLastName + @SqlQueryStatus
EXEC(@SqlQuery)
PRINT(@SqlQuery)
END
===========================================================I
I already have a class in my front-end to call the SP(Stored Procedure)..but how can i display the result of the search on my dataGridView
I can simply do this ( TestMyViewTableAdapter.FillByAll(Me.BiographyDataBIOGRAPHY.TestMyView, SexBindingSource.Current("sex"), StatusBindingSource.Current("status"), txtbxFname.Text, txtbxMname.Text, txtbxLname.Text)) using dataset to display the result without using SP.... but i like to use SP, i want to code it since i am a newbie, so that i can appreciate on what is instant...
Help me please 🙁 Thank you 🙂
See my UI Attachment
September 23, 2013 at 9:06 pm
Just say NO to dynamic SQL!!! That opens doors to all kinds of REALLY bad things! I should probably go find Kevin Kline's "SQL Server Low-Hanging Fruit" presentation - Wherever possible, you want to us the smallest and most restrictive data type possible in your stored procedures. Dynamic SQL in my opinion is a tool of last resort.
What if you have a simple stored procedure like you have with huge parameters and someone passes "; INSERT INTO..." and then mails himself the results using DBMail? You're in serious trouble. Not good at all.
I would suggest a more more restrictive approach. Gender can be (usually) one of 3 options: M, F, nknown/Null. So make the field a CHAR, and then it's impossible to stuff any junk in there that doesn't belong.
September 23, 2013 at 10:12 pm
pietlinden (9/23/2013)
Just say NO to dynamic SQL!!! That opens doors to all kinds of REALLY bad things! I should probably go find Kevin Kline's "SQL Server Low-Hanging Fruit" presentation - Wherever possible, you want to us the smallest and most restrictive data type possible in your stored procedures. Dynamic SQL in my opinion is a tool of last resort.What if you have a simple stored procedure like you have with huge parameters and someone passes "; INSERT INTO..." and then mails himself the results using DBMail? You're in serious trouble. Not good at all.
I would suggest a more more restrictive approach. Gender can be (usually) one of 3 options: M, F, nknown/Null. So make the field a CHAR, and then it's impossible to stuff any junk in there that doesn't belong.
Hi pietlinden 🙂
Thank you for answering my question my friend 🙂
But can you elaborate your answer...i am really intersted with your answer.. 🙂 thanks 😉
Here Hoping 😉
September 24, 2013 at 8:16 am
pietlinden (9/23/2013)
Just say NO to dynamic SQL!!! That opens doors to all kinds of REALLY bad things! I should probably go find Kevin Kline's "SQL Server Low-Hanging Fruit" presentation - Wherever possible, you want to us the smallest and most restrictive data type possible in your stored procedures. Dynamic SQL in my opinion is a tool of last resort.
I completely disagree here. Dynamic sql is incredibly powerful and is absolutely the right tool for this type of query. The problem is the approach is completely wrong. This code is wide open to sql injection attack.
_______________________________________________________________
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/
September 24, 2013 at 8:17 am
PLEASE do yourself a favor and read this article. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
It has been suggested to you at least a couple of other times in the various threads you have started all on basically the same topic.
_______________________________________________________________
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/
September 24, 2013 at 6:33 pm
Sean Lange (9/24/2013)
PLEASE do yourself a favor and read this article. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]It has been suggested to you at least a couple of other times in the various threads you have started all on basically the same topic.
Hi Sean
Yeah i just read all the articles that is given to me(catch all queries)
but, i'm confuse because some other post says that dynamic sql is a no no, can you enlighten me sean??? 🙁
Thanks Champion 🙂
September 25, 2013 at 6:32 am
enriquezreyjoseph (9/24/2013)
Sean Lange (9/24/2013)
PLEASE do yourself a favor and read this article. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]It has been suggested to you at least a couple of other times in the various threads you have started all on basically the same topic.
Hi Sean
Yeah i just read all the articles that is given to me(catch all queries)
but, i'm confuse because some other post says that dynamic sql is a no no, can you enlighten me sean??? 🙁
Thanks Champion 🙂
You'll have to learn as you go along not to take everything you read online as gospel (including this :-)).
There is nothing inherently wrong with Dynamic Sql as long as its the right tool for the job and you take the
time to ensure its known weaknesses can't be exploited, such as, Sql Injection.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply