using parameters to prevent SQL injention

problem using indexing service

[Paco]

Hi,

I want to use indexing service for the serch page of a website.

I found the following code to do it:

[support.microsoft.com...]

If I'm not wrong that looks sensible to sql injection. I tried to use parameters to avoid it but I'm having problems:

This is my code:

strQuery = "Select DocTitle,Filename,Characterization, Size,PATH,URL, rank from Scope() where FREETEXT('?') ORDER BY rank DESC";

string connstring = "Provider=MSIDXS.1;Integrated Security .='';Data Source="+strCatalog;

try
{
OleDbConnection conn = new OleDbConnection(connstring);
conn.Open();

//
OleDbCommand dataCommand = new OleDbCommand(strQuery, conn);

OleDbDataAdapter cmd = new OleDbDataAdapter();

//
dataCommand.Parameters.Add("@p1", OleDbType.VarChar).Value = TextBoxSearch.Text;
cmd.SelectCommand = dataCommand;

DataSet testDataSet = new DataSet();

cmd.Fill(testDataSet, "SearchResults");
DataView source = new DataView(testDataSet.Tables[0]);

DataGridResults.DataSource = source;
DataGridResults.DataBind();

...
*****************************************************
I'm getting the following error:

The ICommandWithParameters interface is not supported by the 'MSIDXS.1' provider. Command parameters are unsupported with the current provider.

*****************************************************
Is there any way to use parameters with indexing services?

[mrMister]

I don't see why you are worried about SQL Injection.

You have a read only catalog, so there's no chance of them modifying it, and all the data in the catalog is publically accessable anyway (so there's no chance of them getting information that they're not authorised to).

Am I missing something?

[Paco]

I'm worried because of this line of code:

strQuery = "Select DocTitle,Filename,Size,PATH,URL from Scope() where FREETEXT('" +TextBox1.Text+ "')";

as you can inject SQL there.

I don't know what could be done through that injection. If it's just seeing the catalog, as you said that's ok. Is it something else? I don't know.

[mrMister]

I would assume you've only got read access to the catalog (make sure first), in which case I wouldn't be concerned about SQL injection. All they'll be able to do with SQL injection is read data, and there's nothing in the catalog that they can't access through the site search.

[Prolific]

Baring the fact it's all publicly accessible data, I don't think its a good idea to get in the habbit of bad programming.

You need to scrub the data and remove anything charactors that can be used in SQL injection - namely the apostrophe.

[mattglet]

It's so disgustingly easy to implement measures that prevent injection. To even mention a solution that doesn't do it is silly.

Paco-
Option 1: Build a utility that replaces 1 single quote with 2 single quotes in your dynamic SQL string.

Option 2: Put your query in a stored procedure (as it should be anyway).

[aspdaddy]

Dont forget to test for injected SQL comments "--"

Select DocTitle,Filename,Size,PATH,URL from Scope() where FREETEXT(' --- Drop Table myTable

[mrMister]

Mattglet, please explain how you create stored procedures in the Windows Indexing Service

As ASPDaddy has mentioned, there's more than apostrophe's that you have to guard against when protecting aginst SQL injection. I'd assume that the original poster would do apostrophe replacement as this is a web seach and people will use apostrophes in their search.

As I said before, I wouldn't fret about it too much. There's not much a hacker can do with the Indexing Service.

[mattglet]

My bad... forgot he was using MIS.

[Paco]

Mattglet, please explain how you create stored procedures in the Windows Indexing Service

As ASPDaddy has mentioned, there's more than apostrophe's that you have to guard against when protecting aginst SQL injection. I'd assume that the original poster would do apostrophe replacement as this is a web seach and people will use apostrophes in their search.

As I said before, I wouldn't fret about it too much. There's not much a hacker can do with the Indexing Service.

Glad the situation is not that dangerous as I thought.

I also agree there are more things to injection than the single quote, some are pretty ingenious and you just can't count on having a bad word list as you can be sure you'll miss some.