Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is this Correct Use of Dynamic SQL???


Is this Correct Use of Dynamic SQL???

Author
Message
enriquezreyjoseph
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 191

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description:   <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SearchBiography]
   
@firstname nvarchar(50),
@middlename nvarchar(50),
@lastname nvarchar(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)

SET @SqlQuery = ''
SET @SqlQueryStatus = ''
SET @SqlQueryFirstname = ''
SET @SqlQueryMiddlename = ''
SET @SqlQueryLastName = ''
SET @SqlQuerySex = ''
SET @SqlQueryStatus = ''


IF @sexID <> 0

SET @SqlQuerySex = ' WHERE sexID = ' + convert(varchar(20), @sexID)

IF @statusID <> 0
BEGIN
IF LEN(@SqlQuerySex) > 0
SET @SqlQueryStatus = ' AND statusID = ' + convert(varchar(20), @statusID)
ELSE
SET @SqlQueryStatus = ' WHERE statusID = ' + convert(varchar(20), @statusID)
END

IF LEN(@firstname) > 0
BEGIN
IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0
SET @SqlQueryFirstname = ' AND firstname like ''%' + @firstname + '%'''
ELSE
SET @SqlQueryFirstname = ' WHERE firstname like ''%' + @firstname + '%'''
END

IF LEN(@middlename) > 0
BEGIN
IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0 or LEN(@SqlQueryFirstname) > 0
SET @SqlQueryMiddlename = ' AND middlename like ''%' + @middlename + '%'''
ELSE
SET @SqlQueryMiddlename = ' WHERE middlename like ''%' + @middlename + '%'''
END

IF LEN(@lastname) > 0
BEGIN
IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0 or LEN(@SqlQueryFirstname) > 0 or LEN(@SqlQueryMiddlename) > 0
SET @SqlQueryLastname = ' AND lastname like ''%' + @lastname + '%'''
ELSE
SET @SqlQueryLastname = ' WHERE lastname like ''%' + @lastname + '%'''
END

SELECT @SqlParam = ' @xfirstname nvarchar(50),
@xmiddlename nvarchar(50),
@xlastname nvarchar(50),
@xsexID int,
@xstatusID int '

EXEC sp_executesql, @SqlParam, @firstname,@middlename,@lastname,@sexID,@statusID

END





pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2172 Visits: 12482
The correct use for dynamic SQL is Option 1: DON'T, especially if you're a noob.

If you understand the repercussions of code that can't be optimized, and SQL injection attacks, etc, then knock yourself out.

It's perfectly okay to have a lot of stored procedures in your database, because those can be optimized.
enriquezreyjoseph
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 191
pietlinden (9/28/2013)
The correct use for dynamic SQL is Option 1: DON'T, especially if you're a noob.

If you understand the repercussions of code that can't be optimized, and SQL injection attacks, etc, then knock yourself out.

It's perfectly okay to have a lot of stored procedures in your database, because those can be optimized.


I can't understand :-(..please elaborate more please ;-)
enriquezreyjoseph
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 191
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description:   <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SearchBiography]
   
@firstname nvarchar(50),
@middlename nvarchar(50),
@lastname nvarchar(50),
@sexID nchar(5) = NULL,
@statusID nchar(5) = NULL

AS
BEGIN
   
   SET NOCOUNT ON;
DECLARE @SqlQuery nvarchar(max) , @SqlParam nvarchar(max)

SET @SqlQuery = ''

SELECT @SqlQuery = ' SELECT * ' + ' FROM TestMyView WHERE 1 = 1 '

IF @sexID <> '0'

SELECT @SqlQuery = @SqlQuery + ' AND sexID LIKE ' + @sexID

IF @statusID <> '0'

SELECT @SqlQuery = @SqlQuery + ' AND statusID LIKE ' + @statusID

IF LEN(@firstname) > 0

SELECT @SqlQuery = @SqlQuery + ' AND firstname LIKE ''%'+@firstname+'%'''

IF LEN(@middlename) > 0

SELECT @SqlQuery = @SqlQuery + ' AND middlename LIKE ''%'+@middlename+'%'''

IF LEN(@lastname) > 0

SELECT @SqlQuery = @SqlQuery + ' AND lastname LIKE ''%'+@lastname+'%'''


SELECT @SqlParam = '@xfirstname nvarchar(50),
@xmiddlename nvarchar(50),
@xlastname nvarchar(50),
@xsexID nchar(5),
@xstatusID nchar(5) '


EXEC sp_executesql @SqlQuery,@SqlParam,
@firstname, @middlename,
@lastname, @sexID,
@statusID
END






How about this??..huhuhuhuh ;-(
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24167 Visits: 37929
What are the correct data types for the following columns:

sexID
statusID
firstname
middlename
lastname

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
enriquezreyjoseph
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 191
Lynn Pettis (9/28/2013)
What are the correct data types for the following columns:

sexID
statusID
firstname
middlename
lastname



sexID int
statusID int
firstname varchar(20)
middlename varchar(20)
lastname varchar(20)

that is in my table
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24167 Visits: 37929
enriquezreyjoseph (9/28/2013)
Lynn Pettis (9/28/2013)
What are the correct data types for the following columns:

sexID
statusID
firstname
middlename
lastname



sexID int
statusID int
firstname varchar(20)
middlename varchar(20)
lastname varchar(20)

that is in my table


Okay, then based on the info above, your code is inefficient. Your parameters to both your stored procedure and the dynamic sql you are building should match the data types of the columns in your table/view.

Also, the way you wrote your last dynamic sql you don't even need the parameters you defined, you aren't using them. The code you have written is ripe for SQL injection.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2594 Visits: 8436
When you post in multiple threads like this, you fragment answers and make people have to start from the beginning again with helping you. If you have further questions, stick to your original thread please.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47173 Visits: 44347
All of the posted code is vulnerable to SQL injection. Please, please, for the third or fourth time, read up on SQL injection and don't use dynamic SQL until you have done so and understand how and why it's such a risk.

You've got sp_execute SQL with parameters being passed to it, but those parameters are never used anywhere in the dynamic SQL and hence give you no protection at all. Passing parameters is not the key. Using only parameters is the key

Additionally, you have been referred, more than once, to my blog post on how to do catch-all queries safely with no injection risk.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


enriquezreyjoseph
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 191
GilaMonster (9/28/2013)
All of the posted code is vulnerable to SQL injection. Please, please, for the third or fourth time, read up on SQL injection and don't use dynamic SQL until you have done so and understand how and why it's such a risk.

You've got sp_execute SQL with parameters being passed to it, but those parameters are never used anywhere in the dynamic SQL and hence give you no protection at all. Passing parameters is not the key. Using only parameters is the key

Additionally, you have been referred, more than once, to my blog post on how to do catch-all queries safely with no injection risk.


Thank you sir:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search