Escaping special characters in sql ,c#

Question

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.

score 4 · Accepted Answer · edited May 23 '17 at 12:18

4

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);
}

edited May 23 '17 at 12:18

Community

1
1

answered May 24 '13 at 20:40

Steve

213,761
22
232
286

I thought the point of using parameters was to handle the special characters that could be in tablename and prevent injection attack? – Mikkel Bang May 24 '13 at 20:50
1

No, you can't use a parameter to replace the tablename or columns name. Yes you can and __should__ use a parameter to pass a value in WHERE/INSERT or UPDATE statements – Steve May 24 '13 at 20:53
This probably has to be the best answer. Especially since it touches on the injection aspect. I think the way I would do this would probably be a hard coded set of tables that are allowed to be output if the entered value does not equal one of those values then throw an error or something else. I don't know the specific system you are using but allowing the users to output any table in the database to a CSV is not a good idea. As for escaping the table names appropriately you should be able to use square brackets as this post and others have said. – Midpipps May 24 '13 at 21:00
It all works great now, thanks! I edited my path assignment like you said but why should I not have used string concatenation to do it? Is it just bad practice, bad looks, or can it actually create problems? – Mikkel Bang May 24 '13 at 21:05
Problems. If you plan to port your program on a different OS where the separator is not the backslash your code will be invalid. However, apart from the extreme cases, I think that is a good practice to use a Framework method that could also help a bit with invalid characters. Read the remarks section for [Path class](http://msdn.microsoft.com/en-us/library/fyy7a5kt.aspx) on MSDN – Steve May 24 '13 at 21:12

score 2 · Answer 2 · answered May 24 '13 at 20:40

2

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.

answered May 24 '13 at 20:40

Geeky Guy

9,229
4
42
62

score 2 · Answer 3 · answered May 24 '13 at 20:42

2

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"

answered May 24 '13 at 20:42

Brian

5,069
7
37
47

if the only input is the table the user selects from a listbox, what is my parameter name and value? – Mikkel Bang May 24 '13 at 20:53
Likely whatever the value is in the drop-down, no? For example, `.value = dropdownlist.Value;'. – Brian May 24 '13 at 21:12

score 1 · Answer 4 · answered May 24 '13 at 20:39

1

Try wrapping your table name in brackets.

SELECT * FROM [Some Table Name]

answered May 24 '13 at 20:39

TyCobb

8,909
1
33
53

score 0 · Answer 5 · answered May 24 '13 at 20:41

0

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

answered May 24 '13 at 20:41

ashes999

9,925
16
73
124

1

regarding your method of parameter use, I've tried that before and I get this error: "Syntax error in query. Incomplete query clause." – Mikkel Bang May 24 '13 at 20:57
@user2390773 you may have an issue with your code. Can you post the exact code in your question? – ashes999 May 24 '13 at 21:08

Escaping special characters in sql ,c#

5 Answers5