How to pass in sql "like" condition from c# with single quotes

Question

I have string variable in c#

String Condition = " And LoginName like ''%"+txtLoginName.text+"%''";

I pass this condition variable to a stored procedure like this:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "GetAllUserMasterBySearch";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Condition ", Condition );

And my stored procedure is here:

create PROCEDURE [dbo].[GetAllUserMasterBySearch] (  
    @Condition nvarchar(max)  
    ) 
as
    declare @Query  nvarchar(max) 

    set @Query = 'SELECT
                      [UserMasterId], [LoginName], [UserName],
                      [UserType], [MobileNo], [Email],
                      [IsLogin], [IpAddress]
                  FROM  
                      UserMaster      
                  WHERE 
                      IsActive = 1 ' + @Condition

    print @query   

    EXEC sp_executesql @Query

How to pass "like" condition from C# ?

Please read the help on formatting your questions. http://stackoverflow.com/editing-help, your question in its current form is very hard to follow. — Scott Chamberlain, Feb 14 '16 at 14:48
@DanBracuk invalid column that I have enter in txtLoginName.Text — Bhavin, Feb 14 '16 at 14:50
be very careful with building SQL like this. It is vulnerable to SQL injection. In means that the user can execute arbitrary SQL in your database just by using a simple trick and entering it to txtLoginName.text. Use parameterized queries instead. — Stefan Steinegger, Feb 14 '16 at 14:57
Similar question: http://stackoverflow.com/questions/35193635/what-is-the-meaning-of-data/35194648#35194648 — Stefan Steinegger, Feb 14 '16 at 14:58

IgorM · Accepted Answer · 2016-02-14T15:08:43.233

You can pass only variable and rewrite your SP as following

alter PROCEDURE [dbo].[GetAllUserMasterBySearch] (  
    @var nvarchar(max)  
) as

declare @Query  nvarchar(max) 
set @Query = 'SELECT
                  [UserMasterId]
                 ,[LoginName]
                 ,[UserName]
                 ,[UserType]
                 ,[MobileNo]
                 ,[Email]
                 ,[IsLogin]
                 ,[IpAddress]
              FROM  
                  UserMaster      
              WHERE 
                  IsActive=1 AND LoginName LIKE %' + @var + '%';

print @query   
EXEC sp_executesql @Query

Update: If you want to play with dynamic SQL then try ''' instead of ''. Honestly I haven't deal with dynamic SQL for a while since it it terrible and not secure approach (as it was already mentioned in comments)

@BhavinChhatrola, if you have many columns you shall pass all values as variables and rewrite your SP accordingly with use of WHERE and CASE. This can help: http://stackoverflow.com/questions/206484/sql-switch-case-in-where-clause — IgorM, Feb 14 '16 at 15:11

How to pass in sql "like" condition from c# with single quotes

1 Answers1