MySQL Tutorial
MySQL Installation and Configuration
MySQL Database Operations
Database Design
MySQL Data Types
MySQL Storage Engines
MySQL Basic Operations of Tables
MySQL Constraints
MySQL Operators
MySQL Function
MySQL Manipulate Table Data
MySQL View
MySQL Indexes
MySQL Stored Procedure
MySQL Trigger
MySQL Transactions
MySQL Character Set
MySQL User Management
MySQL Database Backup and Recovery
MySQL Log
MySQL Performance Optimization
In MySQL, certain characters have special meanings, such as the single quote ('), double quote ("), backslash (\), and others. These characters need to be "escaped" when they're included in a string, to prevent errors or SQL injection attacks. Escaping is done by prefixing the character with a backslash (\).
Here are some examples of MySQL escape characters:
When you're writing a SQL query in MySQL, you can use these escape characters to include special characters in your strings. Here is an example:
INSERT INTO example (column) VALUES ('This isn\\'t a problem.');
This will insert the string This isn't a problem. into the column in the example table.
If you're writing SQL queries directly, you'll have to remember to escape these characters. However, if you're using a programming language like PHP, Python, or Java to interact with your MySQL database, these languages often have functions that will automatically escape special characters for you. For example, in PHP, you might use the mysqli_real_escape_string() function to escape special characters.
Alternatively, you can use "Prepared Statements" which is a much safer way of executing SQL statements that contain user input. This feature automatically escapes special characters and helps prevent SQL injection attacks.
Here's an example using PHP's PDO extension:
$stmt = $pdo->prepare("INSERT INTO example (column) VALUES (:value)");
$stmt->execute(['value' => "This isn't a problem."]);
In this case, you don't need to escape the special characters yourself; the PDO extension takes care of it for you.
SELECT * FROM employees WHERE last_name = 'O\'Reilly';
SELECT * FROM products WHERE product_name LIKE 'C\%';
INSERT INTO messages (content) VALUES ('This is a single quote: \' and a backslash: \\');
SELECT * FROM customers WHERE customer_name = 'John''s Shop';
INSERT INTO escape_example (text_column) VALUES ('This is a newline.\nAnd this is a tab.\t');
SELECT * FROM products WHERE product_name LIKE '%50\% off%';
-- Using escape characters SELECT * FROM users WHERE username = 'admin\' OR \'1\'=\'1'; -- Using parameterized queries PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?'; SET @param = 'admin\' OR \'1\'=\'1'; EXECUTE stmt USING @param;
SELECT * FROM messages WHERE content LIKE '%\_%' ESCAPE '\';
\0 An ASCII NUL (0x00) character. \' Single quote. \" Double quote. \b Backspace. \n Newline (linefeed). \r Carriage return. \t Tab. \\ Backslash. \% Percent sign (used in LIKE patterns). \_ Underscore (used in LIKE patterns).
INSERT INTO example_table (text_column) VALUES ("This is a double quote: \"" );
SELECT * FROM patterns WHERE pattern LIKE '50\% off' ESCAPE '\';
INSERT INTO text_data (content) VALUES ('Special characters: !@#$%^&*()');
SELECT * FROM percentages WHERE value LIKE '50\%';
INSERT INTO quotes_example (quote) VALUES ('She said, ''Hello!''');
SELECT * FROM `select` WHERE `where` = 'conditional';
SELECT `column with space` FROM `table with spaces` WHERE `identifier` = 'value';
CREATE PROCEDURE escape_example_procedure()
BEGIN
SELECT * FROM users WHERE username = 'admin\' OR \'1\'=\'1';
END;