EnquoteLiteral(literal, charset)

This method validates the literal, adds single quotes if required, and return the result.

Declaration

// C#
public static string EnquoteLiteral(string literal, OracleDatabaseCharset charset);

Parameters

  • literal

    The input string literal.

  • charset

    The database character set.

Return Value

The literal is returned enclosed with single quotes.

Exceptions

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

OracleException - Invalid identifier or literal.

Remarks

This method's purpose is to prevent SQL injection.

Any single quote in the literal is replaced with two single quotes for the purposes of escaping, or overloading. For example, the input string, scott's, becomes 'scott''s' in the return value.

Empty literals are allowed with ODP.NET adding quotes with this method.

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

ODP.NET uses charset to verify the specified database character set that can represent the literal. 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 EnquoteLiteral method output for various input values.

Input Output Notes 
scott
 
'scott'

Add surrounding single quotes.

 
scott's name
 
'scott''s name'

Add surrounding single quotes. The existing single quote is replaced with two single quotes.

 
    scott    
'    scott    '

Add surrounding single quotes. Leading and trailing white space is not ignored nor trimmed.

Example

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

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

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

    string empName "Walsh";
    string cmdText = "SELECT FIRST_NAME FROM EMPLOYEES WHERE LAST_NAME = " + 
OracleDBMSAssert.EnquoteLiteral(empName, OracleDatabaseCharset.AL32UTF8);
    // Resulting Command Text: "SELECT FIRST_NAME FROM EMPLOYEES WHERE LAST_NAME = 'Walsh'"

    OracleCommand cmd = new OracleCommand(cmdText);
    DataReader reader = cmd.ExecuteReader();
    if(reader.Read())
    {
      Console.WriteLine("The first name for " + empName + " is " + reader.GetString(0));
    }

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

Note: