CODEWITHSUNDEEP
c#ado.netodbc
user610217
user610217

Reputation:

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 tablename input to protect against all SQL Injection using the OdbcCommand object?

Upvotes: 1

Views: 1922

Answers (4)

Chris Shain
Chris Shain

Reputation: 51329

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.
   connection.Open();
   DataTable table = connection.GetSchema("Tables");

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

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

Upvotes: 2

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112324

If the table name is enclosed in [ ] then just do not allow table names to contain "]". ] could be used by malicious people to terminated the sql command and to introduce dangerous code.

If you are constructing the sql like this

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

and the tablename is defined like this

string tablename = "tablename]; DELETE FROM [tablename";

The resulting sql becomes

SELECT * FROM [tablename]; DELETE FROM [tablename];

However, this is only possible if the table name contains a ].

Note:

If you are replacing string values like this, then replacing a single quote by two single quotes makes it safe too.

string sql = "SELECT * FROM tbl WHERE Name = '" + input.Replace("'","''") + "'";

Upvotes: 0

Dmitry Frenkel
Dmitry Frenkel

Reputation: 1756

You could first query the information_schema to find out if the table exists:

select * 
from information_schema.tables 
where table_schema = @your_database_name and table_name = @table_name

This query can be parameterized and is NOT prone to SQL injections.

Following that, you can issue your select * from @table_name query.

Upvotes: 0

user743382
user743382

Reputation:

The only special character in a [] quoted identifier for SQL Server is ], and it can be escaped by passing ]]. So for that, "select * from [" + tableName.Replace("]", "]]") + "];" should be safe. Other systems, however, may use other escape mechanisms, so this is not a full solution if you want to connect to a different type of database.

Alternatively, consider each character, and see if it is a valid character for table names you wish to support. If you say table names only contain letters, digits, and/or whitespace, then SQL injection is not possible, because you'll never be able to unquote the [quoted table name].

Upvotes: 0

Related Questions