Open Excel sheet with OleDbAdaptor and query parameters

Question

This was my previous method for opening an excel sheet:

public static System.Data.DataTable GetEntireSheet(string fileName,
    string sheetName)
{
    string connectionString = GetConnectionString(fileName);
    System.Data.DataTable excelTable = new System.Data.DataTable();
    excelTable.Locale = CultureInfo.InvariantCulture;

    using (OleDbConnection connection =
        new OleDbConnection(connectionString))
    {
        using (OleDbDataAdapter adaptor = new OleDbDataAdapter(
            string.Format(CultureInfo.InvariantCulture,
                "Select * from [{0}$]", sheetName),
            connection))
        {
            adaptor.Fill(excelTable);
        }
    }

    return excelTable;
}

This works fine but it also generates code analysis warning:

CA2100 The query string passed to 'OleDbDataAdapter.OleDbDataAdapter(string, OleDbConnection)' in 'ExcelWrapper.GetEntireSheet(string, string)' could contain the following variables 'string.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}$]", sheetName)'. If any of these variables could come from user input, consider using a stored procedure or a parameterized SQL query instead of building the query with string concatenations.

The sheetname is not coming from user input, so I could suppress the warning, but suppressing warnings is a practice I try to avoid as much as possible.

I've tried to update the method with the following:

string query = "SELECT * FROM [?]";
string parameter = string.Format(
    CultureInfo.InvariantCulture, "{0}$", sheetName);
using (OleDbCommand command =
    new OleDbCommand(query, connection))
{
    command.Parameters.Add("?", OleDbType.BSTR).Value =
        parameter;

    using (OleDbDataAdapter adaptor =
        new OleDbDataAdapter(command))
    {
        adaptor.Fill(excelTable);
    }
}

It looks suspicious, though and it also generates an OleDbException of:

The Microsoft Access database engine could not find the object '?'. Make sure the object exists and that you spell its name and the path name correctly. If '?' is not a local object, check your network connection or contact the server administrator.

What is the right way to call this with query parameters?

Answer 1

You cannot use a parameter to represent a table name (sheet name). You use parameters only when you want to pass VALUES for INSERT, UPDATE and in WHERE clause.

However, as pointed out, by your code analysis tool you can use a white list of table names where your user can choose from without typing anything.

You use GetOleDbSchemaTable from the OleDbConnection and fill a combo with a DropDownStyle of DropDownList

using(OleDbConnection excel_con = new OleDbConnection(connectionString))
using(OleDbCommand cmd = new OleDbCommand())
{
    excel_con.Open();
    DataTable result = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    var names = result.AsEnumerable().Select(x => x.Field<string>("TABLE_NAME").TrimEnd('$')).ToList();
    comboBoxTables.DataSource = names;
}

Now your code could use the SelectedItem from the comboBoxTables and use the string concatenation approach without problems with Sql Injection.