MySQL error when inserting data containing apostrophes or single quotes

Question

I have the table like this:

CREATE TABLE `PsicUtentesConsulta` (
  `Id` int NOT NULL AUTO_INCREMENT,
  `DataConsulta` date DEFAULT NULL,
  `CodigoUtente` int NOT NULL,
  `Descricao` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `Colaborador` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=391 DEFAULT CHARSET=utf8;

Then I enter the data as follows:

$DataConsulta = $_POST["DataConsulta"];     
$CodigoUtente1 = $_POST["CodigoUtente1"];       
$Descricao = $_POST["Descricao"];
$Colaborador = $_SESSION['usuarioId'];

$query = 'INSERT INTO raddb.PsicUtentesConsulta
                (DataConsulta, CodigoUtente, Descricao, Colaborador)  
          VALUES ( ?, ?, ?, ?)';
$stmt = $conn->prepare( $query );
$stmt->bind_param("ssss", $DataConsulta, $CodigoUtente1, $Descricao, $Colaborador);
$stmt->execute();

In the Descricao field where I insert the text, when I use quotes or apostrophes, in the database insert this way:

Olá Este é um teste. Este é outro \"teste\".  Outro \'teste\'.

And you should insert this:

Olá Este é um teste. Este é outro "teste".  Outro 'teste'.

I've tried these ways, but it doesn't work:

$Descricao= mysqli_real_escape_string($conn, $Descricao);

OR

$Descricao= str_replace("'","\'", $Descricao);

But they didn't solve the problem

CODE:

function inserir_consultainf1()
{  
var dadosajax = {
    'DataConsulta' : $("#DataConsulta3").val(),
    'CodigoUtente' : $("#CodigoUtente8").val(),
    'CodigoUtente1' : $("#CodigoUtente9").val(),
    'Descricao' : $("#Descricao3").val()        
};

$.ajax({
    url: './registopsiconsultainf1',
    type: 'POST',
    cache: false,
    data: dadosajax,
    error: function(){
        Swal.fire("Erro!", "Tente novamente. Caso persista o erro, contatar Administrador!", "error");
    },
    success: function(result)
    { 

        $('.form9')[0].reset();
        $("#ad_Modalnovaconsultainf").modal("hide");
        $("#dataModal10").modal("hide");
        Swal.fire('Boa!', 'Gravado com sucesso!', 'success');
    }
});
}

user_4811873 · Answer

As others have said, you don't "escape" strings that you're using as parameters: the literal value of the string will be taken as is. (Backslashes, for instance, will remain "literal backslashes.")

Of course, if the string first appears in, say, an assignment statement in your programming language, escaping will be handled by the programming language in the usual way to develop the actual string value that is to be used. But the SQL engine will then use "that actual sequence of bytes, whatever it is," and will not look at it further. A parameter is not "part of the query," but rather an input to it.

MySQL error when inserting data containing apostrophes or single quotes

1 Answers