Jeremy Holovacs Jeremy Holovacs - 4 years ago 177
C# Question

How to protect against SQL Injection with OdbcCommand when the query is not parameterized?

I am writing a generic sqldump utility that takes a DSN and a table name and dumps the contents to a file. It's an internal app so SQL Injection is not a serious threat, but I don't want to have to worry about it. The thing is, the variable part of the query is actually the tablename, so the query is going to look like:

select * from [tablename];

...which I don't imagine will work well with the OdbcCommand's parameterized query support. I am also trying to support all types of DSN's as generically as I can, regardless of the driver on the other side of the DSN.

Is there some universal way to sanitize my
input to protect against all SQL Injection using the OdbcCommand object?

Answer Source

I'd check the user input against the list of tables you know are there, using code roughly like what's posted here to retrieve the table list (code from the link included for posterity):

class Program
  static void Main(string[] args)
  string connectionString = GetConnectionString();
  using (SqlConnection connection = new SqlConnection(connectionString))
   // Connect to the database then retrieve the schema information.
   DataTable table = connection.GetSchema("Tables");

   // Display the contents of the table.
   Console.WriteLine("Press any key to continue.");

That said, I agree with @KeithS above. This is probably a Bad Idea.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download