MySQL

Quote escaping

1. Escaping Single Quotes (')

In MySQL, single quotes are used to denote string literals. If your string contains a single quote, you need to escape it by doubling it ('').

INSERT INTO users (username) VALUES ('O''Reilly');

1. Escaping Double Quotes (")

Double quotes are not typically used for string literals in MySQL (unless the ANSI_QUOTES SQL mode is enabled). If you need to include a double quote in a string, you can escape it by doubling it ("").

-- No special escaping is needed unless ANSI_QUOTES is enabled
INSERT INTO products (description) VALUES ('5" display');

1. Using Backslashes () for Special Characters MySQL recognizes certain escape sequences for special characters. For example:

\0 - ASCII NUL (0x00)

\' - Single quote

\" - Double quote

\b - Backspace

\n - Newline

\r - Carriage return

\t - Tab

\\ - Backslash

\% - Percent sign (used in LIKE clauses)

\_ - Underscore (used in LIKE clauses)

1. Using Prepared Statements (Best Practice) Instead of manually escaping quotes and special characters, use prepared statements with parameterized queries. This approach is safer and prevents SQL injection.

$stmt = $mysqli->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $username, $email);
$username = "O'Reilly";
$email = "oreilly@example.com";
$stmt->execute();

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="testdb"
)
cursor = db.cursor()
query = "INSERT INTO users (username, email) VALUES (%s, %s)"
values = ("O'Reilly", "oreilly@example.com")
cursor.execute(query, values)
db.commit()

1. Using QUOTE() Function

MySQL provides a QUOTE() function that automatically escapes a string and wraps it in single quotes.

SELECT QUOTE('O\'Reilly');