[mysql] How to escape apostrophe (') in MySql?

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?

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%'

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!"

What I believe user2087510 meant was:

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

I have also used this with success.

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.

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

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

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

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

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

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.