4

In one of my forms I use the rich text editor from Yahoo!. Now i want to store the data from that textarea in a MySQL database.

The user can enter anything in that textarea, e.g. many double or single quotes.

How can I store that data?

Normally we store by adding that data in one variable and then put that in sql, but the quotes cause problems.

Quentin
  • 914,110
  • 126
  • 1,211
  • 1,335
Nitz
  • 1,690
  • 11
  • 36
  • 56

6 Answers6

6

You use a PDO prepared statement (or mysql_real_escape_string)

Community
  • 1
  • 1
Quentin
  • 914,110
  • 126
  • 1,211
  • 1,335
1

You can use mysql_real_escape_string().

Escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query(). If binary data is to be inserted, this function must be used.

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.

e.g.

$value = mysql_real_escape_string(" ' \" etc ");
$sql = "INSERT INTO blah VALUES ('$value')";

But a better solution is to use PDO and prepared statements.

Tom Haigh
  • 57,217
  • 21
  • 114
  • 142
  • ..."mysql_real_escape_string" is a MySQL function, not PHP. Your example of: $value = mysql_real_escape_string(" ' \" etc "); would return PHP errors both for the unknown function, as well as the open-ended string. – ashleedawg Oct 06 '17 at 08:45
  • @ashleedawg not sure I understand. I linked to the PHP documentation for the function. If it doesn't exist then the mysql module probably isn't loaded. – Tom Haigh Oct 06 '17 at 11:50
0

If PDO isnt an option you might be able to use mysqli instead of course with a prepared statement.

Kristoffer Sall-Storgaard
  • 10,576
  • 5
  • 36
  • 46
0

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....

enter image description here

Ajay
  • 176
  • 6
-4

Better yet! When submitting the content to the database, use addslashes();

When retrieving and displaying the string use stripslashes();

$string = "That's awesome!";

addslashes($string); will come out as That\'s Awesome in the database (and won't break anything)

Then stripslashes($string); will return it to normal.

http://php.net/manual/en/function.addslashes.php

I use this all the time - simple and straight-forward.

Tim
  • 6,986
  • 8
  • 38
  • 57
  • from http://php.net/manual/en/function.addslashes.php: " It's highly recommeneded to use DBMS specific escape function (e.g. mysqli_real_escape_string() for MySQL or pg_escape_string() for PostgreSQL) " – Tom Haigh Apr 27 '10 at 12:07
-4

Thanks you guys,
for your replay.
But i had only replace the quotes characters...by this.. 

html = html.replace(/\'/g, "&#39;"); // 39 is ascii of single quotes
html = html.replace(/\"/g, "&#34;"); // 39 is ascii of double quotes

and then stored in the database.
its working great..by this way... and when i want that data then i just replace to its orginal.

But thanks for your replay..

Nitish.
Panchjanya Corporation
Nitz
  • 1,690
  • 11
  • 36
  • 56
  • This is JavaScript. You cannot do escaping like that on the client as this will leave your application open for sql injection if someone just disabled that script! – ThiefMaster May 19 '10 at 11:57