EnquoteIdentifier(identifier, maxIdentifierLength, charset, bAlwaysQuote, bCapitalize)

This method validates the identifier, adds double quotes if missing, and returns the result.

Declaration

// C#
public static string EnquoteIdentifier(string identifier, int maxIdentifierLength,
        OracleDatabaseCharset charset, bool bAlwaysQuote = true, bool bCapitalize =
        true);

Parameters

  • identifier

    The input identifier.

  • maxIdentifierLength

    The maximum allowed identifier length in bytes.

  • charset

    The database character set.

  • bAlwaysQuote

    If true, then the returned identifier is always quoted. If false, then quotes will only be added for a simple identifier that is not already quoted if the case needs to be preserved.

  • bCapitalize

    If true, a simple identifier will be uppercased except when it is already double quoted. If false, it is case-sensitive, and case will be preserved with double quotes, if it contains a lowercase letter.

Return Value

The identifier is returned with double quotes, except for a simple identifier that is not already quoted in either of the following cases:

  • bAlwaysQuote is false and bCapitalize is true.

  • bAlwaysQuote is false, bCapitalize is false, and the identifier is uppercase.

Exceptions

ArgumentNullException - Value cannot be null. (Parameter 'identifier')

ArgumentException - Value does not fall within the expected range. (Parameter 'maxIdentifierLength')

OracleException - Invalid identifier or literal.

Remarks

This method's purpose is to prevent SQL injection.

Simple SQL names are supported as identifiers. In other words, qualified SQL names are not supported. For example, SCHEMA.TABLE would be a qualified SQL name.

A simple identifier that is not already quoted will only get quoted if bAlwaysQuote is true, or bCapitalize is false and it contains a lowercase letter. It will be uppercased if bCapitalize is true.

A quoted identifier is returned as is, but the identifier being quoted cannot contain double quotes or the null character.

Other identifiers can contain any characters, except double quotes and the null character. The identifier will be enclosed in double quotes.

Empty identifiers are not allowed.

Leading and trailing white space is not ignored nor trimmed when quoted.

Reserved words will always be quoted.

maxIdentifierLength and charset are used for identifier validation. An identifier length must be a minimum of one byte and maximum of maxIdentifierLength bytes, when represented in the database character set. The double quotes in a quoted identifier are not counted for the identifier length.

ODP.NET uses charset to verify the specified database character set that can represent the identifier. Sometimes alternative representations of quotes can be used to introduce SQL injection. An OracleException will be thrown if the quotes cannot be correctly represented in the database character set.

This table shows EnquoteIdentifier method output for various input values and settings.

Input Output bCapitalize=true bAlwaysQuote=false Output bCapitalize=false bAlwaysQuote=false Notes 
TABLE123$
 
TABLE123$
 
TABLE123$

No quotes nor uppercasing needed.

 
table123$
 
TABLE123$
 
"table123$"

For bCapitalize=true, uppercasing is needed, but not quotes.

For bCapitalize=false, the identifier needs to be treated as lowercase and quoted. 

"table123$"
 
"table123$"
 
"table123$"

Already quoted and remains quoted.

 
table123?
 
"table123?"
 
"table123?"

? is not allowed in simple identifiers. No uppercasing. Quotes required.

 
select
 
"SELECT"
 
"select"

Reserved words are quoted. For bCapitalize=true, uppercasing is also needed. 

   table123$   
"   table123$   "
 
"   table123$   "

Leading and trailing white space is not ignored nor trimmed. Quotes are required. It will not be uppercased because it is not a simple identifier.

This ODP.NET code sample shows how to use OracleDBMSAssert.EnquoteIdentifier to protect the query from SQL injection compromise.

Example

// C#
using System;
using System.Data;
using Oracle.ManagedDataAccess.Client;

class EnquoteIdentifierSample
{
  static void Main()
  {
    string constr = "User Id=HR;Password=<PASSWORD>;Data Source=<DATA SOURCE>";
    OracleConnection con = new OracleConnection(constr);
    con.Open();

    string tableName "employees";
    string cmdText = "SELECT count(*) FROM " + OracleDBMSAssert.EnquoteIdentifier(tableName, 128, OracleDatabaseCharset.AL32UTF8, false);
    // Resulting Command Text: "SELECT count(*) FROM EMPLOYEES"

    OracleCommand cmd = new OracleCommand(cmdText);
    object count = cmd.ExecuteScalar();
    Console.WriteLine("There are " + count + " rows in the table.");

    cmd.Dispose();
    con.Dispose();
  }
}

Note: