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: '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 adidas.com.
terms: 'adidas Gift Cards may be redeemed for merchandise on adidas.com 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
Using Backticks, Double Quotes, and Single Quotes when querying a MySQL database can be boiled down to two basic points. Using double quotes here is some input and output examples:Double Quotes
SELECT "test", "'test'", "''test''", "te""st";
The output looks like this:
Wrapping single quotes inside of double quotes will cancel out the expected behavior of the single quotes in the MySQL Query and instead treat it as part of the string. This can be seen in columns 2 and 3 in the example above.
Inserting two double quotes in the middle of the string will cancel out one of them.
Single Quotes
Using single quotes here is some input and output examples:
SELECT 'test', '"test"', '""test""', 'te''st';
The output looks like this:
As shown in the demonstration above, single quotes behave the same way as double quotes in these contexts.
Using Single Quotes and Double Quotes Together
Often times there will be a contraction in a string, or a direct quote. In situations like in NPS survey reports or other customer feedback forms this is often the case. In these cases using double quotes to wrap a text string that contains a contraction like They’ve will keep the single quote in the string as an apostrophe.
In this case presenting a string with a contraction should look like this:
SELECT "They've found this tutorial to be helpful"
The output looks like this:
Or, if you need to use double quotes to present a customer feedback quote in the string, you can use single quotes to wrap the whole string.
SELECT 'They responded, "We found this tutorial helpful"'
If you need to use single quotes and double quotes in a string that contains both a contraction and a quote, you will need to use the backslash ‘' to cancel out the following character. For example: a string containing this ' will recognize the backslash as an instruction to cancel out the single quote’s syntactical meaning and instead insert it into the string as an apostrophe.
SELECT 'They\'ve responded, "We found this tutorial helpful"'
Backticks
Backticks are used in MySQL to select columns and tables from your MySQL source. In the example below we are
calling to the table titled Album
and the column Title
. Using backticks we are signifying that those are the column and table names.
SELECT `Album`.`Title`
FROM `Album` AS `Album`
GROUP BY `Album`.`Title`
ORDER BY `Title` ASC
LIMIT 10;
The backticks for column names may not be necessary though.
SELECT Album.Title
FROM Album AS Album
GROUP BY Album.Title
ORDER BY Title ASC
LIMIT 10;
Both of these queries will return the same result.
Putting it all together
The following query will use all we’ve learned here, including double quotes, single quotes, and backticks.
SELECT 'They\'ve responded, "We found this tutorial helpful"' as `Response`
Will return: