76842

OdbcCommand. Parametrization with table name

I have a problem doing a parametrized sql query to a OdbcConnection to a odbc driver in C#. The column name and table name will be configurable from a web gui so I want it protected from injections

using (OdbcCommand command = connection.CreateCommand()) { command.CommandText = "SELECT ? FROM ?"; command.CommandTimeout = SynchTimeout; command.CommandType = CommandType.Text; command.Parameters.Add(new OdbcParameter(string.Empty, "User")); command.Parameters.Add(new OdbcParameter(string.Empty, "TableName")); OdbcDataReader reader = command.ExecuteReader(); while (reader.Read()) { // TODO: Do something clever.. } }

Using odcparameters for the columname "User" seems ok. but if I add the table name as a paramter with the placeholder '?' I get the following error: ERROR [HY000] [Microsoft][ODBC Excel Driver] Parameter 'Pa_RaM002' specified where a table name is required.

Does anyone know how to pass a table name safe in this situation if it cannot be a OdbcParameter?

Answer1:

I don't think it's possible to parameterize table names and column names in SQL. So, in some way or another you'll still resort to string concatenation to build dynamic SQL statements.

What is possible though is for you to do some checks prior to executing the statement. I see two types of checks you could perform:

<strong>1. Whitelist check (the better solution)</strong>

If possible, have a list of tables and columns that you allow to be used in this manner. When a user specifies the table and column, make sure you only allow elements in the list.

<strong>2. Dynamic check (the risky solution)</strong>

Apply this approach only if the names of the tables/columns are not known beforehand (ex: created dynamically) and it is impossible to build a whitelist. Otherwise, go for the whitelist approach.

You could check that the configured table and column exist in the database.

For example, if you are using SQL Server you could do this by querying the Information Schema views, like so:

select top 1 COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @tableNameParameter and COLUMN_NAME = @columnNameParameter and <additional criteria*>

To avoid running the check too often, you could perform these checks as a validation in the web page that allows configuring the table and column names.

*<strong>WARNING:</strong> if you only validate that the table and column exist, that would give the users the ability to discover all tables in your database. To avoid this, you could add an additional criteria in your SQL, to make sure you only select tables that were meant to be used in this way. For example, all dynamic tables could have a certain prefix, so you could do [...] and TABLE_NAME like 'prefix%'

<hr>

Regardless of which solution you choose, be aware that it is critical from a security point of view. You should be very careful which components of the system are allowed to write the custom table/column values and apply the validation in each of these points.

Recommend

  • mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mys
  • Authentication not allowed after clearing cache of symfony2
  • create database using sudo mysql -u=root
  • Connection failed: Access denied for user ''username'@'localhost' (using pa
  • Prompting for SAS ODBC connection password when running in batch mode
  • Perl Dbi and stored procedures
  • python - connecting to a database with pyodbc - not working
  • ColdFusion - DateTime Format with GMT offset
  • Creating UDF with VSTO in Excel
  • VBA Dir function not working on Excel 2010
  • Outlook to Excel hyperlink issue
  • C# Excel interop - how to test if interop object is still working and performing a task?
  • powershell Get-Counter -ComputerName parameter on Windows 7
  • Xamarin MonoAndroid Azure mobile service InsertAsync
  • pip in virtualenv gets ConnectTimeoutError
  • Private IP address in reserved subnet range
  • How To Customize ASP.NET Chart Databound To SqlDataSource
  • pyodbc doesn't report sql server error
  • Needing to do .toArray() to get output of mongodb .find() on key name not value
  • SignalR .NET Client Invoke throws an exception
  • Using variable in a value field in jMeter
  • Using $this when not in object context
  • java.lang.NoClassDefFoundError: com.parse.Parse$Configuration$Builder on below Lollipop versions
  • Array.prototype.includes - not transformed with babel
  • Volley JsonObjectRequest send headers in GET Request
  • Accessing IRQ description array within a module and displaying action names
  • 'TypeError' while using NSGA2 to solve Multi-objective prob. from pyopt-sparse in OpenMDAO
  • Can a Chrome extension content script make an jQuery AJAX request for an html file that is itself a
  • Symfony2: How to get request parameter
  • Build own AppleScript numerical error handling
  • Websockets service method fails during R startup
  • ORA-29908: missing primary invocation for ancillary operator
  • Google cloud sdk not working when python points python3
  • Is there a mandatory requirement to switch app.yaml?
  • Codeigniter doesn't let me update entry, because some fields must be unique
  • Hits per day in Google Big Query
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Linking SubReports Without LinkChild/LinkMaster
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • Does armcc optimizes non-volatile variables with -O0?