How to escape apostrophe (a single quote) in MySql?

Question

The MySQL documentation says that it should be \'. However, both scite and mysql shows that '' works. I saw that and it works. What should I do?

Scandura

Are you talking about whether '' or \' is correct? – Philip

\' is MySQL specific while '' is ANSI SQL compliant if I'm not mistaken – Liguria

Depending on the implementation of SQL;- ' '\'' , ' \'' , and sometimes '[ ']'' will allow a break out from the code. On top of this any number of unicode ' replacements will bypass this check. The whole game here is abusing the quality of '' based escaping that it requires the final ' count to be even rather than odd. If it ends up odd by mashing multiple escape methods into each other, you can defeat the escaping and inject raw SQL. Moral of the story: NEVER use string interpolation, ALWAYS use prepared statements. – Halophyte

Answer 1

Standard SQL uses doubled-up quotes; MySQL has to accept that to be reasonably compliant.

'He said, "Don''t!"'

Antheridium

+1. Where does it say that it should be escaped by '' not dev.mysql.com/doc/refman/5.0/en/… – Scandura

It says 'may' rather than 'should', but the information is there (below the table): There are several ways to include quote characters within a string: A “'” inside a string quoted with “'” may be written as “''”. A “"” inside a string quoted with “"” may be written as “""”. Precede the quote character by an escape character (“``”). – Antheridium

This is the best way to escape apostrophe by doubling it. – Roshelle

Answer 2

The MySQL documentation you cite actually says a little bit more than you mention. It also says,

A “'” inside a string quoted with “'” may be written as “''”.

(Also, you linked to the MySQL 5.0 version of Table 8.1. Special Character Escape Sequences, and the current version is 5.6 — but the current Table 8.1. Special Character Escape Sequences looks pretty similar.)

I think the Postgres note on the backslash_quote (string) parameter is informative:

This controls whether a quote mark can be represented by \' in a string literal. The preferred, SQL-standard way to represent a quote mark is by doubling it ('') but PostgreSQL has historically also accepted \'. However, use of \' creates security risks...

That says to me that using a doubled single-quote character is a better overall and long-term choice than using a backslash to escape the single-quote.

Now if you also want to add choice of language, choice of SQL database and its non-standard quirks, and choice of query framework to the equation, then you might end up with a different choice. You don't give much information about your constraints.

Pomeranian

Answer 3

What I believe user2087510 meant was:

name = 'something'
name = name.replace("'", "\\'")

I have also used this with success.

Ocampo

Worked for me while those top answers didn't – Beseech

why \\' rather than \' ? – Clearance

@biniam_Ethiopia the second \ escapes the first one – Deloris

possible security issue. can get sql injection if the string already contains \', so you insert a \, which now is in the string as \\' which will terminate the string. Use name.replace("'", "''") instead – Fusil

Answer 4

There are three ways I am aware of. The first not being the prettiest and the second being the common way in most programming languages:

1. Use another single quote: 'I mustn''t sin!'
2. Use the escape character \ before the single quote': 'I mustn\'t sin!'
3. Use double quotes to enclose string instead of single quotes: "I mustn't sin!"

Roundshouldered

My personal preference would be \' as it is used by so many programming languages, but '' is supported by more SQL dialects, so using option 1 is better for compatibility. Sqlite for example doesn't work with backslash escapes. – Northcutt

Answer 5

just write '' in place of ' i mean two times '

Outwardbound

use apostrophe two times in place of one – Outwardbound

Answer 6

Here's an example:

SELECT * FROM pubs WHERE name LIKE "%John's%"

Just use double quotes to enclose the single quote.

If you insist in using single quotes (and the need to escape the character):

SELECT * FROM pubs WHERE name LIKE '%John\'s%'

Avery

Answer 7

Possibly off-topic, but maybe you came here looking for a way to sanitise text input from an HTML form, so that when a user inputs the apostrophe character, it doesn't throw an error when you try to write the text to an SQL-based table in a DB. There are a couple of ways to do this, and you might want to read about SQL injection too. Here's an example of using prepared statements and bound parameters in PHP:

$input_str = "Here's a string with some apostrophes (')";
// sanitise it before writing to the DB (assumes PDO)
$sql = "INSERT INTO `table` (`note`) VALUES (:note)";
try {
    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(':note', $input_str, PDO::PARAM_STR);
    $stmt->execute();
} catch (PDOException $e) {
    return $dbh->errorInfo();
}
return "success";

In the special case where you may want to store your apostrophes using their HTML entity references, PHP has the htmlspecialchars() function which will convert them to '. As the comments indicate, this should not be used as a substitute for proper sanitisation, as per the example given.

Pellmell

Encoding is not for storing, it is for displaying. Use prepared statements. – Ko

I agree that prepared statements are the gold standard for security and reliability. I was highlighting a specific case where you may want to store text data as HTML, in which case my method is in addition to using bound parameters, rather than instead of. HTML in, HTML out. – Pellmell

I think your answer starts to make a good point, and then sags. In cases like this where the 'simple option' is a fatal flaw, don't recommend it, especially to newbies. – Pestilent

Answer 8

Replace the string

value = value.replace(/'/g, "\\'");

where value is your string which is going to store in your Database.

Further,

NPM package for this, you can have look into it

https://www.npmjs.com/package/mysql-apostrophe

Vannoy

Please don't just post some tool or library as an answer. At least demonstrate how it solves the problem in the answer itself. – Crescint

That's not going to work. You forgot to escape things. replace(/\'/g, "\\\'") – Laroy

Answer 9

I think if you have any data point with apostrophe you can add one apostrophe before the apostrophe

eg. 'This is John's place'

Here MYSQL assumes two sentence 'This is John' 's place'

You can put 'This is John''s place'. I think it should work that way.

Telegraph

Answer 10

Replace apostrophes with &#39 can solve your issue. It is working for me

datavalue= json.loads(json.dumps(data).replace("'","'"))

Serles

Answer 11

In PHP I like using mysqli_real_escape_string() which escapes special characters in a string for use in an SQL statement.

see https://www.php.net/manual/en/mysqli.real-escape-string.php

Incontrovertible

Never do this. Always use prepared statements / bound parameters, your SQL should NEVER contain a raw parameter from the language (PHP / Java / C# / Anything) – Pestilent