Full Text Search query without stored procedure

  • Thread starter Thread starter Mate
  • Start date Start date
M

Mate

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);
 
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);
Click to expand...

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
 
Mate said:
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);
Click to expand...


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! |
*******************************************
 
Back
Top