January 14, 2017 at 8:15 pm
i have a issue, when application passes underscore to the stored proc as filter condition search doesnt display any data in below code set 2. i want to replace _ with [_] only when user choose like operator in filter criteria. is it possible when we have multiple fields to check like operator and replace _ with [_]
IF OBJECT_ID('TempDB..#test') IS NOT NULL
DROP TABLE #test;
create table #test
(
colname varchar(250) not null,
col2 varchar(20) not null
)
insert into #test
select 'Abu_Dhabi' ,'yes'
union select 'Port_Said','yes'
union select 'El-Mahalla_El-Kubra','yes'
union select 'French_Polynesia','yes'
union select 'Andorra la Vella','yes'
union select 'Escaldes','yes'
union select 'Sharjah','no'
union select 'Abu Dhabi','yes'
union select 'Ajman','no'
union select 'Balkh','no'
union select 'Bamian','no'
declare @sqlstmt nvarchar(2000)
declare @filterstmt varchar(200)
set @sqlstmt = 'select colname from #test where '
set @filterstmt = ' colname like ''%_%'' and col2 = ''yes'' '
set @sqlstmt = @sqlstmt +@filterstmt
set @sqlstmt = replace(@sqlstmt , '_','[_]')
exec sp_executesql @sqlstmt[/code]
--code set2
IF OBJECT_ID('TempDB..#test') IS NOT NULL
DROP TABLE #test;
create table #test
(
colname varchar(250) not null,
col2 varchar(20) not null
)
insert into #test
select 'Abu_Dhabi' ,'yes'
union select 'Port_Said','yes'
union select 'El-Mahalla_El-Kubra','yes'
union select 'French_Polynesia','yes'
union select 'Andorra la Vella','yes'
union select 'Escaldes','yes'
union select 'Sharjah','no'
union select 'Abu Dhabi','yes'
union select 'Ajman','no'
union select 'Balkh','no'
union select 'Bamian','no'
declare @sqlstmt nvarchar(2000)
declare @filterstmt varchar(200)
set @sqlstmt = 'select colname from #test where '
set @filterstmt = ' colname = ''Abu_Dhabi'' and col2 = ''yes'' '
set @sqlstmt = @sqlstmt +@filterstmt
set @sqlstmt = replace(@sqlstmt , '_','[_]')
--select @sqlstmt
exec sp_executesql @sqlstmt
[/code]
January 15, 2017 at 3:08 am
Question, are you trying to find all the entries containing an underscore character?
😎
Note that replacing the underscore character in brackets will only work with the LIKE operator, change the equality operator to a LIKE in the second statement and it will work.
January 15, 2017 at 3:16 am
Yes I am trying to replace it for all the underscore occurrences in the filter statement.
Challenge is I should replace underscore for like operator not for equal to = .
January 15, 2017 at 3:19 am
The above also screams SQL Injection at me. Are your getting the values of @sqlstmt and @filterstmt from the SP's paramters?
What if I were to pass the following to that parameter:Exec YourSP 'CREATE DATABASE NewDB;', 'Create DATABASE AnotherNewDB;';
Does that run? That's a concern if so.
A better, and working solution, would be:USE TestDB;
GO
CREATE TABLE SampleTable (colname varchar(250) NOT NULL,
col2 varchar(20) NOT NULL);
GO
INSERT INTO SampleTable
SELECT 'Abu_Dhabi' ,'yes'
UNION SELECT 'Port_Said','yes'
UNION SELECT 'El-Mahalla_El-Kubra','yes'
UNION SELECT 'French_Polynesia','yes'
UNION SELECT 'Andorra la Vella','yes'
UNION SELECT 'Escaldes','yes'
UNION SELECT 'Sharjah','no'
UNION SELECT 'Abu Dhabi','yes'
UNION SELECT 'Ajman','no'
UNION SELECT 'Balkh','no'
UNION SELECT 'Bamian','no'
GO
CREATE PROC SearchSample @Name varchar(250), @Col2 varchar(20) AS
SET @Name = REPLACE(@Name,'_','[_]');
SET @Name = '%' + @Name + '%';
SELECT ST.colname
FROM SampleTable ST
WHERE ST.colname LIKE @Name
AND ST.col2 = @Col2;
GO
EXEC SearchSample '_', 'yes';
EXEC SearchSample 'Abu_Dhabi', 'yes';
GO
DROP PROC SearchSample;
DROP TABLE SampleTable;
GO
EDIT:
Alternatively, if you want to keep the LIKE and = operators, you could do:CREATE PROC SearchSample2 @Name varchar(250), @Col2 varchar(20), @Match Bit = 0 AS
IF @Match = 0 BEGIN
SET @Name = REPLACE(@Name,'_','[_]');
SET @Name = '%' + @Name + '%';
END
SELECT ST.colname
FROM SampleTable ST
WHERE ((ST.colname LIKE @Name AND @Match = 0)
OR (ST.colname = @Name AND @Match = 1))
AND ST.col2 = @Col2;
GO
EXEC SearchSample2 '_', 'yes';
EXEC SearchSample2 'Abu_Dhabi', 'yes', 1;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 15, 2017 at 3:29 am
I have wrote a function which checks sql injection this is sample code to demonstrate issue i
have
January 15, 2017 at 3:36 am
mxy - Sunday, January 15, 2017 3:29 AMI have wrote a function which checks sql injection this is sample code to demonstrate issue i have
I still don't think that D-SQL is the way to go here.Does the above work?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 15, 2017 at 3:45 am
Thom A - Sunday, January 15, 2017 3:36 AMmxy - Sunday, January 15, 2017 3:29 AMI have wrote a function which checks sql injection this is sample code to demonstrate issue i haveI still don't think that D-SQL is the way to go here.Does the above work?
unfortunately i cannot change parameters, otherwise it would be lot easier for me to make changes. filter is generated by vendor code.
January 15, 2017 at 3:52 am
You're going to be hard pressed to get a solution then. As Eirikur pointed out, an escaped underscore in an = statement doesn't work(well, it searches for '[_]', not '_'). Unless you can define some kind of logic to know that the statement contains all literals, or all LIKE's, your not going to be able to figure out when to replace a '_'with '[_]' and when not to.
Personally, I would go back to your vendor and explain the implications of what they are attempting to do (like the use of D-SQL and SQL injection) and get them to rethink their strategy and provide you with an update. If they think that what they have created is acceptable then they should not be a vendor and you should consider using a different one...
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply