Dynamic sql

  • 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]

  • 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.

  • 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 = .

  • 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

  • I have wrote a function which checks sql injection this is sample code to demonstrate issue i

    have

  • mxy - Sunday, January 15, 2017 3:29 AM

    I 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

  • Thom A - Sunday, January 15, 2017 3:36 AM

    mxy - Sunday, January 15, 2017 3:29 AM

    I 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?

    unfortunately i  cannot change parameters, otherwise it would be lot easier for me to make changes. filter is generated by vendor code.

  • 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