Apostrophe causing error when inserting data in MySQL

Question

Apostrophe causing error when inserting data in MySQL

Navigation

#1 by (4 votes)

3

I am a beginner in PHP and I am creating a personal project to consolidate my knowledge until I came across a problem, when entering a name that has an apostrophe this apostrophe causes INSERT INTO not to execute causing the data are not written to the MySQL database.

The query and the rest are all correct because I tried to remove the apostrophe and the data was saved correctly and were displayed on the site correctly, I would like to know how to handle these possible character errors at the time of insertion. >

PHP code

<?php require_once "topo-painel.php"; ?>

<?php

$array_erro = array(
    UPLOAD_ERR_OK           => "Sem erro.",
    UPLOAD_ERR_INI_SIZE     => "O arquivo enviado excede o limite definido no PHP.ini .",
    UPLOAD_ERR_FORM_SIZE    => "O arquivo enviado excede o limite definido no formulário.",
    UPLOAD_ERR_PARTIAL      => "O upload do arquivo não terminou ou foi cancelado antes de ser concluido.",
    UPLOAD_ERR_NO_FILE      => "Nenhum arquivo foi enviado.",
    UPLOAD_ERR_NO_TMP_DIR   => "Não foi definida uma pasta temporaria.",
    UPLOAD_ERR_CANT_WRITE   => "Falha ao escrever arquivo em disco",
    UPLOAD_ERR_EXTENSION    => "Uma extensão do PHP interrompeu o upload do arquivo."
);

if ( isset($_POST["cadastrar"]) ) {

    $champ_nome     = $_POST["nome-champ"];
    $champ_desc     = $_POST["desc-champ"];
    $champ_avatar   = $_FILES["avatar-champ"]["name"];

    $adiciona_champ = "INSERT INTO campeoes (champ_nome, champ_descricao, champ_avatar) VALUES ('$champ_nome', '$champ_desc', 'img/campeoes/{$champ_avatar}' )";

    $executa_champ = mysqli_query($conexao, $adiciona_champ);

    $arquivo_temporario = $_FILES["avatar-champ"]["tmp_name"];
    $arquivo = basename( $_FILES["avatar-champ"]["name"] );

    $diretorio = "../img/campeoes";

    if ( move_uploaded_file($arquivo_temporario, $diretorio."/".$arquivo) ) {
        $mensagem = "Arquivo publicado";
    }else {
        $numero_erro = $_FILES["avatar-champ"]["error"];
        $mensagem = $array_erro[$numero_erro];
    }
}

?>

<?php

?>

<?php //require_once "menu-painel.php"; ?>



<div class="col-md-10 col-md-offset-2">
    <div class="container">

        <div class="col-md-4 col-md-offset-3 mtl">
            <form action="adiciona-campeao.php" method="post" enctype="multipart/form-data">
                <div class="form-group">
                    <label for="nome-champ">Nome do campeão</label>
                    <input type="text" class="form-control" id="nome-champ" name="nome-champ" placeholder="Informe o nome do campeão">
                </div>

                <div class="form-group">
                    <label for="desc-champ">Descrição do campeão</label>
                    <input type="text" class="form-control" id="desc-champ" name="desc-champ" placeholder="Informe a descrição do campeão">
                </div>

                <div class="form-group">
                    <label for="avatar-champ">Avatar do campeão</label>
                    <input type="hidden" name="MAX_FILE_SIZE" value="3145728" />
                    <input type="file" name="avatar-champ" value="avatar" id="avatar-champ">
                    <p class="help-block">Tamanho máximo de 3MB</p>
                </div>

                <input type="submit" value="CADASTRAR" name="cadastrar" class="btn btn-outlined btn-white" />
            </form>

            <?php 
if ( isset($mensagem) ) {
    echo $mensagem;
}
            ?>
        </div>

    </div>
</div>

<?php require_once "rodape-painel.php"; ?>

php mysql sql-insert

asked by anonymous 09.03.2015 / 17:24

1 answer

Segmentation Fault (core dumped): array reading Is InnerClass object created at what time?

score 4 · Accepted Answer

EDIT

The best-viewed solution would be to use Prepared Statement, que chamarei de "PS" ; Let's look at the example:

We keep your original string supplied by the user and then we use PS that will take care of the rest (note that there will not be a "sanitize" of your string, so your apostrophes will appear correctly in the database, but without any risk since it (PS) , will take care of security).

$champ_nome     = filter_input(INPUT_POST, 'nome-champ'); //Mantêm a string original
$champ_desc     = filter_input(INPUT_POST, 'desc-champ'); //Mantêm a string original
$champ_avatar   = 'img/campeoes/' . $_FILES["avatar-champ"]["name"];

//Fornecemos ? nos lugares dos valores, que indicarão um "parâmetro";
$adiciona_champ = $sua_conexao->prepare("INSERT INTO campeoes (champ_nome, champ_descricao, champ_avatar) VALUES (?, ?, ?)");

//Bind Params. Tipos: s = string, i = integer, d = double,  b = blob
//No caso são 3 Strings, então o primeiro parâmetro do bind_param receberá 'sss';
//Caso tivesse algum inteiro por exemplo, poderia ser 'ssis'... e assim por diante.
//Cuidado para não trocar a ordem que você forneceu("INSERT INTO campeoes (champ_nome, champ_descricao, champ_avatar)..."); no caso a ordem é: champ_nome, champ_descricao, champ_avatar.

$adiciona_champ->bind_param('sss', $champ_nome, $champ_desc, $champ_avatar);
$adiciona_champ->execute();


//Continuação

Another solution may be the filter_input function (most used for validation and filtering information from unsafe sources), which provides the capture of external parameters and can apply specific filters.

Explanation of documentation:

filter_input - Gets a specific external variable by name and optionally filters it

Syntax: filter_input(tipo, variável, filtro(opcional:vide documentação));
To filter special characters you can pass the filter: FILTER_SANITIZE_SPECIAL_CHARS .

Your code by applying filter_input;

//Filtrar caracteres especiais
$champ_nome = filter_input(INPUT_POST, 'nome-champ', FILTER_SANITIZE_SPECIAL_CHARS);
//Filtrar caracteres especiais
$champ_desc = filter_input(INPUT_POST, 'desc-champ', FILTER_SANITIZE_SPECIAL_CHARS);

The ideal complement for code continuation would be the use of the Prepared Statement and not the direct concatenation in the SQL String.

References / Add-ons:

Documentation
Filter Types
PHP Prepared Statement

If you need anything else, or would like to add more information to the answer, please let me know.