I have an extensive search page (ASP/SQL Server) which uses similar code to this - with many optional search parameters. I am trying to use only stored procedures to prevent sql injection attacks (the site was recently hacked via another similar page). Doesn't using an EXEC statement make this script vulnerable to a similar attack, since someone could enter an 'INSERT' or similar statement into one of the text fields and it would run inside the script? Or am I misunderstanding? (I hope so, because it would be much easier to do it this way!)
Thanks for your time!
RELATED QUESTION:
--------------------------
Title:
Dynamic "where" statement (Stored procedure)
Bookmark:
Question: Se attached code snippet. I need help to make the "whereStatement" correct.
If @guid is NOT blank I want the where statement to be like this:
where active='1' and guid=@guid
If @guid is blank I want the where statement to be like this:
where active='1' and codeID=@codeID
Thanks for all help :)
ALTER PROCEDURE [dbo].[spKodeDetaljPay]
(
@codeid smallint,
@guid varchar(64)
)
AS
BEGIN
Select title, text,
(Select name from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as Name,
(Select email from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as email from tblCode
where active='1' -- and " & whereStatement
END
Try;-
ALTER PROCEDURE [dbo].[spKodeDetaljPay]
(
@codeid smallint,
@guid varchar(64)
)
AS
BEGIN
Select title, text,
(Select name from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as Name,
(Select email from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as email from tblCode
where
(active='1' and guid=@guid)
OR
(active='1' and codeid=@codeid and @guid='')
END
DECLARE @query as varchar(MAX)
Set @query = 'Select title, text, ' +
'(Select name from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as Name, ' +
'(Select email from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as email from tblCode ' +
'where active=''1'' '
IF @guid = '' OR @guid IS NULL
Set @query = @query + 'AND guid=''' + @guid + ''' '
ELSE
Set @query = @query + 'AND codeID=''' + @codeID + ''' '
EXEC(@query)
Start Free Trial
