HTML & HTML Entities - Correct Method?

[metcaelfe]

Hi Everybody.

 

I hope someone here can advise the correct method for storing html/text in a MySQL DB AND retrieving it for updating via a <textarea>

 

Basically, I need to store in my DB knowlege base articles, which have a title, category, product version/edition, description, solution, notes, etc... standard KB fields.. sorta...

 

The issue I am experiencing, or the trouble I am having, is when I need to store both formatting HTML and output HTML in the solution or notes fields, html special chars are being interpreted and the HTML that should be displayed is not there. The char codes are interpreted and the browser then "hides" the HTML tags.

 

For example, the solution may be this:

 

Find the following line:

<serverURL>URL</serverURL>

Change the URL to reflect your current server URL.

 

The html chars are interpreted and changed to the actual less than and greater than signs rather than their &...; form (ie: < > &nbsp etc.)

 

Some code snippets if it helps...

 

Creating a record:

 

...
$sol = addslashes($_POST['asol']);
$notes = addslashes($_POST['anotes']);
include('./conf/connect.php');
mysql_query("INSERT INTO `faq_kb`.`articles` (`id`, `solution`, `notes`, `updated`) VALUES (NULL, '$sol', '$notes', NULL) ") or die(mysql_error());
mysql_close($conn);
...

 

Retrieving a record:

 

...
include('./connect.php');
$get = $_GET['a'];
$sql = "SELECT * FROM articles WHERE id='$get'";
$result = mysql_query($sql);
mysql_close($conn);
while($row=mysql_fetch_array($result))
{
	$solution = $row['solution'];
	$notes = $row['notes'];
}
...
<h4>Solution:</h4>
<div align="right"><textarea name="rsol" style="width:550px;height:150px;">
<?php echo $solution; ?>
</textarea></div>
<h4>Notes:</h4>
<div align="right"><textarea name="rnotes" style="width:550px;height:150px;">
<?php echo $notes; ?>
</textarea></div>
...

 

Updating a record:

 

...
$aid = $_POST['id'];
$sol = addslashes($_POST['rsol']);
$notes = addslashes($_POST['rnotes']);
include('./conf/connect.php');
mysql_query("UPDATE `faq_kb`.`articles` SET solution='$sol', notes='$notes' WHERE id='$aid'") or die(mysql_error());
mysql_close($conn);
...

 

I guess, you can ignore my whole spiel above and simply answer this question:

 

what function do I need to use to encode the variable for inserting into the database, and what function do I then need to use to decode the variable for output so that it retains it's formatting html and my output/displayed html (< >)???

 

Thanks guys!

 

I hope that this makes sense...

 

 

------------------------

Further Example:

 

This is what is output (inside the dashes):

----------------------------

To configure your SysAid Server logging to debug mode please do the following.

 

Open the file serverConf.xml located in the directory:

 

    [...\SysAidServer\root\WEB-INF\conf\]

 

Change

    <sysaidLogLevel>info</sysaidLogLevel>

to

    <sysaidLogLevel>debug</sysaidLogLevel>

 

Save the change and then restart the SysAid Server service.

--------------------------

 

And this is what the source of that looks like:

<div class="divText" id="txtselect"><p>
<span style="font-size: 10pt; font-family: 'Times New Roman',serif; color: black;" lang="EN-US">
To configure your SysAid Server logging to debug mode please do the following.</span></p>
<p><font size="2"><span style="font-family: 'Times New Roman',serif;">Open the 
file </span></font>
<span style="font-size: 10pt; font-family: 'Times New Roman',serif; color: black; font-style: italic;" lang="EN-US">
serverConf.xml</span><span style="font-size: 10pt; font-family: 'Times New Roman',serif; color: black;" lang="EN-US"> 
located in the directory:</span></p>
<p><font size="2"><span style="font-family: 'Times New Roman',serif;">   
</span></font>
<span style="font-size: 10pt; font-family: 'Times New Roman',serif; color: black;" lang="EN-US">
[...\SysAidServer\root\WEB-INF\conf\] </span></p>
<p>

<span style="font-size: 10pt; font-family: 'Times New Roman',serif; color: black;" lang="EN-US">
Change <br>
    </span><font face="Courier New">
<span style="font-size: 10pt; color: black;" lang="EN-US">
<sysaidLogLevel>info</sysaidLogLevel><br>
</span></font>
<span style="font-size: 10pt; font-family: 'Times New Roman',serif; color: black;" lang="EN-US">
to<br>
    </span><font face="Courier New">
<span style="font-size: 10pt; color: black;" lang="EN-US">

<sysaidLogLevel>debug</sysaidLogLevel></span></font></p>
<p><span style="font-size: 10pt; color: black;" lang="EN-US">Save the change 
and then restart the SysAid Server service.</span></p>
</div>

 

And this is a copy paste straight out of phpMyAdmin:

<p>
<span lang="EN-US" style="font-size: 10.0pt; font-family: 'Times New Roman',serif; color: black">
To configure your SysAid Server logging to debug mode please do the following.</span></p>
<p><font size="2"><span style="font-family: 'Times New Roman',serif">Open the 
file </span></font>
<span lang="EN-US" style="font-size: 10.0pt; font-family: 'Times New Roman',serif; color: black; font-style: italic">
serverConf.xml</span><span lang="EN-US" style="font-size: 10.0pt; font-family: 'Times New Roman',serif; color: black"> 
located in the directory:</span></p>
<p><font size="2"><span style="font-family: 'Times New Roman',serif">   
</span></font>
<span lang="EN-US" style="font-size: 10.0pt; font-family: 'Times New Roman',serif; color: black">
[...\SysAidServer\root\WEB-INF\conf\] </span></p>
<p>
<span lang="EN-US" style="font-size: 10.0pt; font-family: 'Times New Roman',serif; color: black">
Change <br>
    </span><font face="Courier New">
<span lang="EN-US" style="font-size: 10.0pt; color: black">
<sysaidLogLevel>info</sysaidLogLevel><br>
</span></font>
<span lang="EN-US" style="font-size: 10.0pt; font-family: 'Times New Roman',serif; color: black">
to<br>
    </span><font face="Courier New">
<span lang="EN-US" style="font-size: 10.0pt; color: black">
<sysaidLogLevel>debug</sysaidLogLevel></span></font></p>
<p><span lang="EN-US" style="font-size: 10.0pt; color: black">Save the change 
and then restart the SysAid Server service.</span></p>

 

Then EVEN CRAZIER, I click the edit button on the article and this is (direct copy paste) what is output to the <textarea>:

<p>
<span lang="EN-US" style="font-size: 10.0pt; font-family: 'Times New Roman',serif; color: black">
To configure your SysAid Server logging to debug mode please do the following.</span></p>
<p><font size="2"><span style="font-family: 'Times New Roman',serif">Open the 
file </span></font>
<span lang="EN-US" style="font-size: 10.0pt; font-family: 'Times New Roman',serif; color: black; font-style: italic">
serverConf.xml</span><span lang="EN-US" style="font-size: 10.0pt; font-family: 'Times New Roman',serif; color: black"> 
located in the directory:</span></p>
<p><font size="2"><span style="font-family: 'Times New Roman',serif">   
</span></font>
<span lang="EN-US" style="font-size: 10.0pt; font-family: 'Times New Roman',serif; color: black">
[...\SysAidServer\root\WEB-INF\conf\] </span></p>
<p>
<span lang="EN-US" style="font-size: 10.0pt; font-family: 'Times New Roman',serif; color: black">
Change <br>
    </span><font face="Courier New">
<span lang="EN-US" style="font-size: 10.0pt; color: black">
<sysaidLogLevel>info</sysaidLogLevel><br>
</span></font>
<span lang="EN-US" style="font-size: 10.0pt; font-family: 'Times New Roman',serif; color: black">
to<br>
    </span><font face="Courier New">
<span lang="EN-US" style="font-size: 10.0pt; color: black">
<sysaidLogLevel>debug</sysaidLogLevel></span></font></p>
<p><span lang="EN-US" style="font-size: 10.0pt; color: black">Save the change 
and then restart the SysAid Server service.</span></p>

Notice how there are NO < or > chars in this?

 

How do I make this work :(

 

 

Thanks guys

[metcaelfe]

Hello everybody!

 

I am storing HTML in a column which is part of a table containing Knowledge Base articles.

 

I am having some issues with echoing this data in my web gui.

 

Currently, I provide a solution to a client, then I paste the solution straight into MS Frontpage to give me the same text but HTML formatted.

I then go to my "New Article" form, paste the HTML from Frontpage straight into a <textarea> and submit to execute the INSERT query.

 

That is all well and good and when I SELECT that for output, the HTML tags (formatting) are all interpreted correctly and the Special Chars/Entities are there too (eg: "<" & ">" in the web browser, which are "<" and ">" in the source code).

 

This works correctly for displaying the articles, as the < > are both displayed as the correct less/greater than symbol (<>) on the web page.

 

THE PROBLEM EXISTS when I wish to EDIT an article.

I query and SELECT the row, and the column which contains the HTML is stored in a var then echoed within a <textarea>.

 

The problem is that the browser is interpreting the HTML Entities as the actual char they are.

Ie:

 

In the database, the data is:

<img src="[uRI OF IMAGE]">

 

 

 

When the article is loaded for viewing, it displays:

<img src="[uRI OF IMAGE]">

 

 

 

When I go to edit the article and output the contents of that column to a <textarea>:

Displayed in Browser within the textarea:

<img src="[uRI OF IMAGE]">

Source code in Browser:

<textarea><img src="[uRI OF IMAGE]"></textarea>

 

This then causes a major issue, as once I edit the text, then submit the changes, the value within the <textarea> updates to the column, thus I lose the HTML entities (< >) and instead the actual chars are saved, which means that next time I load that article for viewing, I won't see the code because the browser interprets it as actual HTML (as it should).

 

How can I avoid this?

 

I have tried playing around with:

addslashes();  <-- I use this on the var which stores the value to be written to the DB.

htmlentities();  <-- Cannot assist as I am using HTML & Entities in the one...

htmlspecialchars();  <-- Same as entities, cannot use this as I have HTML tags & Entities in the one...

 

 

Can anyone offer any advice?

 

Thanks in advance!

 

Cael

[cssfreakie]

hehe quite a long read,

 

but if i sum it up:

 

you use htmlentities and the similar functions before inserting it in the database? if so that's is a bit odd if i am correct (but i am still a php noob so don't trust what i say )

 

I use mysqli_real_escape to connect and insert in the database,  and on output i use either htmlentities or html_specialchars

 

so on input: mysqli_real_escape

on output: htmlentities and the alike

 

Not sure if this helps, but this is how i do it

[metcaelfe]

Cheers for taking the time to read!

 

Sorry for the long post  :-\

 

To be honest, I haven't touched the code for over a week as I have been too busy, so I think if I really put my mind to it and take another look at it, I will resolve this issue...

 

I only use addslashes() on the string before I INSERT anything...

 

I will try your suggestion and see where I end up... I think this comes down to a limitation of echo() into a <textarea> as the browser will just interpret and not leave me with the text  :-(

 

Cheers!