Error inserting jSON in MySQL with PHP [closed]

0

I have a code PHP that causes an array to become jSON

<?php
$imagens = array('imagem1.jpg', 'imagem2.jpg', 'imagem3.jpg');

$json = json_encode($imagens);

$inserir = mysql_query("INSERT INTO table VALUES ('$nome', '$descricao', '$foto', '".$json."'));
?>

But when I run this query, PHP returns me:

  

You have an error in your SQL syntax; check the manual that   correspond to your MySQL server version for the right syntax to use   near   '72249.jpg \ ", \" 66462.jpg \ ", \" 84186.jpg \ ", \" 96462.jpg \ ", \" 80447.jpg \ "] \")' at line 1

I have this problem, but I do not know what it can be.

    
asked by anonymous 17.12.2015 / 14:28

4 answers

2

Stop using all the functions that start with mysql_ * and prohibit them in your code. These functions are deprecated and have been removed in the latest version of PHP (PHP 7). Using the INSERT in the manner you are using is unsafe and is exposed to SQL Injections .

Use the class PDO .

// Conexão com o banco de dados
$dbh = new PDO('mysql:host=servidor_bd;dbname=nome_do_banco', $usuario, $senha);

...

$imagens = json_encode(array('imagem1.jpg', 'imagem2.jpg', 'imagem3.jpg'));

$sql = 'INSERT INTO table VALUES (:nome, :descricao, :foto, :imagens)';
$stmt = $dbh->prepare($sql);
$stmt->bindValue(':nome', $nome, PDO::PARAM_STR);
$stmt->bindValue(':descricao', $descricao, PDO::PARAM_STR);
$stmt->bindValue(':foto', $foto, PDO::PARAM_STR);
$stmt->bindValue(':imagens', $imagens, PDO::PARAM_STR);
$stmt->execute();
    
17.12.2015 / 16:07
0

Try this:

 mysql_real_espace_string(json_encode($imagens));

I already warn that mysql_ functions are discontinued in recent versions of PHP. You should migrate to mysqli_ or PDO .

Recommended reading:

Why should not we use functions of type mysql_ *?

    
17.12.2015 / 16:07
0

It's simple to miss a% code in the code before the last parenthesis:

$inserir = mysql_query("INSERT INTO table VALUES ('$nome', '$descricao', '$foto', '".$json."')");

As Wallace Maxters said, you'd much rather prefer " functions.

More explanations: Why should not we use functions of type mysql_ *?

    
17.12.2015 / 16:12
0

Prefer not to use the old API mysql_ , try mysqli or pdo, because you have the bind option, which will also avoid this kind of problem with the characters.

See this answer:

Note: As in the response from @JorgeB. there is also missing a quotation mark at the end "

To solve the current problem one can escape the single quotation marks ' , try mysql_real_escape_string :

$json = mysql_real_escape_string($json);
mysql_query("INSERT INTO table VALUES ('$nome', '$descricao', '$foto', '". $json ."'"));

However it is highly recommended to migrate to mysqli or pdo:

<?php
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$stmt = $mysqli->prepare("INSERT INTO table VALUES (?, ?, ?, ?, ?)");
$stmt->bind_param('sssss', $nome, $descricao, $foto, $json);

$stmt->execute();

printf("%d linhas inseridas.\n", $stmt->affected_rows);

//Fecha o stmt
$stmt->close();


//Fecha a conexão
$mysqli->close();
    
17.12.2015 / 16:09