Characters That Need Escaping

Let's connect to Manticore and explore special characters.

mysql -P9306 -h0

Create a test table:

CREATE TABLE docs(title text, content text);

Insert documents with special characters:

INSERT INTO docs(title, content) VALUES
('C++ Programming', 'Learn C++ programming basics'),
('Email: test@example.com', 'Contact us at test@example.com'),
('Price $99.99', 'Special offer for $99.99'),
('50% Off Sale!', 'Get 50% off today!'),
('File: config.txt', 'Edit the config.txt file'),
('Path /usr/local/bin', 'Binary files in /usr/local/bin');

Characters that function as operators must be escaped:

! " $ ' ( ) - / < @ \ ^ | ~

Without escaping, these characters trigger operator behavior or cause errors.

Try searching without escaping (may fail or give unexpected results):

SELECT * FROM docs WHERE MATCH('C++');

The ++ has special meaning. Let's learn to escape it properly.

Escaping in MySQL Client

In MySQL command line, escaping rules are:

1. Single quotes: one backslash \'
2. Operator characters: double backslash \\
3. Backslash itself: four backslashes \\\\

Search for @ symbol (email):

SELECT * FROM docs WHERE MATCH('test\\@example');

Search for $ symbol (price):

SELECT * FROM docs WHERE MATCH('\\$99');

Search for exclamation mark:

SELECT * FROM docs WHERE MATCH('50\\% Off Sale\\!');

Search for forward slash:

SELECT * FROM docs WHERE MATCH('\\/usr');

Multiple special characters in one query:

SELECT * FROM docs WHERE MATCH('test\\@example\\.com');

The dot (.) doesn't need escaping but is shown for completeness.

When searching for text with quotes:

SELECT * FROM docs WHERE MATCH('config\\.txt');

Remember: MySQL client requires extra escaping because it processes backslashes before sending to server.

Escaping Parentheses and Operators

Parentheses are used for grouping in queries. To search them literally, escape them.

Insert more test data:

INSERT INTO docs(title, content) VALUES
('Function call()', 'The function call() returns a value'),
('Array [index]', 'Access array elements with [index]'),
('Regex pattern (a|b)', 'Match pattern (a|b) in text'),
('Range 10-20', 'Values in range 10-20');

Search for parentheses:

SELECT * FROM docs WHERE MATCH('call\\(\\)');

Search for pipe character (OR operator):

SELECT * FROM docs WHERE MATCH('\\(a\\|b\\)');

Search for dash (NOT operator context):

SELECT * FROM docs WHERE MATCH('10\\-20');

The dash only needs escaping when it could be interpreted as negation.

Caret (field start operator):

SELECT * FROM docs WHERE MATCH('\\^pattern');

Tilde (proximity operator):

SELECT * FROM docs WHERE MATCH('10\\~20');

When in doubt, escape special characters to ensure literal matching.

Escaping in Different Contexts

Different clients and APIs have different escaping requirements.

For MySQL drivers with prepared statements, use single backslash:

# Pseudocode - in application code:
query = "SELECT * FROM docs WHERE MATCH('test\\@example')"
cursor.execute(query)

For HTTP JSON API, double quotes need single escaping:

{
  "index": "docs",
  "query": {
    "match": {
      "content": "test\\@example"
    }
  }
}

Escaping backslashes themselves:

INSERT INTO docs(title, content) VALUES('Path C:\\\\Users', 'Windows path C:\\\\Users\\\\Admin');

SELECT * FROM docs WHERE MATCH('C\\\\\\\\Users');

For JSON properties with special characters, use backticks:

CREATE TABLE jsondata(data json);

INSERT INTO jsondata(data) VALUES('{"a=b": 123, "x/y": 456}');

Query JSON with special property names:

SELECT * FROM jsondata WHERE data.`a=b` = 123;

Backticks escape the entire property name including special characters.

Wildcards and Best Practices

Asterisks (*) have special wildcard behavior that cannot be escaped at word boundaries.

SELECT * FROM docs WHERE MATCH('config*');

This searches for words starting with "config" - the * acts as wildcard.

To search for literal asterisk in middle of text:

INSERT INTO docs(title, content) VALUES('Rating: 5*', 'This product has 5* rating');

SELECT * FROM docs WHERE MATCH('5\\*');

Best practices for escaping:

1. Identify special characters in user input
2. Apply appropriate escaping based on your client
3. Test queries with edge cases
4. Consider using prepared statements

Helper function concept (pseudocode):

def escape_query(text):
    special = ['!', '"', '$', "'", '(', ')', '-', '/', '<', '@', '\\', '^', '|', '~']
    for char in special:
        text = text.replace(char, '\\' + char)
    return text

Summary of escaping rules:

• MySQL CLI: double backslash for operators
• MySQL drivers: single backslash with prepared statements
• HTTP JSON: automatic escaping by JSON libraries
• JSON properties: backticks for special names