How is it necessary to process the text and html-code for writing to the MySQL database? What is the scheme?

I rummaged through the Internet, everywhere the advice for writing to the database is to use mysql_real_escape_string (), and nothing more is needed. But what about html specials? For example, "⇔" when inserted into a form is displayed as a symbol, and not as a symbol of the html-code. Accordingly, when writing to the database, it turns into "?"

As I understand it is necessary to process the text like this before pasting:

$inputText = htmlentities($inputText, ENT_NOQUOTES, 'UTF-8'); $inputText = mysql_real_escape_string($inputText);

Accordingly, when outputting from the database for display, it is necessary to process the text like this:

 $inputTex = html_entity_decode($inputTex);

Is this algorithm correct or should it be done somehow differently?

The coding site utf8.

  • I store only the text in the database. Html is stored in the site templates and render them, substituting the variables in which the text is stored (from the database). And about the fact that html should be stored in the database I hear for the first time. Try to store it as a hash (and how are you going to transfer the html structure with their indents?). - discipleartem

1 answer 1

The question is very simple, but at the same time very characteristic.

It perfectly collected the most popular misconceptions of novice users of PHP. Let's try to understand them.

Storage

The very first thing to learn is not to confuse data storage and its use in SQL queries. These are completely different things. With regard to storage, it should be understood that absolutely any data can be stored in the database, and at the same time they should be stored as is. That is, to store them in any way, you don’t need to process at all.

Therefore, answering the questions posed in the post (not quite correct, as we see):

  • How to store any data in the database? Data must be stored as is.
  • How to process data for DB? No In no case can not handle.
  • How to store HTML? No special action is required for either HTML or any other data. All data is stored exactly the same.

Using data in SQL queries.

But this is a completely different question. The only one in response to which we will have to do something. But at the same time , we will not touch the data ourselves anyway. Yes Yes! Even for placing in SQL query, we will not process the data in any way. The fact is that "advice for writing to the database to use mysql_real_escape_string ()" is simply enchanting stupidity, unfortunately, distributed in millions of copies.

And the only correct way to add data to the SQL query is to do it through placeholders .

That is, to use any data in the SQL query, you must first write question marks in their place:

 INSERT INTO users (name, lastname) VALUES (?,?)

this, by the way, applies to all requests in general. SELECT we write the same way:

 SELECT * FROM users WHERE name=?

after that, it will be necessary to prepare the request, and then execute it, passing the variables separately . Here’s how it happens with PDO:

 $stmt = $pdo->prepare("INSERT INTO users (name, lastname) VALUES (?,?)"); $stmt->execute(array($name, $lastame));

that is, the idea is this: if we need to substitute a variable in the request, we need to put a question mark instead. And pass the variable itself after.

Thus, we will be guaranteed against any mistakes and troubles, since PHP itself will process all variables for us and do it correctly .

HTML special characters

I think that the attentive reader has already caught the idea: HTML in general has nothing to do with the database. Slightest. These are completely different things. That is, the PHP user should never have the idea to use a function in which the word "HTML" is found for any work with the database, and when working with HTML, a function in which the word "mysql" occurs.

Functions for working with HTML should be used to work with HTML.

That is, if we are going to display HTML text in HTML, then we should display it as it is.

But if we are going to display text that is not HTML in HTML, we must format it so that it does not accidentally spoil the layout for us. Usually, the htmlspecialchars() function is used for this.

Encoding

If any character is saved as a question mark, then this is not an HTML coding problem, but an encoding problem that simply does not support this character. In order for a database to save characters of type ⇔ , it must have the utf8 encoding. As well as the PHP script, establishing a connection with the database must set the encoding of this connection to utf8 . At the same time, when displaying the form to the user, he must issue the HTTP Content-type header with UTF-8 encoding.

And then all the characters will be recorded safe and sound.

  • 3
    Thank you, I will add your answer to everything in one place: Protection-from-sql-injections-in-php - copenhagen
  • Super answer, what I needed! - alwizo
  • Comments are not intended for extended discussion; conversation moved to chat . - Qwertiy ♦
  • @ Ipatiev Placers are not the only correct way to add data to an SQL query, and your statement about mysqli_real_escape_string () is wrong. This function is not outdated in the PHP documentation, and according to your statement, thousands of people who participate in the development of PHP do not agree. Using mysqli_real_escape_string () is possible and necessary for escaping data for SQL. Placers will be useful primarily for beginners to eliminate possible SQL injections. Please do not delete this comment, as it is important for those who read the answer. - Miron
  • one
    @Miron, if the function is not obsolete, it does not mean that it should be used. - Qwertiy ♦