I'm working on a .NET application that needs to execute SQL parameterized by database objects like tables or columns. The application supports both the Microsoft SQL Server and Oracle ADO.NET providers, in an environment where it cannot assume anything about the database. DB object names can come from anywhere, usually the DB itself. As far as I am aware, there is no nice way to use parameters where an identifier is expected without using PL/SQL which I'd like to avoid due to extra overhead in terms of maintenance and such.
Using the Oracle ADO.NET provider, here is a simple but somewhat contrived example of what I mean:
using (OracleCommand cmd = connection.CreateCommand())
{
// Potential SQL injection vulnerability?
cmd.CommandText = $"SELECT id, name FROM {tableName} WHERE name = :name";
// Unfortunately not valid :(
//cmd.CommandText = $"SELECT id, name FROM :tableName WHERE name = :name";
//cmd.Parameters.Add("tableName", tableName);
cmd.Parameters.Add("name", name);
return cmd.ExecuteReader();
}
Here is actual code I've written using Oracle PL/SQL:
// Wish we could use this:
//const string cmdText = "ALTER SESSION SET CURRENT_SCHEMA = :schemaName"
// Or perhaps something like this:
//const string cmdText = $"ALTER SESSION SET CURRENT_SCHEMA = {Something.Validate(schemaName)}"
// Alas:
const string cmdText = @"
DECLARE
v_schema_name VARCHAR2(128);
BEGIN
BEGIN
-- Try literal schema name first (handles quoted schemas)
v_schema_name := dbms_assert.schema_name(:schemaName);
EXCEPTION WHEN OTHERS THEN
-- Fall back to uppercase version for unquoted schemas
v_schema_name := dbms_assert.schema_name(UPPER(:schemaName));
END;
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=""' || v_schema_name || '""';
END;";
using (OracleCommand cmd = connection.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.Parameters.Add("schemaName", schemaName);
cmd.ExecuteNonQuery();
}
A moderator on this site kindly flagged this question as a duplicate of How to Safely Parameterize Table Names in C# to prevent SQL Injection? which brought to my attention that SqlCommandBuilder.QuoteIdentifier can be used with SQL Server to somewhat safely insert identifiers dynamically, but only when the server's collation is case-insensitive. Otherwise, it seems fairly easy to validate an SQL Server identifier based on the naming rules for "regular identifiers", except for checking reserved words:
The identifier must not be a Transact-SQL reserved word. SQL Server reserves both the uppercase and lowercase versions of reserved words. [...] The words that are reserved depend on the database compatibility level. This level can be set by using the ALTER DATABASE compatibility level statement.
This question was also flagged as a duplicate of How to check for valid oracle table name using sql/plsql, again which I'd like to avoid due to the extra overhead I demonstrated earlier. Oracle identifier rules are even more complicated as they depend on the DB version, the character set the DB was installed with, and also cannot be reserved words. Naturally I could just validate the Oracle identifier without taking character encoding into account (by assuming Unicode), but then I'm worried about encoding-related vulnerabilities that are beyond my comprehension.
So the question is: Are my fears unfounded? If not, how do I properly validate (unquoted) identifiers?
Here is my preliminary and untested validation code for reference (there are probably errors but this is just to demonstrate my naive approach to identifier validation):
using System;
using System.Collections.Generic;
using System.Text;
namespace DbProviderAbstractions
{
public abstract class DbProvider
{
/// <summary>
/// Validates whether the given identifier can be used unquoted.
/// </summary>
/// <param name="identifier">The identifier to validate.</param>
/// <returns>
/// <c>true</c> if <paramref name="identifier"/> is a valid unquoted identifier,
/// <c>false</c> if <paramref name="identifier"/> must be quoted.
/// </returns>
/// <remarks>
/// If <paramref name="identifier"/> is already quoted,
/// this method shall return <c>false</c>
/// (an identifier containing quotes is not a valid unquoted identifier).
/// </remarks>
public abstract bool Validate(string identifier);
/// <summary>
/// Throw if a given identifier is not valid as an unquoted identifier.
/// </summary>
/// <param name="identifier">The identifier to validate.</param>
/// <returns>The unmodified identifier.</returns>
/// <exception cref="ArgumentOutOfRangeException">When the identifier contains special characters that must be quoted.</exception>
public string Unquoted(string identifier)
{
return Validate(identifier) ? identifier : throw new ArgumentOutOfRangeException("Nooooooo!");
}
}
public class SqlProvider : DbProvider
{
/// <summary>
/// SQL Server reserved words as of SQL Server 2022.
/// </summary>
public static readonly ISet<string> ReservedWords = new HashSet<string>(StringComparer.OrdinalIgnoreCase)
{
"ABSOLUTE", "ACTION", "ADA", "ADD", "ADMIN", "AFTER", "AGGREGATE", "ALIAS", "ALL", "ALLOCATE", "ALTER", "AND", "ANY", "ARE", "ARRAY", "AS", "ASC", "ASENSITIVE", "ASSERTION", "ASYMMETRIC", "AT", "ATOMIC", "AUTHORIZATION", "AVG",
"BACKUP", "BEFORE", "BEGIN", "BETWEEN", "BINARY", "BIT", "BIT_LENGTH", "BLOB", "BOOLEAN", "BOTH", "BREADTH", "BREAK", "BROWSE", "BULK", "BY",
"CALL", "CALLED", "CARDINALITY", "CASCADE", "CASCADED", "CASE", "CAST", "CATALOG", "CHAR", "CHAR_LENGTH", "CHARACTER", "CHARACTER_LENGTH", "CHECK", "CHECKPOINT", "CLASS", "CLOB", "CLOSE", "CLUSTERED", "COALESCE", "COLLATE", "COLLATION", "COLLECT", "COLUMN", "COMMIT", "COMPLETION", "COMPUTE", "CONDITION", "CONNECT", "CONNECTION", "CONSTRAINT", "CONSTRAINTS", "CONSTRUCTOR", "CONTAINS", "CONTAINSTABLE", "CONTINUE", "CONVERT", "CORR", "CORRESPONDING", "COUNT", "COVAR_POP", "COVAR_SAMP", "CREATE", "CROSS", "CUBE", "CUME_DIST", "CURRENT", "CURRENT_CATALOG", "CURRENT_DATE", "CURRENT_DEFAULT_TRANSFORM_GROUP", "CURRENT_PATH", "CURRENT_ROLE", "CURRENT_SCHEMA", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_TRANSFORM_GROUP_FOR_TYPE", "CURRENT_USER", "CURSOR", "CYCLE",
"DATA", "DATABASE", "DATE", "DAY", "DBCC", "DEALLOCATE", "DEC", "DECIMAL", "DECLARE", "DEFAULT", "DEFERRABLE", "DEFERRED", "DELETE", "DENY", "DEPTH", "DEREF", "DESC", "DESCRIBE", "DESCRIPTOR", "DESTROY", "DESTRUCTOR", "DETERMINISTIC", "DIAGNOSTICS", "DICTIONARY", "DISCONNECT", "DISK", "DISTINCT", "DISTRIBUTED", "DOMAIN", "DOUBLE", "DROP", "DUMP", "DYNAMIC",
"EACH", "ELEMENT", "ELSE", "END", "END-EXEC", "EQUALS", "ERRLVL", "ESCAPE", "EVERY", "EXCEPT", "EXCEPTION", "EXEC", "EXECUTE", "EXISTS", "EXIT", "EXTERNAL", "EXTRACT",
"FALSE", "FETCH", "FILE", "FILLFACTOR", "FILTER", "FIRST", "FLOAT", "FOR", "FOREIGN", "FORTRAN", "FOUND", "FREE", "FREETEXT", "FREETEXTTABLE", "FROM", "FULL", "FULLTEXTTABLE", "FUNCTION", "FUSION",
"GENERAL", "GET", "GLOBAL", "GO", "GOTO", "GRANT", "GROUP", "GROUPING",
"HAVING", "HOLD", "HOLDLOCK", "HOST", "HOUR",
"IDENTITY", "IDENTITY_INSERT", "IDENTITYCOL", "IF", "IGNORE", "IMMEDIATE", "IN", "INCLUDE", "INDEX", "INDICATOR", "INITIALIZE", "INITIALLY", "INNER", "INOUT", "INPUT", "INSENSITIVE", "INSERT", "INT", "INTEGER", "INTERSECT", "INTERSECTION", "INTERVAL", "INTO", "IS", "ISOLATION", "ITERATE",
"JOIN",
"KEY", "KILL",
"LABEL", "LANGUAGE", "LARGE", "LAST", "LATERAL", "LEADING", "LEFT", "LESS", "LEVEL", "LIKE", "LIKE_REGEX", "LIMIT", "LINENO", "LN", "LOAD", "LOCAL", "LOCALTIME", "LOCALTIMESTAMP", "LOCATOR", "LOWER",
"MAP", "MATCH", "MAX", "MEMBER", "MERGE", "METHOD", "MIN", "MINUTE", "MOD", "MODIFIES", "MODIFY", "MODULE", "MONTH", "MULTISET",
"NAMES", "NATIONAL", "NATURAL", "NCHAR", "NCLOB", "NEW", "NEXT", "NO", "NOCHECK", "NONCLUSTERED", "NONE", "NORMALIZE", "NOT", "NULL", "NULLIF", "NUMERIC",
"OBJECT", "OCCURRENCES_REGEX", "OCTET_LENGTH", "OF", "OFF", "OFFSETS", "OLD", "ON", "ONLY", "OPEN", "OPENDATASOURCE", "OPENQUERY", "OPENROWSET", "OPENXML", "OPERATION", "OPTION", "OR", "ORDER", "ORDINALITY", "OUT", "OUTER", "OUTPUT", "OVER", "OVERLAPS", "OVERLAY",
"PAD", "PARAMETER", "PARAMETERS", "PARTIAL", "PARTITION", "PASCAL", "PATH", "PERCENT", "PERCENT_RANK", "PERCENTILE_CONT", "PERCENTILE_DISC", "PIVOT", "PLAN", "POSITION", "POSITION_REGEX", "POSTFIX", "PRECISION", "PREFIX", "PREORDER", "PREPARE", "PRESERVE", "PRIMARY", "PRINT", "PRIOR", "PRIVILEGES", "PROC", "PROCEDURE", "PUBLIC",
"RAISERROR", "RANGE", "READ", "READS", "READTEXT", "REAL", "RECONFIGURE", "RECURSIVE", "REF", "REFERENCES", "REFERENCING", "REGR_AVGX", "REGR_AVGY", "REGR_COUNT", "REGR_INTERCEPT", "REGR_R2", "REGR_SLOPE", "REGR_SXX", "REGR_SXY", "REGR_SYY", "RELATIVE", "RELEASE", "REPLICATION", "RESTORE", "RESTRICT", "RESULT", "RETURN", "RETURNS", "REVERT", "REVOKE", "RIGHT", "ROLE", "ROLLBACK", "ROLLUP", "ROUTINE", "ROW", "ROWCOUNT", "ROWGUIDCOL", "ROWS", "RULE",
"SAVE", "SAVEPOINT", "SCHEMA", "SCOPE", "SCROLL", "SEARCH", "SECOND", "SECTION", "SECURITYAUDIT", "SELECT", "SEMANTICKEYPHRASETABLE", "SEMANTICSIMILARITYDETAILSTABLE", "SEMANTICSIMILARITYTABLE", "SENSITIVE", "SEQUENCE", "SESSION", "SESSION_USER", "SET", "SETS", "SETUSER", "SHUTDOWN", "SIMILAR", "SIZE", "SMALLINT", "SOME", "SPACE", "SPECIFIC", "SPECIFICTYPE", "SQL", "SQLCA", "SQLCODE", "SQLERROR", "SQLEXCEPTION", "SQLSTATE", "SQLWARNING", "START", "STATE", "STATEMENT", "STATIC", "STATISTICS", "STDDEV_POP", "STDDEV_SAMP", "STRUCTURE", "SUBMULTISET", "SUBSTRING", "SUBSTRING_REGEX", "SUM", "SYMMETRIC", "SYSTEM", "SYSTEM_USER",
"TABLE", "TABLESAMPLE", "TEMPORARY", "TERMINATE", "TEXTSIZE", "THAN", "THEN", "TIME", "TIMESTAMP", "TIMEZONE_HOUR", "TIMEZONE_MINUTE", "TO", "TOP", "TRAILING", "TRAN", "TRANSACTION", "TRANSLATE", "TRANSLATE_REGEX", "TRANSLATION", "TREAT", "TRIGGER", "TRIM", "TRUE", "TRUNCATE", "TRY_CONVERT", "TSEQUAL",
"UESCAPE", "UNDER", "UNION", "UNIQUE", "UNKNOWN", "UNNEST", "UNPIVOT", "UPDATE", "UPDATETEXT", "UPPER", "USAGE", "USE", "USER", "USING",
"VALUE", "VALUES", "VAR_POP", "VAR_SAMP", "VARCHAR", "VARIABLE", "VARYING", "VIEW",
"WAITFOR", "WHEN", "WHENEVER", "WHERE", "WHILE", "WIDTH_BUCKET", "WINDOW", "WITH", "WITHIN", "WITHIN GROUP", "WITHOUT", "WORK", "WRITE", "WRITETEXT",
"XMLAGG", "XMLATTRIBUTES", "XMLBINARY", "XMLCAST", "XMLCOMMENT", "XMLCONCAT", "XMLDOCUMENT", "XMLELEMENT", "XMLEXISTS", "XMLFOREST", "XMLITERATE", "XMLNAMESPACES", "XMLPARSE", "XMLPI", "XMLQUERY", "XMLSERIALIZE", "XMLTABLE", "XMLTEXT", "XMLVALIDATE",
"YEAR",
"ZONE",
};
/// <inheritdoc/>
public override bool Validate(string identifier)
{
char first = identifier[0];
bool validFirstChar = char.IsLetter(first)
|| first == '_'
|| first == '@'
|| first == '#';
bool invalid = string.IsNullOrEmpty(identifier)
|| ReservedWords.Contains(identifier)
|| Encoding.Unicode.GetByteCount(identifier) > 128
|| !validFirstChar;
if (invalid)
{
return false;
}
foreach (char c in identifier)
{
if (char.IsLetterOrDigit(c))
{
continue;
}
switch (c)
{
case '@':
case '$':
case '#':
case '_':
continue;
}
return false;
}
return true;
}
}
public class OracleProvider : DbProvider
{
/// <summary>
/// Oracle reserved words as of 19c.
/// </summary>
public static readonly ISet<string> ReservedWords = new HashSet<string>(StringComparer.OrdinalIgnoreCase)
{
"ALL", "ALTER", "AND", "ANY", "AS", "ASC", "AT",
"BEGIN", "BETWEEN", "BY",
"CASE", "CHECK", "CLUSTERS", "CLUSTER", "COLAUTH", "COLUMNS", "COMPRESS", "CONNECT", "CRASH", "CREATE", "CURSOR",
"DECLARE", "DEFAULT", "DESC", "DISTINCT", "DROP",
"ELSE", "END", "EXCEPTION", "EXCLUSIVE",
"FETCH", "FOR", "FROM", "FUNCTION",
"GOTO", "GRANT", "GROUP",
"HAVING",
"IDENTIFIED", "IF", "IN", "INDEX", "INDEXES", "INSERT", "INTERSECT", "INTO", "IS",
"LIKE", "LOCK",
"MINUS", "MODE",
"NOCOMPRESS", "NOT", "NOWAIT", "NULL",
"OF", "ON", "OPTION", "OR", "ORDER", "OVERLAPS",
"PROCEDURE", "PUBLIC",
"RESOURCE", "REVOKE",
"SELECT", "SHARE", "SIZE", "SQL", "START", "SUBTYPE",
"TABAUTH", "TABLE", "THEN", "TO", "TYPE",
"UNION", "UNIQUE", "UPDATE",
"VALUES", "VIEW", "VIEWS",
"WHEN", "WHERE", "WITH",
};
/// <inheritdoc/>
public override bool Validate(string identifier)
{
bool invalid = string.IsNullOrEmpty(identifier)
|| ReservedWords.Contains(identifier)
|| Encoding.Unicode.GetByteCount(identifier) > 128
|| !char.IsLetter(identifier[0]);
if (invalid)
{
return false;
}
foreach (char c in identifier)
{
if (char.IsLetterOrDigit(c))
{
continue;
}
switch (c)
{
case '_':
case '$':
case '#':
continue;
}
return false;
}
return true;
}
}
}
BEGIN OPEN :cursor FOR 'SELECT id, name FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(:table_name) || ' WHERE name = :name' USING :name; END;with 3 bind variables: the cursor to return the result set; the table name; and the name to filter your result set. fiddle