CODEWITHSUNDEEP
c#databaseparametersspecial-characters
Mikkel Bang
Mikkel Bang

Reputation: 614

Escaping special characters in sql ,c#

I'm working on a program that allows the user to select an MS Access table and then will export it to a .csv file in a particular destination. The problem is I'm getting an error when the selected table contains sql special characters.

I have done some research on the topic and I understand that I need to use parameters but I can't figure out how to actually do so. Here's my code sample:

        string destination = Form2.destination;
        string selectString = "SELECT * FROM " + tablename;                      
        string path = destination + "\\" + tablename + ".csv"; 
        File.Create(path).Dispose();
        TextWriter tw = new StreamWriter(path);

        OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtDataPath.Text);
        connection.Open();
        DataSet myDataSet = new DataSet();

        OleDbCommand command = new OleDbCommand(selectString,connection);

        OleDbDataAdapter adapter = new OleDbDataAdapter(command.CommandText,connection);
        adapter.Fill(myDataSet, tablename);

So far what happens is if a table name that does not have special characters is selected, the csv file is created and looks good, but if it contains something like a '-' then my program produces a "Syntax error in FROM clause" which is because of the special character.

None of the examples I've seen so far work with what I'm trying to do specifically I guess so hopefully someone can help me out. Thanks.

Upvotes: 1

Views: 5098

Answers (5)

Steve
Steve

Reputation: 216273

Your actual query doesn't require parameters because they could be used to supply values for a WHERE condition or for an INSERT/UPDATE statement but not to replace the name of a table or the name of the columns.

However the table name is problematic by itself if it contains special characters (a space for example)
To avoid this problem enclose the table name in square brackets

string selectString = "SELECT * FROM [" + tablename + "]";

Remember that joining string in this way is very dangerous. If the tablename variable comes from a user input not appropriately checked you risk a Sql Injection Attack.

Also do not use string concatenation to build your paths, there is a very handy class for this

string path = Path.Combine(destination, tablename + ".csv");

Finally, try to use the Using Statement to enclose your connection and other disposable objects because it ensure the proper closing and disposing of these objects that otherwise could become a problem for the stability of your program

using(OleDbConnection connection = new OleDbConnection("........"))
using(OleDbCommand command = new OleDbCommand(selectString,connection))
using(OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
    connection.Open();
    DataSet myDataSet = new DataSet();
    adapter.Fill(myDataSet, tablename);
}

Upvotes: 4

Brian
Brian

Reputation: 5119

First, enclose the table name in brackets like this:

string selectString = "SELECT * FROM [" + tablename + "]";

Second, parameterize your query like this: 

command.Parameters.Add("@parametername1here", OleDbType.VarChar).value = "somevalue"
command.Parameters.Add("@parametername2here", OleDbType.VarChar).value = "someothervalue"

Upvotes: 2

ashes999
ashes999

Reputation: 10163

You have two options, from what I can see.

The first, easier option, is to just sanitize the table name:

tableName = tableName.replace("-", "\\-");

The second option, which may not be appropriate here, is to let C# automagically escape the name for you by using a SqlParameter instance. Something like (pseudoish code):

    string selectString = "SELECT * FROM @tableName";                      
    // [...]
    OleDbCommand command = new OleDbCommand(selectString, connection);
    command.AddParameter(new SqlParamter("@tableName", tablename); // auto-escapes

Upvotes: 0

Geeky Guy
Geeky Guy

Reputation: 9399

Encase the table name with brackets. The following should be a valid command:

SELECT foo FROM [Le Tablé]

You can add brackets to table names that don't have special characters too, so just encase them all anyway and be happy.

Upvotes: 2

TyCobb
TyCobb

Reputation: 9089

Try wrapping your table name in brackets.

SELECT * FROM [Some Table Name]

Upvotes: 1

Related Questions