When do you need to do data shielding?

For many years, SQL queries were executed as is, in a string. If a variable was placed in such a line, it was necessary to carefully monitor that it did not contain in its structure an SQL fragment created by an attacker that alters the logic of the query — an SQL injection

 "SELECT name FROM tbl WHERE id = $id" 

For example, if the string "0 UNION SELECT password FROM tbl WHERE id = 234" placed in $id , then instead of the user name, you can output the password / hash of the user ID 234. If the number is sufficiently easy to secure by coercion to a number, then in the case of strings this more difficult

 "SELECT * FROM tbl WHERE nickname = '$name'" 

For such a request, the injection might look like this "' OR nickname='root'" . You cannot exclude quotes because usernames may contain them - they need to be escaped, i.e. turn this snippet into "\' OR nickname=\'root\'" . There are many functions and methods to do this, one of which is the real_escape_string() method that you mentioned in the question. Escaping is always done before inserting it into the database in order to interpolate the variable correctly and avoid SQL injections.

However, in recent years it is not customary to insert variables directly into a query — this is considered a bad tone, posing a security risk. Instead, use prepared queries that use parameters. The following is an example using the PDO extension (a similar approach is used for the mysqli extension)

 <?php try { $pdo = new PDO( 'mysql:host=localhost;dbname=test', 'root', '', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]); $query = "SELECT `text` FROM tbl WHERE id = :id"; $tbl = $pdo->prepare($query); $tbl->execute(['id' => 3]); $row_images = $tbl->fetchAll(PDO::FETCH_COLUMN); echo "<pre>"; print_r($row_images); echo "</pre>"; } catch (PDOException $e) { echo "Ошибка выполнения запроса: " . $e->getMessage(); } 

You compose a query using tokens, in this case :id , which are filled at the stage of query execution with the execute() method. With this approach, SQL injections disappear as a class and you do not need to perform screening in rows.

There is another threat - XSS-injection, inserting malicious HTML / JS-constructions in order to change the logic of the behavior of the JS-part of the site (alternatively, theft of cookies, passwords, distortion of the site design, etc.). To deal with them, all HTML output from the database is converted to a secure form using the htmlspecialchars() function. Text is converted after being extracted from the database. Firstly, you can save the original string entered by the user and investigate the situation, secondly, you do not have problems with editing: with multiple editing there is a possibility that HTML entities are transformed many times

 & => & => & => & 

Although in new versions of PHP, the htmlspecialchars() function can recognize this situation, it is better to prevent it and to use htmlspecialchars() always after retrieving from the database.

Before adding. Escaping just removes what should not be.