How to correctly escape characters in SQL queries if you need to send HTML code?

Question

PHP

I interpose (INSERT) the data into a DB through PHP.

The $FnlCodeMsgMobile tag data is not inserted.

$FnlCodeMsgMobile = "
<img src=\"$NewFileDir\" alt=\"$NewFileDir\" class='responsive-img' id='dynamic$maxIDNumberMobile'>
<script>
    document.getElementById('$maxIDNumberMobile').addEventListener('click', function() {
        lightGallery(document.getElementById('$maxIDNumberMobile'), {
            dynamic: true,
            thumbnail: true,
            dynamicEl: [{
                "src": '$NewFileDir',
            }]
        })

    });
</script>
";

Moreover, if you simply assign "Test" to a variable, then everything is inserted. What else needs to be escaped so that the row is successfully inserted into the database?

In general, I simply replaced single quotes with double ones and escaped them too, everything was inserted and it worked.

Answer 1

HTML does not need to be specially escaped. And you don't need to screen anything at all . Shielding is trash, waste, the last century and injections. The data in a DB should be sent simply separately from request.
It's amazing that so many people signed up for this question.
Well, I don’t know - how to subscribe to the question "how much is six six?". Not "twice two", but close.
Working with the database is the very basics of PHP, which are covered in every textbook from all sides. Is it really so bad that this question is of such great interest to be on the list of interesting ones on the toaster?
And your personal problem here is not at all "how to screen". HTML does not need to be escaped in any special way, the database absolutely does not care what you insert into it. Your problem is that you basically don't know PHP and get confused in the basics of the language, you use an outdated version that will not work on a live site. And even the most basic syntax scares you. Therefore, I'm sorry, I will write an answer not for you, but for those who, once again, ask this evergreen question, but for some reason could not master the basic skills of working with Google / Yandex.
To insert data into mysql, and never think about any "escaping" at all, you need to
1. Replace all variables in the query with question marks
2. Prepare the query for execution
3.
4. Run the request.
As I said, the functions that begin with mysql are long gone in the language. Only those that start with mysqli and PDO remain. Here I will give examples for them.
mysqli

$type = 'testing';
$reporter = "John O'Hara";
$query = "INSERT INTO contents (type, reporter) VALUES(?, ?)"; // заменем
$stmt = $mysqli->prepare($query); // подготавливаем
$stmt->bind_param("ss", $type, $reporter); // привязываем
$stmt->execute(); // исполняем

Important! For this code to work, the mysqli
PDO connection must first be properly established

$type = 'testing';
$reporter = "John O'Hara";
$query = "INSERT INTO contents (type, reporter) VALUES(?, ?)"; // заменяем
$stmt = $pdo->prepare($query); // подготавливаем
$stmt->execute([$type, $reporter]); // привязываем и исполняем

Important! For this code to work, you first need to set up the PDO connection correctly.
You can also use third-party libraries, which are even easier with. But for some reason, the use of third-party libraries will scare beginners worse than zombies in a cemetery on a moonless night, so I won’t insist

Answer 2

I love using the safemysql library
https://github.com/colshrapnel/safemysql
phpfaq.ru/safemysql
In short:

$data = array( 'field' => $FnlCodeMsgMobile );
$db->query( 'INSERT INTO ?n SET ?u', 'table_name', $data );

Answer 3

$data =
<<< start
<img src=\"$NewFileDir\" alt=\"$NewFileDir\" class='responsive-img' id='dynamic$maxIDNumberMobile'>
<script>
    document.getElementById('$maxIDNumberMobile').addEventListener('click', function() {
        lightGallery(document.getElementById('$maxIDNumberMobile'), {
            dynamic: true,
            thumbnail: true,
            dynamicEl: [{
                "src": '$NewFileDir',
            }]
        })

    });
</script>
start;

As a Heredoc variant

Answer 4

Nikita Guriev , do

$sql = "INSERT INTO `ваша_таблица`  data='".mysql_real_escape_string($data)."' WHERE ваше условие вставки";

everything should fit in. Set the field name and table name to yours, $data from Alexander's comment above (obtained via Heredoc).
It didn't work for you without Heredoc, because you took the string in double quotes ", and inside it used " without escaping to:
"src": '$NewFileDir',
and line breaks inside " " could be processed incorrectly.
There is a reinforced concrete option to insert anything (if the size of the database field is enough):

$sql = "INSERT INTO `ваша_таблица`  data='".base64_encode( gzcompress($data) )."'";

and when you get it from the database, you need to unpack it back:

$data = gzuncompress( base64_decode( $row['data' ] ) );

Of the minuses:
- additional load on packing-unpacking, but for your line this is nonsense. base64_encode() increases the data size by about 1.5 times, but zip squeezes more.
From pluses:
- SQL injections are absent as a class
- it is not necessary to escape anything.
- data encoding - on the drum, even UTF8 data will be inserted into the cp1251 table. The main thing is to give them in the correct encoding (when you pull them out of the table and send them to the browser)