How to escape single quotes in MySQL

Put quite simply:

SELECT 'This is Ashok''s Pen.';

So inside the string, replace each single quote with two of them.

Or:

SELECT 'This is Ashok\'s Pen.'

Escape it =)

' is the escape character. So your string should be:

This is Ashok''s Pen

If you are using some front-end code, you need to do a string replace before sending the data to the stored procedure.

For example, in C# you can do

value = value.Replace("'", "''");

and then pass value to the stored procedure.

See my answer to "How to escape characters in MySQL"

Whatever library you are using to talk to MySQL will have an escaping function built in, e.g. in PHP you could use mysqli_real_escape_string or PDO::quote

You should escape the special characters using the \ character.

This is Ashok's Pen.

Becomes:

This is Ashok\'s Pen.

In PHP, use mysqli_real_escape_string.

Example from the PHP Manual:

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

mysqli_query($link, "CREATE TEMPORARY TABLE myCity LIKE City");

$city = "'s Hertogenbosch";

/* this query will fail, cause we didn't escape $city */
if (!mysqli_query($link, "INSERT into myCity (Name) VALUES ('$city')")) {
    printf("Error: %s\n", mysqli_sqlstate($link));
}

$city = mysqli_real_escape_string($link, $city);

/* this query with escaped $city will work */
if (mysqli_query($link, "INSERT into myCity (Name) VALUES ('$city')")) {
    printf("%d Row inserted.\n", mysqli_affected_rows($link));
}

mysqli_close($link);
?>

If you are using PHP, just use the addslashes() function.

PHP Manual addslashes

This is how my data as API response looks like, which I want to store in the MYSQL database. It contains Quotes, HTML Code , etc.

Example:-

{

rewardName: "Cabela's eGiftCard $25.00",

shortDescription: '<p>adidas gift cards can be redeemed in over 150 adidas Sport Performance, adidas Originals, or adidas Outlet stores in the US, as well as online at&nbsp;<a href="http://adidas.com/">adidas.com</a>.</p>

terms: '<p>adidas Gift Cards may be redeemed for merchandise on&nbsp;<a href="http://adidas.com/">adidas.com</a>&nbsp;and in adidas Sport Performance, adidas Originals, and adidas Outlet stores in the United States.'

}

SOLUTION

CREATE TABLE `brand` (
`reward_name` varchar(2048),
`short_description` varchar(2048),
`terms` varchar(2048),  
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

While inserting , In followed JSON.stringify()

    let brandDetails= {
    rewardName: JSON.stringify(obj.rewardName),  
    shortDescription: JSON.stringify(obj.shortDescription),
    term: JSON.stringify(obj.term),
     }

Above is the JSON object and below is the SQL Query that insert data into MySQL.

let query = `INSERT INTO brand (reward_name, short_description, terms) 
VALUES (${brandDetails.rewardName}, 
(${brandDetails.shortDescription}, ${brandDetails.terms})`;

Its worked....

If nothing works try this :

var res = str.replace(/'/g, "\\'");
var res = res.replace(/"/g, "\\\"");

It adds the \ escape character to all(every) occurrences of ' and "

Not sure if its the correct/professional way to fix the issue

I'm guessing it will work but in actual content, every single and double quotes will be replaced with \ character

As a Python user I replace the quote with a raw "'":

don_not_work_str = "This is Ashok's Pen."
work_str = don_not_work_str.replace("'", r"\'")

For Python，I tried many ways to escape '"', not work, cuz I have various input.

Finally, I just use these code, the data in database was same like the input.

tmp = val.replace('\\', r'\\')
ret = tmp.replace('"', r'\"')