Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Full Text Search query without stored procedure

Reply
Thread Tools

Full Text Search query without stored procedure

 
 
Mate
Guest
Posts: n/a
 
      07-15-2009
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);


 
Reply With Quote
 
 
 
 
Alexey Smirnov
Guest
Posts: n/a
 
      07-15-2009
On Jul 15, 9:07*am, "Mate" <(E-Mail Removed)> 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
 
Reply With Quote
 
 
 
 
Gregory A. Beamer
Guest
Posts: n/a
 
      07-15-2009
"Mate" <(E-Mail Removed)> wrote in news:h3jv7m$mge$(E-Mail Removed):

> 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! |
*******************************************
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
LINQ Query : How to return a single value from a stored procedure psycho ASP .Net 2 11-20-2008 11:57 PM
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
MS Access Query - use like stored procedure? Brenda Pasquarello ASP .Net 1 03-08-2006 12:46 PM
Options for generic full-text search without using database-specific full-text engine? Samuel R. Neff ASP .Net 2 06-10-2005 06:53 PM
Using query string to pass a value to a stored procedure parameter Machelle Chandler ASP .Net Datagrid Control 0 10-20-2003 11:22 PM



Advertisments