C# - Using OleDbParameter on table name

Question

I want to protect my app from SQL injection. I want to use OleDbParameter in a SQL query for the table name ({1}).

The problem is that it doesn't work (error in FROM or something like that). I can pass the OleDbParameter in {3} thought. Example:

IDbCommand cmd = m_oConnection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = String.Format("SELECT {0} FROM {1} WHERE {2}={3}",
                "ParentID",
                "?",
                sWhere,
                "?"
);
cmd.Parameters.Add(new OleDbParameter("@sTable", sTable));
cmd.Parameters.Add(new OleDbParameter("@id", id));

What can I do? Am I forced to write a function which escapes some SQL characters by hand? If yes, where can I find a perfect function?

Thanks

You're aware, I assume, that the dangers of SQL Injection with a Jet/ACE data store are minuscule in comparison to other database engines, right? See http://stackoverflow.com/questions/512174/non-web-sql-injection/522382#522382 — David-W-Fenton, Jul 10 '11 at 02:16

score 2 · Accepted Answer · answered Jul 08 '11 at 15:42

So you know that you can't parameterize table names but you could do this

cmd.CommandText = String.Format("SELECT {0} FROM [{1}] WHERE {2}={3}",
                "ParentID",
                sTable,
                sWhere,
                "?"

But this is dangerous if and only if sTable comes from user input. If you directly control the sTable you don't have to worry about it.

If it does indeed come from user input you'll need to protect yourself. The easiest way is to make sure that sTable is a valid table, Attached table, or query name

To do that just execute

 SELECT Name FROM Myssobjects Where Type in (1,5,6,)

to get the list of valid values for sTable.

Depending on your application you could probably execute it once and cache the results so you don't have do it every time you call this method.

Kevin Burton · Answer 2 · 2011-07-08T14:37:57.420

1

You will need to use dynamic sql to generate your command.

but as you are passing in the table name it is easily possible to embed a sql string in the parameter though, this is not good.

see: http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx

'Anything placed into a parameter will be treated as field data, not part of the SQL statement'

edited Jul 08 '11 at 14:37

answered Jul 08 '11 at 14:21

Kevin Burton

11,676
2
24
37

C# - Using OleDbParameter on table name

2 Answers2

Linked