PHP how to save HTML string into database [duplicate]

Question

In response to an API call, I'm getting a full HTML script. Full means it includes HTML CSS and Javascript. Now I have that HTML as string in PHP variable.

$content = '<html>
<head>
  <script>--Some javascript and libraries included--</script>
  <title></title>
</head>
<body>
   <style>--Some Styling--</style>
</body>
</html>';

Now, what is the best way to save this variable in Database and How?

As a string with VARCHAR or TEXT type?
As a string with Base64 Encoded with VARCHAR or TEXT type?
As a Binary with BLOB type?

Or any other you would like to suggest(May be Serialize or Pack)?

In wp they used addslashes(deslash($content)); . deslash function written by wp core itself to make it generalize and store it on WordPress database. It helps to work on. — Kvvaradha
– Kvvaradha, Commented Oct 17, 2016 at 9:28
I hope your users can not access these html data else your leaving your self open for xss attack's — Bobby Axe
– Bobby Axe, Commented Sep 20, 2018 at 15:50

saiid · Accepted Answer · 2021-01-20 08:21:13Z

15

I use base64 encoded data to store in my Database with the BLOB datatype. The boilerplate code is as follow.

$content = '<html>
<head>
  <script>--Some javascript and libraries included--</script>
  <title></title>
</head>
<body>
   <style>--Some Styling--</style>
</body>
</html>';

To encode data in base64

$encodedContent = base64_encode($content); // This will Encode

And save the data in database with BLOB. Now after retrieve data, just decode it as follow.

$ContentDecoded = base64_decode($content);  // decode the base64

Now the value of $contentDecoded is the plain HTML.

edited Jan 20, 2021 at 8:21

answered May 20, 2016 at 10:47

saiid

6131 gold badge6 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

saiid Over a year ago

I'd also like to know about the downvotes. Why people downvote without pointing out the mistake?

IceFire Over a year ago

@e4c5 said it: you increase the storage size in this way

e4c5 · Accepted Answer · 2016-05-20 05:57:30Z

6

If you base64 encode it you increase the storage size by rougly 30% and you need to decode it each time you display it. Look at the table structure for Wordpress, the most widely used software that stores html on a mysql database using php. What do they use? LONGTEXT. In your case TEXT is probably better because you probably have a good idea about the size of the page.

answered May 20, 2016 at 5:57

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

4 Comments

saiid Over a year ago

Thanks @e4c5 I'll have a look at this. The link you suggest is more sort of generalized form. But yes I'd have a look how WP deal with this.

e4c5 Over a year ago

No that was just an example, no need to dig deeper. Just use TEXT but don't bother with base64 encode

stan0 Over a year ago

@e4c5 what if the HTML is out of our control? For example if I fetch a random site from the internet, should I somehow encode it before storing it? Would this help to prevent a potential sql-injection?

e4c5 Over a year ago

You should be using prepared statements to avoid sql injection. You should never allow HTML that is outside your control to be saved in the database in the first place, that will leave you wide open to XSS attacks

Gorakh Yadav · Accepted Answer · 2016-05-20 09:17:31Z

3

Store HTML into a variable using addslashes() function.

$html = addslashes('<div id="intro">
<div id="about" align="left">
<h2 class="bigHeader" dir="rtl"HEADER</h2>
<img src="img/Med-logo.png" alt="" />
<div id="wellcomePage" class="text-left text" dir="rtl">
<p>...some words....</p>
<p>.some words....</p>
<p>&nbsp;</p>
</div>
</div>
</div>');

After this, form an SQL query.

$sql = "UPDATE `Pages` SET `content`= '".$html."'";

and you have to add stripslashes when retrieve from DB

edited May 20, 2016 at 9:17

answered May 20, 2016 at 6:28

Gorakh Yadav

3025 silver badges19 bronze badges

Comments

Manjeet Barnala · Accepted Answer · 2016-05-20 06:10:54Z

1

I would recommend you to use TEXT. Blobs are typically used to store images, audio or other multimedia objects. read more about bolobs

Data type to store HTML in Database would be TEXT.

Use mysql_real_escape_string() to store html text in database

$content = '<html>
<head>
  <script>--Some javascript and libraries included--</script>
  <title></title>
</head>
<body>
   <style>--Some Styling--</style>
</body>
</html>';

$html = mysql_real_escape_string($content);

answered May 20, 2016 at 6:10

Manjeet Barnala

2,9951 gold badge13 silver badges21 bronze badges

1 Comment

saiid Over a year ago

Thanks @Manjeet But the function you suggest mysql_real_escape_string is deprecated. Also I see this htmlspecialchars() function but, is it safe to store big html documents in database? Because I have never seen straight HTML in databases.

Jignesh Patel · Accepted Answer · 2016-05-20 05:57:54Z

0

you can use base64_encode and store that string into db with text/blob type of field

answered May 20, 2016 at 5:57

Jignesh Patel

1,0226 silver badges10 bronze badges

2 Comments

Jignesh Patel Over a year ago

Yes... at the time of displaying you need to decode that string

e4c5 Over a year ago

base64 encode/decode is not needed since BLOBs can hold any kind of data. And encode/decode takes up CPU and memory.

Collectives™ on Stack Overflow

PHP how to save HTML string into database [duplicate]

5 Answers 5

2 Comments

4 Comments

Comments

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

4 Comments

Comments

1 Comment

2 Comments

Linked

Related