DataAdapter.SelectCommand与SQL注入：导出功能优化的安全疑问

Great question—this is a common pitfall when dynamically building SQL for column selection, so let’s break this down clearly.

First: Yes, this approach has SQL injection risks #

When you directly concatenate columns from a (potentially compromised) config file into your SQL string, you’re opening yourself up to malicious input. For example, if an attacker modifies the config to include something like:

ID, (SELECT password FROM secret_users), 1; DROP TABLE WHATEVER--

Your resulting SQL would become:

SELECT ID, (SELECT password FROM secret_users), 1; DROP TABLE WHATEVER-- FROM WHATEVER WHERE ID=:ID

This could execute two harmful actions: first stealing sensitive data, then deleting your table. Even with OracleDataAdapter, this leads to data exfiltration, table destruction, or other malicious outcomes—Oracle allows multiple statements in a single command depending on configuration, and even without that, subqueries can still leak sensitive information.

Removing spaces won’t protect you #

Stripping spaces from column names is ineffective for SQL injection prevention. Attackers can bypass this using:

• Comment syntax: ID/**/(SELECT/**/password/**/FROM/**/secret_users)
• URL-encoded whitespace: ID%2C(SELECT%20password%20FROM%20secret_users)
• No spaces at all (valid in many SQL dialects): ID,(SELECTpasswordFROMsecret_users)

Worse, stripping spaces could break legitimate column names if your schema uses columns with spaces (e.g., User Name—though this is bad practice, it’s possible).

The safe solution: Whitelist validation #

The only reliable way to prevent this is to validate incoming column names against a whitelist of actual columns in your table. Here’s how to implement this:

1. Fetch valid column names from the database (cache this result to avoid repeated queries):

   private HashSet<string> GetValidTableColumns(string tableName, string schemaName)
   {
       var validColumns = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
       using (var cmd = ConFactory.CreateOracleCommand())
       {
           // Query Oracle's system catalog to get valid columns for the table
           cmd.CommandText = @"
               SELECT COLUMN_NAME 
               FROM ALL_TAB_COLUMNS 
               WHERE TABLE_NAME = :TABLE_NAME 
               AND OWNER = :SCHEMA_NAME";
           cmd.Parameters.Add("TABLE_NAME", OracleDbType.Varchar2).Value = tableName;
           cmd.Parameters.Add("SCHEMA_NAME", OracleDbType.Varchar2).Value = schemaName;
           
           using (var reader = cmd.ExecuteReader())
           {
               while (reader.Read())
               {
                   validColumns.Add(reader.GetString(0));
               }
           }
       }
       return validColumns;
   }
   

2. Filter your input columns against the whitelist:

   // Get cached valid columns for your table
   var validColumns = GetValidTableColumns("WHATEVER", "YOUR_SCHEMA_NAME");
   // Filter incoming columns to only include valid ones
   var safeColumns = columns
       .Select(col => col.Trim())
       .Where(col => validColumns.Contains(col))
       .ToList();
   
   // Handle edge case: no valid columns selected
   if (!safeColumns.Any())
   {
       throw new InvalidOperationException("No valid columns specified for export.");
       // Or fall back to a default set of columns
       // safeColumns = new List<string> { "ID", "DEFAULT_COLUMN" };
   }
   
   // Build your safe SQL string
   string sColumns = string.Join(",", safeColumns);
   var sql = string.Format("SELECT {0} FROM WHATEVER WHERE ID=:ID", sColumns);
   

3. Secure your config file:
   Even with validation, restrict file system permissions on your config file so only trusted users can modify it. This reduces the chance of malicious changes in the first place.

Why parameterization doesn’t work here #

Note that you can’t parameterize column names—SQL parameters only work for values (like the :ID in your query), not for identifiers (columns, tables). So whitelisting is the only viable approach here.

内容的提问来源于stack exchange，提问作者Stokke