Simple quotes breaking the UPDATE query in mysql

Asked

Viewed 145 times

0

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.

php mysql database sql-insert
  • 1

    I sent you a reply, but I also suggest you search for sql Injection. Because, this kind of implementation can generate security vulnerabilities.

1 answer

0

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

  • Look, using this change you made, the query that is sent to the database is this: UPDATE portfolio SET company = mysql_real_escape_string('Art’s Hair & Aesthetics'), link = mysql_real_escape_string('https://artsdepilacaoeestetica.com.br/'), tipo_servico = mysql_real_escape_string('1') WHERE id = 3 , on the issue of sql Injection, really, my application is very vulnerable, I’m with less than a year of studies using php, if you have any indication of courses where I can is learning more, thank you.

  • You are right the code was wrong. I performed the correction, but I am no environment to test here. About SQL Injection, it is a type of attack that exploits the passage of SQL parameters using quotes and other characters like "-" to try to gain access to database information, has.

  • It hasn’t worked haha yet, but I just decided here, I’m going to create everything from scratch, implementing the whole security issue related to sql Injection and using newer functions for crud. Thanks for your help, brother.

Browser other questions tagged php mysql database sql-insert

You are not signed in. Login or sign up in order to post.