Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is this Vulnerable for SQL injection?.. Expand / Collapse
Author
Message
Posted Wednesday, September 25, 2013 11:02 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 81, Visits: 191
Hi Everyone

I hope everyone is having a nice day.

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),
@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 = ''


IF LEN(@sex) > 0
SET @SqlQuerySex = ' AND sex like ''%' + @sex + '%'''
ELSE
SET @SqlQuerySex = ''


IF LEN(@status) > 0
SET @SqlQueryStatus = ' AND status like ''%' + @status + '%'''
ELSE
SET @SqlQueryStatus = ''


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 sexID = ' + convert(varchar(20), @sexID) + ' AND statusID = ' + convert(varchar(20), @statusID)
SET @SqlQuery = @SqlQuery + @SqlQuerySex + @SqlQueryStatus + @SqlQueryFirstName + @SqlQueryMiddleName + @SqlQueryLastName


EXEC (@SqlQuery) /* Should i need a parameter here??? */ please tell me :-(
PRINT(@SqlQuery)

END




Post #1498669
Posted Thursday, September 26, 2013 12:50 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:19 PM
Points: 975, Visits: 3,342
YES!
Anything that is Dynamic can be. Plus as everyone else has said, it is very poor practice. And poor performing.

I wish that it were illegal for a front-end programmer to even open SQL Server. It isn't illegal for one to perform surgery on themselves. Why don't you try that next time you are ill?

And you wonder why so many SQL DBAs are not being paid nearly enough, while having to deal with crap code that makes a SQL Server come to a crawl. And also given code like this to try and troubleshoot or change, all because the front-end programmer was fired.

Andrew SQLDBA
Post #1498683
Posted Thursday, September 26, 2013 12:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 81, Visits: 191
AndrewSQLDBA (9/26/2013)
YES!
Anything that is Dynamic can be. Plus as everyone else has said, it is very poor practice. And poor performing.

I wish that it were illegal for a front-end programmer to even open SQL Server. It isn't illegal for one to perform surgery on themselves. Why don't you try that next time you are ill?

And you wonder why so many SQL DBAs are not being paid nearly enough, while having to deal with crap code that makes a SQL Server come to a crawl. And also given code like this to try and troubleshoot or change, all because the front-end programmer was fired.

Andrew SQLDBA


HAHAHAH..thanks andrew..

can you "Re-Code" the above Code??...so that it will not be prone to sql injection...thanks andrew..
Post #1498685
Posted Thursday, September 26, 2013 1:54 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:15 PM
Points: 457, Visits: 635
You should use sp_executesql with parameters.

Have a look at The Curse and Blessings of Dynamic SQL for a bit more info.




Post #1498705
Posted Thursday, September 26, 2013 2:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 13,312, Visits: 10,178
The book by Denny Cherry on Securing SQL Server has a chapter on SQL Injection, it might be worth checking it out.

Securing SQL Server, Second Edition: Protecting Your Database from Attackers




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1498706
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse