Strings, Quotes and Escaping

A string literal is a piece of text written inside your SQL, such as 'Pune'.

Single quotes

Standard SQL uses single quotes for text:

1

SELECT 'Hello' AS greeting;

1
2
3
4
5
6

+----------+
| greeting |
+----------+
| Hello    |
+----------+
1 row in set

MySQL also accepts double quotes for text by default, but that is not portable and breaks if ANSI_QUOTES is on. Use single quotes.

An apostrophe inside text

Two ways to put a single quote inside a single-quoted string:

1

SELECT 'It''s fine' AS doubled_quote, 'It\'s fine' AS backslash_quote;

1
2
3
4
5
6

+---------------+-----------------+
| doubled_quote | backslash_quote |
+---------------+-----------------+
| It's fine     | It's fine       |
+---------------+-----------------+
1 row in set

Doubling the quote ('') is the standard way. The backslash form is MySQL-specific.

Escape sequences

Inside a string, a backslash starts an escape sequence (a special code):

1

SELECT 'line one\nline two' AS two_lines, 'C:\\temp' AS path;

1
2
3
4
5
6
7

+-------------------+---------+
| two_lines         | path    |
+-------------------+---------+
| line one
line two | C:\temp |
+-------------------+---------+
1 row in set

The \n made a real new line and \\ became a single backslash.

Joining adjacent strings

Two literals next to each other are joined:

1

SELECT 'Hello, ' 'world' AS joined;

1
2
3
4
5
6

+--------------+
| joined       |
+--------------+
| Hello, world |
+--------------+
1 row in set

That is occasionally useful in long text, and is a classic way to lose a comma by accident, so be careful.

Hexadecimal and binary literals

1

SELECT 0x4D7953514C AS hex_value, CAST(0x4D7953514C AS CHAR) AS as_text, b'1010' + 0 AS binary_value;

1
2
3
4
5
6

+-----------+---------+--------------+
| hex_value | as_text | binary_value |
+-----------+---------+--------------+
| MySQL     | MySQL   |           10 |
+-----------+---------+--------------+
1 row in set

Escaping is not a security tool

Putting a user’s text into an SQL string by hand, even with escaping, is how SQL injection happens. The correct protection is prepared statements, where the SQL and the data travel separately (Prepared Statements and SQL Injection). Learn quoting so you can read SQL, and use parameters when your code has user input.