September 24, 2013 at 7:46 pm
Hi Everyone 😉
I hope everyone is having a nice day ahead 😉
I have a question guys...
I want to search for example a Sex whether Male or Female regardless of their status..
How should i do that??...here is my Code in SQL 2005 and my Front-end is Visual Studio 2008
see my UI attachment
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),
@sexID int,
@statusID int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlquery varchar(max) , @SqlQueryFirstName varchar(max),@SqlQueryMiddleName varchar(max), @SqlQueryLastName varchar(max), @SqlQuerySex varchar(max), @SqlQueryStatus varchar(max), @SqlSexIDQuery varchar(max), @SqlStatusIDQuery varchar(max)
SET @sqlquery = ''
IF LEN(@sex) > 0
SET @SqlQuerySex = ' sex = ''' + @sex + ''''
ELSE
SET @SqlQuerySex = ''
IF LEN(@status) > 0
SET @SqlQueryStatus = ' AND status = ''' + @status + ''''
ELSE
SET @SqlQueryStatus = ''
IF @sexID <> 0
SET @SqlSexIDQuery = ' AND sexID IN(SELECT sexID FROM sex WHERE sexID = ' + convert(varchar(20), @sexID) + ' )'
ELSE
SET @SqlSexIDQuery = ''
IF @statusID <> 0
SET @SqlStatusIDQuery = ' AND statusID IN(SELECT statusID FROM status WHERE statusID = ' + convert(varchar(20), @statusID)+ ')'
ELSE
SET @SqlStatusIDQuery= ''
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 '
SET @sqlquery = @sqlquery + @SqlQuerySex + @SqlQueryStatus + @SqlSexIDQuery + @SqlStatusIDQuery + @SqlQueryFirstName + @SqlQueryMiddleName + @SqlQueryLastName
EXEC(@SqlQuery)
PRINT(@SqlQuery)
END
September 24, 2013 at 7:58 pm
Well, you can either search the table for one, or both.
SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = 'M' OR SEX = 'F'
That will select all rows with either M or F in the Sex column.
If you want only one.
SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = 'M'
If you are using a sproc, simply add an input parameter with the SexyType
And the query would be
SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = @SexType
Have you looked at the SQL Books Online for anything? This would be the place to start.
And as almost everyone on here has stated, stop using Dynamic SQL.
Andrew SQLDBA
September 24, 2013 at 8:10 pm
AndrewSQLDBA (9/24/2013)
Well, you can either search the table for one, or both.
SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = 'M' OR SEX = 'F'
That will select all rows with either M or F in the Sex column.
If you want only one.
SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = 'M'
If you are using a sproc, simply add an input parameter with the SexyType
And the query would be
SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = @SexType
Have you looked at the SQL Books Online for anything? This would be the place to start.
And as almost everyone on here has stated, stop using Dynamic SQL.
Andrew SQLDBA
Hi Andrew 🙂
But some post say that..Dynamic Sql is a powerful tool...
can you give me a strong justification why should i stop using dynamic SQL?? PLEASE :-(..badly need some advise..
September 24, 2013 at 10:05 pm
enriquezreyjoseph (9/24/2013)
AndrewSQLDBA (9/24/2013)
Well, you can either search the table for one, or both.
SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = 'M' OR SEX = 'F'
That will select all rows with either M or F in the Sex column.
If you want only one.
SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = 'M'
If you are using a sproc, simply add an input parameter with the SexyType
And the query would be
SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = @SexType
Have you looked at the SQL Books Online for anything? This would be the place to start.
And as almost everyone on here has stated, stop using Dynamic SQL.
Andrew SQLDBA
Hi Andrew 🙂
But some post say that..Dynamic Sql is a powerful tool...
can you give me a strong justification why should i stop using dynamic SQL?? PLEASE :-(..badly need some advise..
1. Debugging a dynamic query is awful.
2. risk of SQL Injection.
3. when you have a very simple solution available for sex search, then why go dynamic 🙂
September 25, 2013 at 1:45 am
enriquezreyjoseph (9/24/2013)
But some post say that..Dynamic Sql is a powerful tool...
Indeed, but you don't use a jackhammer to put a nail in the wall. Powerful tool != use all the time
can you give me a strong justification why should i stop using dynamic SQL?? PLEASE 🙁
Harder to write. Much harder to read. Very hard to debug. Vulnerable to SQL injection. Requires elevated permissions. I could go on. When I do code reviews, code that uses dynamic SQL for no good reason goes straight back to the developer to fix.
Now, if you want to do a dynamic search, you will need dynamic SQL, but not the very convoluted, insecure way you've got.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Oh, and if you want to know why the way you've written it is bad, try running this:
EXEC [dbo].[SearchBiography] @firstname = 'Gary'
@middlename = ''
@lastname = 'White''; shutdown with nowait --'
@sex = ''
@status = ''
@sexID = 0
@statusID = 0;
Edit: one quote too many
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2013 at 1:52 am
GilaMonster (9/25/2013)
enriquezreyjoseph (9/24/2013)
But some post say that..Dynamic Sql is a powerful tool...Indeed, but you don't use a jackhammer to put a nail in the wall. Powerful tool != use all the time
can you give me a strong justification why should i stop using dynamic SQL?? PLEASE 🙁
Harder to write. Much harder to read. Very hard to debug. Vulnerable to SQL injection. Requires elevated permissions. I could go on. When I do code reviews, code that uses dynamic SQL for no good reason goes straight back to the developer to fix.
Thank you my Friend GilaMonster 🙂
You are the Monster of SQL ;-)...1billion thumbs Up to you my friend 😉
September 25, 2013 at 8:46 am
It was me who has repeatedly told you over the last few days that you need to parameterize your dynamic sql. I have posted the same link to the same article that Gail posted (she is the author of that fantastic piece of work btw). Please take the time to read it. You said you have read it but you keep posting code that is vulnerable to sql injection.
I even showed you a code example of how dangerous injection can be. Do yourself and your company a favor and stop using dynamic sql without parameters.
_______________________________________________________________
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 25, 2013 at 6:36 pm
Sean Lange (9/25/2013)
It was me who has repeatedly told you over the last few days that you need to parameterize your dynamic sql. I have posted the same link to the same article that Gail posted (she is the author of that fantastic piece of work btw). Please take the time to read it. You said you have read it but you keep posting code that is vulnerable to sql injection.I even showed you a code example of how dangerous injection can be. Do yourself and your company a favor and stop using dynamic sql without parameters.
Thank you Sean 🙂
Ok sean i will do that, i'm new to sql and vb.net and you all enlighten me to a Very Very vEry Goood Exellent answers, hoping to learn more from you 🙂
September 25, 2013 at 6:48 pm
Sean, What do you mean i don't have a parameter??....I guess i have parameters look at my first post above...
like this..
==================================
ALTER PROCEDURE [dbo].[SearchBiography]
@firstname varchar(50),
@middlename varchar(50),
@lastname varchar(50),
@sex varchar(50),
@status varchar(50),
@sexID int,
@statusID int
==================================
firstname,middlename, lastname..etc are parameters of my stored procedure right?..
September 25, 2013 at 7:41 pm
GilaMonster (9/25/2013)
enriquezreyjoseph (9/24/2013)
But some post say that..Dynamic Sql is a powerful tool...Indeed, but you don't use a jackhammer to put a nail in the wall. Powerful tool != use all the time
can you give me a strong justification why should i stop using dynamic SQL?? PLEASE 🙁
Harder to write. Much harder to read. Very hard to debug. Vulnerable to SQL injection. Requires elevated permissions. I could go on. When I do code reviews, code that uses dynamic SQL for no good reason goes straight back to the developer to fix.
Now, if you want to do a dynamic search, you will need dynamic SQL, but not the very convoluted, insecure way you've got.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Oh, and if you want to know why the way you've written it is bad, try running this:
EXEC [dbo].[SearchBiography] @firstname = 'Gary'@middlename = ''
@lastname = 'White''; shutdown with nowait --'
@sex = ''
@status = ''
@sexID = 0
@statusID = 0;
Edit: one quote too many
Hi Sir Gail..
Please see my Attachment..that is the result when i try to run your suggestion...
Can you explain to me why sir Gail??..thanks..
September 25, 2013 at 10:09 pm
GilaMonster (9/25/2013)
enriquezreyjoseph (9/24/2013)
But some post say that..Dynamic Sql is a powerful tool...Indeed, but you don't use a jackhammer to put a nail in the wall. Powerful tool != use all the time
can you give me a strong justification why should i stop using dynamic SQL?? PLEASE 🙁
Harder to write. Much harder to read. Very hard to debug. Vulnerable to SQL injection. Requires elevated permissions. I could go on. When I do code reviews, code that uses dynamic SQL for no good reason goes straight back to the developer to fix.
Now, if you want to do a dynamic search, you will need dynamic SQL, but not the very convoluted, insecure way you've got.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Oh, and if you want to know why the way you've written it is bad, try running this:
EXEC [dbo].[SearchBiography] @firstname = 'Gary'@middlename = ''
@lastname = 'White''; shutdown with nowait --'
@sex = ''
@status = ''
@sexID = 0
@statusID = 0;
Edit: one quote too many
+1 🙂
September 26, 2013 at 3:09 am
enriquezreyjoseph (9/25/2013)
Hi Sir Gail..Please see my Attachment..that is the result when i try to run your suggestion...
Look at what the error say and see what I almost did to your server by injecting a command into your dynamic SQL and now ask yourself what it that had been a DROP DATABASE instead of a shutdown which couldn't run?
What would your boss say when you put that into production and someone a little less ethical deletes data, steals your paswords, drops your database, all because you decided that unparameterised dynamic SQL was easier...
Sean, What do you mean i don't have a parameter??
Your dynamic SQL is not parameterised, hence why I could do nasty things just by adding extra commands (that get executed) to the stored procedure parameter value.
If you aren't willing to learn how to write dynamic SQL safely, then please stop writing it at all, for your company's sake and the sake of all their customers. Oh, and stop running your queries as SA too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2013 at 4:11 am
GilaMonster (9/26/2013)
enriquezreyjoseph (9/25/2013)
Hi Sir Gail..Please see my Attachment..that is the result when i try to run your suggestion...
Look at what the error say and see what I almost did to your server by injecting a command into your dynamic SQL and now ask yourself what it that had been a DROP DATABASE instead of a shutdown which couldn't run?
What would your boss say when you put that into production and someone a little less ethical deletes data, steals your paswords, drops your database, all because you decided that unparameterised dynamic SQL was easier...
Sean, What do you mean i don't have a parameter??
Your dynamic SQL is not parameterised, hence why I could do nasty things just by adding extra commands (that get executed) to the stored procedure parameter value.
If you aren't willing to learn how to write dynamic SQL safely, then please stop writing it at all, for your company's sake and the sake of all their customers. Oh, and stop running your queries as SA too.
What is SA sir gail??
September 26, 2013 at 4:21 am
SysAdmin.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2013 at 6:37 pm
GilaMonster (9/26/2013)
SysAdmin.
I'm willing to learn sir gail...that is why i exist in this community because of you...
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply