Oracle ORA-01424 Error: Causes and Solutions Complete Guide

ORA-01424: Missing or Illegal Character Following the Escape Character

ORA-01424 occurs in Oracle when you use the ESCAPE clause with a LIKE predicate and the character immediately following the designated escape character is either missing or not one of the allowed characters (%, _, or the escape character itself). This error is especially common in applications that build SQL dynamically from user input without properly sanitizing special characters. Understanding and handling this error correctly is essential for writing robust, secure Oracle SQL.

---

Top 3 Causes

1. Pattern String Ends with the Escape Character

When the search pattern terminates with the escape character and there is no following character to escape, Oracle raises ORA-01424.

-- This causes ORA-01424 (pattern ends with \)
SELECT *
FROM   products
WHERE  product_name LIKE '50\' ESCAPE '\';

-- Fix: double the trailing escape character
SELECT *
FROM   products
WHERE  product_name LIKE '50\\' ESCAPE '\';

2. Escape Character Followed by an Illegal Character

Only %, _, or the escape character itself may follow the designated escape character. Any other character is considered illegal.

-- ORA-01424: \B is not a valid escape sequence
SELECT *
FROM   employees
WHERE  last_name LIKE 'O\Brien' ESCAPE '\';

-- Fix: use a different escape character that avoids the conflict,
-- or avoid ESCAPE altogether when not needed
SELECT *
FROM   employees
WHERE  last_name LIKE 'O''Brien';  -- no escape needed here

-- Correct usage: only %, _, or \ may follow the escape char
SELECT *
FROM   sales
WHERE  code LIKE '10\%OFF'  ESCAPE '\'   -- literal %
UNION ALL
SELECT *
FROM   sales
WHERE  ref  LIKE 'col\_1'   ESCAPE '\'   -- literal _
UNION ALL
SELECT *
FROM   sales
WHERE  path LIKE 'C:\\data' ESCAPE '\';  -- literal \

3. Unsanitized User Input in Dynamic SQL

Concatenating raw user input directly into a LIKE pattern without escaping special characters is the most dangerous cause — both for ORA-01424 and for SQL injection vulnerabilities.

-- DANGEROUS: user types "50\" and this query blows up
-- v_input := '50\';
-- v_sql   := 'SELECT * FROM products WHERE name LIKE ''' || v_input || '%'' ESCAPE ''\''';

-- Safe approach: sanitize input with a helper function
CREATE OR REPLACE FUNCTION escape_like(
    p_input  IN VARCHAR2,
    p_esc    IN VARCHAR2 DEFAULT '\'
) RETURN VARCHAR2 IS
BEGIN
    IF p_input IS NULL THEN RETURN NULL; END IF;
    -- Order matters: escape the escape char first
    RETURN REPLACE(
               REPLACE(
                   REPLACE(p_input, p_esc, p_esc||p_esc),
               '%', p_esc||'%'),
           '_', p_esc||'_');
END escape_like;
/

-- Safe dynamic query using bind variables
DECLARE
    v_input   VARCHAR2(100) := '50\';
    v_pattern VARCHAR2(200);
    v_count   NUMBER;
BEGIN
    v_pattern := escape_like(v_input) || '%';

    -- Always use bind variables with EXECUTE IMMEDIATE
    EXECUTE IMMEDIATE
        'SELECT COUNT(*) FROM products WHERE product_name LIKE :1 ESCAPE :2'
    INTO v_count
    USING v_pattern, '\';

    DBMS_OUTPUT.PUT_LINE('Rows found: ' || v_count);
END;
/

---

Quick Fix Checklist

Scenario	Fix
Pattern ends with escape char	Double the escape character (\ → \\)
Illegal char after escape	Remove unnecessary ESCAPE clause or pick a different escape char
Raw user input in LIKE	Use escape_like() helper + bind variables
Unknown origin	Add REPLACE chain before the LIKE pattern

---

Prevention Tips

1. Centralize escape logic in a reusable function.
Create a shared utility function like escape_like shown above and enforce its use across all LIKE searches through code review. Never let raw user input touch a LIKE pattern directly.

2. Always use bind variables in dynamic SQL.
Bind variables prevent ORA-01424, block SQL injection, and improve performance through cursor sharing. Replace string concatenation with EXECUTE IMMEDIATE ... USING or DBMS_SQL bind calls as a non-negotiable coding standard.

---

Related Oracle Errors

• ORA-01425 — Escape character must be a single character; raised when the ESCAPE clause receives a multi-character string.
• ORA-00907 — Missing right parenthesis; can accompany malformed LIKE clauses.
• ORA-01722 — Invalid number; may cascade from improper dynamic SQL construction related to escape handling.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.