Full Text Search query without stored procedure

Discussion in 'ASP .Net' started by Mate, Jul 15, 2009.

  1. Mate

    Mate Guest

    I am trying to execure this query in C#, but I can not. It is well when I
    use string with quotes instead @SearchTerm parameter (it is not good because
    of SQL Injection).

    Can I use full text search ad-hoc query with parameters or I need to use
    stored procedures?

    This is my code:

    SQL = @"SELECT * FROM IndexedPages a JOIN CONTAINSTABLE(IndexedPages,
    (Title, PageText), 'ISABOUT(@SearchTerm WEIGHT(.1))') ct ON a.ID = ct.[KEY]
    ORDER BY RANK DESC";

    myCommand = new SqlCommand(SQL, SqlConn);

    myCommand.Parameters.Add(new SqlParameter("@SearchTerm", SearchTerm));

    DAdapter.SelectCommand = myCommand;

    DSet = new DataSet(); DAdapter.Fill(DSet);
     
    Mate, Jul 15, 2009
    #1
    1. Advertising

  2. On Jul 15, 9:07 am, "Mate" <> wrote:
    > I am trying to execure this query in C#, but I can not. It is well when I
    > use string with quotes instead @SearchTerm parameter (it is not good because
    > of SQL Injection).
    >
    > Can I use full text search ad-hoc query with parameters or I need to use
    > stored procedures?
    >
    > This is my code:
    >
    > SQL = @"SELECT * FROM IndexedPages a JOIN CONTAINSTABLE(IndexedPages,
    > (Title, PageText), 'ISABOUT(@SearchTerm WEIGHT(.1))') ct ON a.ID = ct.[KEY]
    > ORDER BY RANK DESC";
    >
    > myCommand = new SqlCommand(SQL, SqlConn);
    >
    > myCommand.Parameters.Add(new SqlParameter("@SearchTerm", SearchTerm));
    >
    > DAdapter.SelectCommand = myCommand;
    >
    > DSet = new DataSet(); DAdapter.Fill(DSet);


    Use string concatenation:

    SQL = @"SELECT * FROM IndexedPages a JOIN CONTAINSTABLE(IndexedPages,
    (Title, PageText), 'ISABOUT(" + x + @" WEIGHT(.1))') ct ON a.ID = ct.
    [KEY]
    ORDER BY RANK DESC";

    and do not forget about sql injections, replace ['] by [''] and [;] by
    [ ]

    In a stored procedure you will also need to concatenate the strings
    because you can't put the parameter inside the literal value. You will
    need to have something like this

    declare @x nvarchar(50)
    set @x='ISABOUT(' + ... +' WEIGHT(.1))'

    SELECT * FROM files a JOIN CONTAINSTABLE(files,
    (filedata), @x) ct ON a.ID = ct.[KEY]
    ORDER BY RANK DESC

    Hope this helps
     
    Alexey Smirnov, Jul 15, 2009
    #2
    1. Advertising

  3. "Mate" <> wrote in news:h3jv7m$mge$:

    > I am trying to execure this query in C#, but I can not. It is well
    > when I use string with quotes instead @SearchTerm parameter (it is not
    > good because of SQL Injection).
    >
    > Can I use full text search ad-hoc query with parameters or I need to
    > use stored procedures?
    >
    > This is my code:
    >
    > SQL = @"SELECT * FROM IndexedPages a JOIN CONTAINSTABLE(IndexedPages,
    > (Title, PageText), 'ISABOUT(@SearchTerm WEIGHT(.1))') ct ON a.ID =
    > ct.[KEY] ORDER BY RANK DESC";
    >
    > myCommand = new SqlCommand(SQL, SqlConn);
    >
    > myCommand.Parameters.Add(new SqlParameter("@SearchTerm", SearchTerm));
    >
    > DAdapter.SelectCommand = myCommand;
    >
    > DSet = new DataSet(); DAdapter.Fill(DSet);



    Parameters do not work the way you are attempting. They are not merely
    pointers in a string.

    Of the options you present, I would opt for a stored procedure over
    concatenation, but that is a personal preference. Alexy has given
    another good option, but heed his injection warning.

    If nothing else, run through some form of encoder before submitting.


    --
    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    Twitter: @gbworld
    Blog: http://gregorybeamer.spaces.live.com

    *******************************************
    | Think outside the box! |
    *******************************************
     
    Gregory A. Beamer, Jul 15, 2009
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Brenda Pasquarello

    MS Access Query - use like stored procedure?

    Brenda Pasquarello, Mar 7, 2006, in forum: ASP .Net
    Replies:
    1
    Views:
    3,733
    Michael Bohman
    Mar 8, 2006
  2. Samuel R. Neff
    Replies:
    2
    Views:
    609
    bradley
    Jun 10, 2005
  3. Mike P
    Replies:
    0
    Views:
    3,351
    Mike P
    Jun 19, 2006
  4. psycho
    Replies:
    2
    Views:
    3,865
    jan.skacel
    Nov 20, 2008
  5. Machelle Chandler

    Using query string to pass a value to a stored procedure parameter

    Machelle Chandler, Oct 21, 2003, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    181
    Machelle Chandler
    Oct 21, 2003
Loading...

Share This Page