php 在mysql表中插入html代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2371093/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting html code in a mysql table
提问by SpikETidE
I use joomla to manage a website... and i am developing a stand alone php application that will insert and modify data into the tables that are used by joomla to store the html of webpages that it dynamically creates...
我使用 joomla 来管理一个网站...我正在开发一个独立的 php 应用程序，它将插入和修改数据到 joomla 用来存储它动态创建的网页的 html 的表中...
The way it works is i use a joomla component to create content and the html code of these articles are stored in a field in a table, say content_table, by joomla.. This html code is later retrieved to construct a part of a webpage.
它的工作方式是我使用 joomla 组件来创建内容，并且这些文章的 html 代码存储在表中的一个字段中，例如 content_table，由 joomla .. 稍后检索此 html 代码以构建网页的一部分。
I want to do the same with my standalone app... i.e add the html code to the filed in content_table which can later be retrieved by joomla to construct the part of the page.
我想对我的独立应用程序做同样的事情...即，将 html 代码添加到 content_table 中的文件中，稍后可以由 joomla 检索以构建页面的一部分。
The problem is : The html code,naturally, of course, has a lot of single and double quotes and this pose a problem while inserting into the database.. I've tried mysql_escape_string() and still get syntax errors..
问题是：当然，html 代码有很多单引号和双引号，这在插入数据库时会造成问题.. 我试过 mysql_escape_string() 并且仍然出现语法错误..
I can use addslashes() but since joomla itself retrieves the code later, it is not possible to use stripslashes() while retrieving it later....
我可以使用addslashes() 但是由于joomla 本身稍后会检索代码，因此在稍后检索时无法使用stripslashes() ......
Is there anyway i can add the html code the table's field...
无论如何我可以在表格的字段中添加html代码...
Thanks for your suggestions...!!
谢谢你的建议...!!
Edit : After adding mysql_escape_string() i get
编辑：添加 mysql_escape_string() 后我得到
Error adding details. Reason : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fulltext = '\n
This is my query :
这是我的查询：
UPDATE $jos_content
SET introtext = '$intro_code',
fulltext = '$article_code'
WHERE id = '$article_id'";
The input string is as follows :
输入字符串如下：
$article_code = '<hr id="system-readmore" />
<center>{loadposition user50}</center>
<p style="text-align: center;">
<span style="color: rgb(0, 255, 255);">
<i>
<b>
<span style="font-size: x-large;">
<span style="font-family: Arial;">
</span>
</span>
</b>
</i>
</span>
<span style="color: rgb(0, 255, 255);">
<i>
<b>
<span style="font-size: x-large;">
<span style="font-family: Arial;">
<?php echo $title; ?>
</span>
</span>
</b>
</i>
</span>
<span style="color: rgb(0, 255, 255);">
<i>
<b>
<span style="font-size: x-large;">
<span style="font-family: Arial;">
<br />
</span>
</span>
</b>
</i>
</span>
</p>
<p style="text-align: center;">
<img height="269" width="515" border="3"
title="<?php echo $title; ?>"
alt=" <?php echo $title; ?>"
src="<?php echo $article_image;?>"
</p>
<p>
<span style="font-size: small;">
<span style="font-family: Arial;">
<span style="color: rgb(153, 204, 255);">
<p style="margin-top: 2px; margin-bottom: 2px; margin-left: 120px; text-align: left;">
<i>
<span style="color: rgb(0, 255, 0);">
<strong>
Cast :
</strong>
<b>
</b>
</span>
</i>
<span style="color: rgb(0, 255, 255);">
<b>
<?php echo $cast; ?>
</b>
</span>
<i>
<span style="color: rgb(0, 255, 255);">
<b>
<br />
</b>
</span>
</i>
<span style="font-family: Arial;">
<span style="font-size: small;">
<span style="color: rgb(153, 204, 255);">
</span>
</span>
<span style="color: rgb(0, 255, 0);">
<i>
<strong>
Direction
</strong>
</i>
<strong>
:
</strong>
<b>
</b>
</span>
<span style="color: rgb(0, 255, 255);">
<b>
<span class="href"
id="ctl00_ContentPlaceHolderMainContent_FormView1_Director">
<?php echo $director; ?>
</span>
</b>
</span>
</span>
<span style="font-family: Arial;">
<br />
<span style="color: rgb(0, 255, 0);">
<i>
<strong>
Production
</strong>
</i>
<strong>
:
</strong>
<b>
</b>
</span>
<span style="color: rgb(0, 255, 255);">
<b>
<?php echo $direction; ?>
</b>
</span>
<span style="color: rgb(255, 102, 0);">
<i>
<b>
<br />
</b>
</i>
</span>
<span style="font-family: Arial;">
<span style="color: rgb(0, 255, 0);">
<span style="font-family: Arial;">
<span style="font-size: small;">
<i>
<strong>
Music
</strong>
</i>
<strong>
:
</strong>
</span>
</span>
</span>
</span>
<span style="color: rgb(0, 255, 255);">
<b>
<i>
</i>
<?php echo $music; ?>
<i>
<br />
<span style="color: rgb(0, 255, 0);">
Lyrics
</span>
</i>
<span style="color: rgb(0, 255, 0);">
:
</span>
<i>
</i>
</b>
</span>
<span style="color: rgb(0, 255, 255);">
<b>
<?php echo $lyrics; ?>
</b>
</span>
<span style="color: rgb(0, 255, 255);">
<b>
<i>
<br />
</i>
<span style="color: rgb(0, 255, 0);">
<i>
Year
</i>
:
</span>
<?php echo $year; ?>
</b>
</span>
</span>
<i>
<span style="color: rgb(0, 255, 255);">
<b>
</b>
</span>
</i>
</p>
</span>
</span>
</span>
</p>
<p>
<left>
{loadposition user14}
</left>
</p>
<div style="text-align: center;">
<p>
<i>
<span style="font-family: Arial;">
<b>
<span style="font-size: medium;">
<span style="color: rgb(51, 255, 255);">
Click
<img src="images/stories/Play button1.png"
alt="alt" />
in the Playlist to Download Songs
</span>
</span>
</b>
</span>
</i>
</p>
</div>
<table border="0" align="center">
<tbody>
<tr>
<td>
<h4 style="text-align: center;">
<i>
<span style="color: rgb(102, 255, 0);">
<b>
<b>
High Bandwidth Users
</b>
</b>
</span>
</i>
<i>
<span style="color: rgb(102, 255, 0);">
<b>
<b>
</b>
</b>
</span>
</i>
<span style="color: rgb(102, 255, 0);">
<b>
</b>
</span>
</h4>
</td>
<td>
<h4 style="text-align: center;">
<i>
<span style="color: rgb(102, 255, 0);">
<b>
<b>
Low Bandwidth Users
</b>
</b>
</span>
</i>
<span style="color: rgb(102, 255, 0);">
<b>
<br />
</b>
</span>
</h4>
</td>
</tr>
<tr>
<td>
{auto width="235" displayheight="0" height="225"} <?php echo $hqList; ?> {/auto}
</td>
<td>
{auto width="235" displayheight="0" height="225"}<?php echo $lqList; ?>{/auto}
</td>
</tr>
</tbody>
</table>
<center>
{loadposition user50}
</center>';
采纳答案by SpikETidE
Well..Debugged it.. Turns out the problem was after all not with the escaping function...
嗯..调试它..结果问题毕竟不是转义函数......
Check out the query :
查看查询：
UPDATE $jos_content
SET introtext = '$intro_code',
fulltext = '$article_code'
WHERE id = '$article_id'";
You can see the 'fulltext' field... Apparently, the word "fulltext" is a mysql keyword... To be precise,it's a field type like TEXT, INT, MEDIUMTEXT etc...
你可以看到“全文”字段......显然，“全文”这个词是一个mysql关键字......准确地说，它是一种字段类型，如TEXT、INT、MEDIUMTEXT等......
I changed the query to this
我将查询更改为此
"UPDATE $jos_content
SET $jos_content.introtext = '$intro_code',
$jos_content.fulltext = '$article_code'
WHERE $jos_content.id = '$article_id'";
And voila...!!!!
瞧……！！！！
回答by iroel
I prefer to convert code to ordinary string before inserting to database. I think, it's most safe scenario. Consider using this code:
我更喜欢在插入数据库之前将代码转换为普通字符串。我认为，这是最安全的情况。考虑使用以下代码：
$article_code = base64_encode($article_code);
/* insert to database */
So, when you want to use that code back, just decode using base64_decode. I suggest you to use 'text' data type for saving $article_code rather than 'varchar'.
因此，当您想使用该代码时，只需使用 base64_decode 进行解码。我建议您使用 'text' 数据类型来保存 $article_code 而不是 'varchar'。
回答by Ignacio Vazquez-Abrams
You should not need slashes. The only thing that will cause a problem during normal inserts is the quotes, and mysql_escape_string()should handle that excepting charset issues. Try mysql_real_escape_string()as well.
你不应该需要斜线。在正常插入期间唯一会导致问题的是引号，并且mysql_escape_string()应该处理除字符集问题之外的问题。也试试mysql_real_escape_string()。
Also, note that storing raw user-supplied HTML in the database can lead to security issues. Consider using something like bbcode or markdown instead.
另请注意，将用户提供的原始 HTML 存储在数据库中可能会导致安全问题。考虑使用诸如 bbcode 或 markdown 之类的东西。
回答by Ajay Kumar
This is the best way i found addslashes()
这是我找到的最好方法 addslashes()
$article_code = addslashes($article_code);
UPDATE $jos_content
SET introtext = '$intro_code',
fulltext = '$article_code'
WHERE id = '$article_id'";
回答by K Prime
Just to confirm, your query looks like this right:
只是为了确认，您的查询如下所示：
$query = '
UPDATE "'.mysql_real_escape_string ($jos_content).'"
SET introtext = "'.mysql_real_escape_string ($intro_code).'",
fulltext = "'.mysql_real_escape_string ($article_code).'"
WHERE id = "'.mysql_real_escape_string ($article_id).'"
";
回答by Chinmay235
fulltextis a mysql predefine keyword. Please use Acute(`) or single quote(')
fulltext是一个 mysql 预定义关键字。请使用 Acute(`) 或单引号 (')
Here is code -
这是代码 -
UPDATE $jos_content
SET `introtext` = '$intro_code',
`fulltext` = '$article_code'
WHERE `id` = '$article_id'";
回答by Waiyl Karim
I had the same problem, I fixed it with regular expressions.
You can use something like this: $target = '{~p class={{q}}important-text{{q}}~}Some text here {~/p~}';
我有同样的问题，我用正则表达式修复了它。你可以使用这样的东西：$target = '{~p class={{q}}important-text{{q}}~}Some text here {~/p~}';
and then use the preg_replace()function:
然后使用preg_replace()函数：
class handle
{
public static function makehtml($target)
{
$output = preg_replace("#{~#", "<", $target);
$output = preg_replace("#~}#", ">", $target);
$output = preg_replace("#{{q}}#", '"', $target);
return $output;
}
}
echo handle::makehtml($target);
// output : <p class="important-text">Some text here</p>
回答by Rob Smart
if you are worried about space and are using the base 64 encode method posted here you could use the gzdeflate command in php to cut it down then encode it. Here is a little test script on some generated sample characters. Obviously there are different compression methods to look into if you are concerned with size but this would mean you could put it into mysql minimising the size on the db. You could then read it back with gzinflate(base64_decode( ... ));
如果您担心空间并使用此处发布的 base 64 编码方法，您可以使用 php 中的 gzdeflate 命令将其裁减然后对其进行编码。这是一些生成的示例字符的小测试脚本。显然，如果您关心大小，可以研究不同的压缩方法，但这意味着您可以将其放入 mysql 中，以最小化数据库的大小。然后你可以用 gzinflate(base64_decode( ... )); 读回来。
<?php
//generate sample chars for the example
function generateRandomString($length = 10000) {
$characters = '01234"56/789abcdefghij:klmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
$randomString = '';
for ($i = 0; $i < $length; $i++) {
$randomString .= $characters[rand(0, strlen($characters) - 1)];
}
return $randomString;
}
//
$string =generateRandomString();
echo 'string size:'.strlen($string);
$b64html = base64_encode($string);
echo '<br/>'.'Original base64 size:'.strlen($b64html);
$compressed = gzdeflate($string, 9);
echo '<br/>'.'compressed size:'.strlen($compressed);
echo '<br/>'.'base64 compressed size:'.strlen(base64_encode($compressed));
/* insert into db the base64_encode($compressed); */
?>
回答by user3053637
Calling the mysql_escape_string() function passing in the variable that holds the html text like:
调用 mysql_escape_string() 函数，传入保存 html 文本的变量，如：
mysql_escape_string($_POST["text"]);
mysql_escape_string($_POST["text"]);
will ensure that the special characters like quotes in the text will not cause a php error and the database will be updated successfully.
将确保文本中的引号等特殊字符不会导致 php 错误并且数据库将成功更新。
回答by nirali
insert html code into database
将html代码插入数据库
$title = $_POST['title'];
$code = '<h1>hello</h1></p>this html tage</p>';
$htmlcode = addslashes($code);
$query = "INSERT INTO `template` (`title`,`code`) VALUES ('".$title."','".$htmlcode."');";
mysqli_query($con,$query);
after insert data into database show data in listing page
将数据插入数据库后，在列表页面中显示数据
$result = mysqli_query($con,'SELECT * FROM template');
while($row = mysqli_fetch_assoc($result)) {
echo htmlentities($row['code']);
}
htmlentities($row['code']); //here your row name;
