Error inserting json in Mysql with PHP

Question

I have a code PHP which 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 running this query, PHP returns me:

You have an error in your SQL syntax; check the manual that Corresponds 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 don’t know what it could be.

Answer 1

Stop using all functions starting with mysql_* and ban them in your code. These functions are discontinued and have been removed in the latest version of PHP (PHP 7). Using INSERT the way you are using is not safe 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();

Answer 2

Try this:

 mysql_real_espace_string(json_encode($imagens));

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

Recommended reading:

Why should we not use mysql type functions_*?

Answer 3

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

See this answer:

• Why should we not use mysql type functions_*?

Note: Like this in reply by @Jorgeb. also missing an quotation mark at the end "

To solve the current problem one should escape the apostrophes (simple quotes) ', try the 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();

Answer 4

She’s simply missing one " in the code before the last parenthesis:

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

As Wallace Maxters said and very well prefer the functions mysqli_.

Further explanations: Why should we not use mysql type functions_*?