Aim: Dynamic SQL based on user input
Use: User search boxes to get a result
Query/Review Question: Does the below leave the code exposed to SQL Injection attacks?
--Declare Variable to hold the Dynamic SQL
DECLARE @vSQL varchar(max);
DECLARE @sSomeValue varchar(max);
DECLARE @sFirstName varchar(max);
--Your ifcondition
IF LTRIM(RTRIM(@sSomeValue))= 'DefaultValue'
BEGIN
--Create you statement with OR condition
SET @vSQL = 'OR SomeValue = ' + @sSomeValue;
END;
ELSE
BEGIN
--Create you statement with AND condition
SET @vSQL = 'ANDSomeValue = ' + @sSomeValue;
END;
SELECT Firstname, SomeValue
FROM TblMainTable
WHERE Firstname LIKE '%' + @sFirstName + '%' + @vSQL;
Notes:
This is a more complex code however the If/Else statements are the same in structure so this is sample code for the principle of the code review.
References:
a' OR 'a' = 'a'for the value of@sSomeValueand see what happens. – RobH Nov 24 '14 at 15:57