在php中将特殊字符从sql转换为普通字符

问题描述：

1) I have a textarea in my html. Inside the textarea I wrote: ABC Enterprise. When saving into the sql database it saved as XYZ Enterprise

2) Does anyone know how to retain < and </> when saving into the database without converting? If this is not possible, does anyone know how to convert XYZ Enterprise to ABC Enterprise in php? I need the string to maintain this form ABC Enterprise in php not html.

I have tried preg_replace("/&([a-z])[a-z]+;/i", "$1", htmlentities($company)), iconv('utf-8', 'ascii//TRANSLIT', $company), htmlspecialchars($compnay), many other ways I happened to stumble upon on stackoverflow but nothing seemed to work. Any help?

1）我的html中有textarea code>。在 textarea code>里面，我写了：＆lt; i＆gt; ABC Enterprise＆lt; / i＆gt; code>。保存到sql数据库时，它保存为＆amp; lt; i＆amp; gt; XYZ Enterprise＆amp; lt; / i＆amp; gt; code> p>

2）有没有人知道如何保存到数据库而不进行转换时如何保留＆lt; code>和＆lt; /＆gt; code>？如果无法做到这一点，是否有人知道如何将＆amp; lt; i＆amp; gt; XYZ Enterprise＆amp; lt; / i＆amp; gt; code>转换为＆lt; i＆gt; ABC Enterprise＆lt; / i＆gt; ; code>在php中？我需要字符串来保持这种形式＆lt; i＆gt; ABC Enterprise＆lt; / i＆gt; code>在php而不是html。 p>

我试过 preg_replace（“ /＆amp;（[az]）[az] +; / i“，”$ 1“，htmlentities（$ company）） code>， iconv（'utf-8'，'ascii // TRANSLIT'， $ company> code>， htmlspecialchars（$ compnay） code>，还有很多其他方法我碰巧偶然发现了stackoverflow，但似乎没什么用。有什么帮助吗？ p> div>

答

To specifically answer your question:

How to retain <> and </> when inserting into the DB? [paraphrased, emphasis added]

Simple: don't modify your data. As discussed below, however, be smart about it and insert the data using a prepared statement.

Why is your data being changed? Most likely because your code is doing some form of modification of the data before putting it in the database. In PHP, this generally means one of:

htmlentities
htmlspecialchars

The general advice for years was simply "escape all your data or suffer the XSS/CSRF/Sql Injection/other attack consequences!" The problem is that there are nuances of when and how to escape and in the zeal for security, many websites over do it. As you've described your situation, I would consider:

When inserting into the DB: use prepared statements, rather than manual escaping.
When pulling from the DB: be judicious when you apply escaping techniques.

A prepared statement is where you tell the database the format of what you're going to send, then send the data in a separate communication. If there's anything awry, the DB knows best how to find it. For example:

$pstmt = $dbh->prepare('INSERT INTO tab (html) VALUES (?)');
$pstmt->execute(array($_POST['my_textarea']));

Note the lack of any sanitization, using the $_POST variable directly. What the user sent to you is what you put in the DB, with zero modification. Because the DB server was sent a format first, it will not allow any ulterior SQL injection shenanigans.

However, when pulling data out of the DB, you need to be careful of exactly what data goes where. For example, to allow < and > characters inside of the content might be foolhardy, depending on your context. I'll leave it to you to decide whether you want to escape the output inside of your <textarea>:

echo "<textarea>$textarea_content_as_retrieved_from_db</textarea>";

echo '<textarea>' . htmlentities( $textarea_content_as_retrieved_from_db ) . '</textarea>';

在php中将特殊字符从sql转换为普通字符

相关推荐