Simple quotes breaking the UPDATE query in mysql

Question

I have the following difficulty, in my application, when executing the registration function, when I type some text with enclosed quotes, the query does not work.

Follow the code below:

Here I take the values passed by the controller, using the __get().

PortfolioDAO.php:

public function alterarPortfolio(Portfolio $portfolio)
{
    $alterar_imagem = ($portfolio->__get('imagem') != '' ? ", imagem = '{$portfolio->__get('imagem')}'" : '');

    $values = "
                empresa = '{$portfolio->__get('empresa')}',
                link = '{$portfolio->__get('link')}',
                tipo_servico = '{$portfolio->__get('tipo_servico')}'
                {$alterar_imagem}
            ";

    $this->alterar($portfolio->__get('id'), $values);
}

Here is the query:

Model.php:

public function alterar($id, $values)
{
    $query = "UPDATE {$this->tabela} SET {$values} WHERE id = {$id}";
    $stmt = $this->db->prepare($query);
    echo $query;
    $stmt->execute();
}

Remembering that, data registration usually occurs, the problem is when I add a simple quotes in the input.

Answer 1

Basically, the problem is solved by replacing single quotes with two quotes, e.g.: str_replace("'", "''", "Joana D'arc"), probably your case the change will be similar to the:

    $values = "
                empresa = '{str_replace("'", "''", $portfolio->__get('empresa'))}',
                link = '{str_replace("'", "''", $portfolio->__get('link'))}',
                tipo_servico = '{str_replace("'", "''", $portfolio->__get('tipo_servico'))}'
                {$alterar_imagem}
            ";

However, there is already a function for this mysql_real_escape_string. Recommended use of the function, since it will also handle too many dangerous characters.

$query = sprintf("UPDATE XPTO SET value1='%s' WHERE id='%s'",
            mysql_real_escape_string($newValue1),
            mysql_real_escape_string($currentId));

for more information on how to use the function visit https://www.php.net/manual/en/function.mysql-real-escape-string.php