Quotes in MySQL: Single, Double, and Backticks – A Complete Breakdown

Introduction: Why Do Quotes in MySQL Drive You Crazy?

If you've ever written SQL queries in PHP, you've probably faced confusion: when to use single quotes (' '), when to use double quotes (" "), and when to use those mysterious backticks (` `)? One wrong character — and instead of data, you get a MySQL syntax error or, even worse, a security hole.

This topic is one of the most popular questions on Stack Overflow (over 2 million views!). And for good reason. Even experienced developers sometimes get confused. Today, we will settle this once and for all. You will learn:

\n
• What each type of quote in MySQL is responsible for
\n
• How to properly escape data in PHP
\n
• Why double quotes are evil (almost always)
\n
• How to protect against SQL injections
\n

Let's go! 🚀

Anatomy of a MySQL Query: Three Types of Quotes

In MySQL, there are three different characters that look like quotes but perform completely different tasks:

1. Single Quotes (' ') — For String Values

When to use: always, when you are passing a string (text, date, email, password).

SELECT * FROM users WHERE name = 'Ivan';\nINSERT INTO articles (title) VALUES ('MySQL for Dummies');

MySQL treats everything inside single quotes as a string literal. This means you are telling the database: "Here is a value, do not try to interpret it as a column name or command."

2. Double Quotes (" ") — Almost the Same, But with a Nuance

In standard SQL, double quotes are used for identifiers (table and column names). However, MySQL by default operates in a mode where double quotes are equivalent to single quotes. This often causes confusion.

-- This will work, but it's better not to do it\nSELECT * FROM users WHERE name = "Maria";

The Problem: if you enable the strict ANSI_QUOTES mode, double quotes will start working like backticks. Your code will break. Therefore:

Rule #1: Use single quotes for strings. Always. Double quotes — only if you know exactly what you are doing, or for compatibility with other DBMS.

3. Backticks (` `) — For Table and Column Names

Backticks are identifiers. They are needed to:

\n
• Use reserved words as names (bad practice, but it happens)
\n
• Work with names containing spaces or special characters
\n
• Avoid conflicts with MySQL keywords
\n

-- Bad: 'select' is a reserved word\nSELECT * FROM select;\n\n-- Good: escape with backticks\nSELECT * FROM `select`;\n\n-- Mandatory: name with a space\nSELECT `user name` FROM users;\n\n-- Recommended: always escape table and column names\nSELECT `id`, `name` FROM `users` WHERE `status` = 'active';

Important: Backticks CANNOT be used for values. This will cause an error.

-- ERROR: MySQL will think 'Ivan' is a column name\nSELECT * FROM users WHERE name = `Ivan`;

PHP + MySQL: How Not to Get Confused with Quotes

Now for the interesting part — how this all works in PHP. PHP itself uses single and double quotes for strings, and it's easy to make a mistake here.

Case #1: Direct Variable Insertion (Dangerous!)

// BAD: vulnerable to SQL injections\n$name = "Ivan'; DROP TABLE users; --";\n$query = "SELECT * FROM users WHERE name = '$name'";\n// Result: SELECT * FROM users WHERE name = 'Ivan'; DROP TABLE users; --'

This is a classic SQL injection. Never do this!

Case #2: Escaping via mysqli_real_escape_string (Better, but Not Ideal)