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

Question

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

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 <i>XYZ Enterprise</i> to <i>ABC Enterprise</i> in php? I need the string to maintain this form <i>ABC Enterprise</i> 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?

Answer 1

To specifically answer your question:

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

1. When inserting into the DB: use prepared statements, rather than manual escaping.
2. 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>";

or

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