none
How to remove Quotes from a query RRS feed

  • Question

  •  

    Hi,

     

    i have a query like

     

    select * from table1 where @variable1

     

    and variable1 id holding the value id=1

     

    so what i want is select * from table1 where id=1

     

    but here the values variable1 is passesed from a c# program which encloses the vaule within single quotes.

     

    so what i get is select * from table1 where 'id=1'

     

    how to correct this?

     

    hope my question is clear.

     

    Thanks

    Friday, April 18, 2008 12:33 PM

Answers

  •  Anish Jose wrote:

     

    Hi,

     

    i have a query like

     

    select * from table1 where @variable1

     

    and variable1 id holding the value id=1

     

    so what i want is select * from table1 where id=1

     

    but here the values variable1 is passesed from a c# program which encloses the vaule within single quotes.

     

    so what i get is select * from table1 where 'id=1'

     

    how to correct this?

     

    hope my question is clear.

     

    Thanks

    Anish,

     

    You cannt query a table in SQL server using a dynamic column...  You would have to use dynamic sql to do this.  There is an increased risk for sql injection attack when using dynamic sql.  This can lead to total database take over or manipulation of SMOs (Sql Server Management Objects).

     

    This fails:

    Code Snippet

    declare @var varchar(20)

    set @var = 'id=1'

    set @var = replace(@var,'''','')

    select *

    from table1

    where

     

     

    This works:

    Code Snippet

    declare @var varchar(20),

    @sql nvarchar(100)

     

    set @var = 'id=1'

    set @var = replace(@var,'''','')

    set @sql = N'

    select *

    from table1

    where ' + @var

     

    exec sp_executesql @sql

     

     

    Friday, April 18, 2008 3:17 PM
    Moderator

All replies

  • SET @variable1 = REPLACE(@variable1, '''', '')
    Friday, April 18, 2008 12:38 PM
  •  Shelly Noll wrote:
    SET @variable1 = REPLACE(@variable1, '''', '')

     

    Thanks for the response

     

    Is it REPLACE(@variable1, {double quote}{single quote}{double quote},{double quote}) ??

    Friday, April 18, 2008 12:48 PM
  • Sorry...I should have been more clear.  All of those quotes are single quotes.  So it's 4 single quotes in the 2nd parameter and 2 single quotes in the third.
    Friday, April 18, 2008 12:55 PM
  •  Anish Jose wrote:

     

    Hi,

     

    i have a query like

     

    select * from table1 where @variable1

     

    and variable1 id holding the value id=1

     

    so what i want is select * from table1 where id=1

     

    but here the values variable1 is passesed from a c# program which encloses the vaule within single quotes.

     

    so what i get is select * from table1 where 'id=1'

     

    how to correct this?

     

    hope my question is clear.

     

    Thanks

    Anish,

     

    You cannt query a table in SQL server using a dynamic column...  You would have to use dynamic sql to do this.  There is an increased risk for sql injection attack when using dynamic sql.  This can lead to total database take over or manipulation of SMOs (Sql Server Management Objects).

     

    This fails:

    Code Snippet

    declare @var varchar(20)

    set @var = 'id=1'

    set @var = replace(@var,'''','')

    select *

    from table1

    where

     

     

    This works:

    Code Snippet

    declare @var varchar(20),

    @sql nvarchar(100)

     

    set @var = 'id=1'

    set @var = replace(@var,'''','')

    set @sql = N'

    select *

    from table1

    where ' + @var

     

    exec sp_executesql @sql

     

     

    Friday, April 18, 2008 3:17 PM
    Moderator