PDO exception raised when prepared statment is execued but values are inserted

Question

I use PDO to insert values into my db. I use the following code in a config file for connecting to my db

define('USER', 'XX');
define('PASS', 'YY');
define('DSN', 'mysql:host=localhost;dbname=mydbtest;charset=utf8mb4');
try {
    $dbh = new PDO(DSN, USER, PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"));
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    print "Erreur ! : " . $e->getMessage() . "<br/>";
    die();
}

In a separate file I retrieve the $_POST values, prepare my query and execute it like this :

include_once('connect.inc.php');

try
{
    /***************** COMPANY DATA POST **************/

$entite_juridique = $_POST['entite_juridique'];
$enseigne_commerciale = $_POST['enseigne_commerciale'];
$raison_sociale = $_POST['raison_sociale'];
$adresse =  $_POST['street_number'] . " " . $_POST['route'];
$adresse2 = $_POST['adresse2'];
$cp = $_POST['cp'];// 75008 
$ville = $_POST['ville'];// Paris 
$country = $_POST['country'];// France 
$region = $_POST['region'];// Île-de-France 
$departement = $_POST['departement'];// Paris (75) 
$tel = $_POST['tel'];
$email = $_POST['email']; //aaaa@mail.com 
$website = $_POST['website'];
$categorie = $_POST['categorie'];
$facebook = $_POST['facebook'];
$twitter = $_POST['twitter'];
$google = $_POST['google'];
$siren = $_POST['siren'];
$lieu_immat = $_POST['lieu_immat'];
$capital = $_POST['capital'];
$description = $_POST['description'];
$status = $_POST['status'];

//$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO cc_a 
(entite_juridique, enseigne_commerciale, raison_sociale, adresse, adresse2, cp, ville,
 country, region, departement, tel , email, website, categorie, facebook, twitter, google,
 siren, lieu_immat, capital, description, status) 
VALUES (:entite_juridique, :enseigne_commerciale, :raison_sociale,:adresse, :adresse2,:cp,:ville, 
:country, :region,  :departement, :tel, :email, :website, :categorie, :facebook,:twitter, :google,
 :siren,:lieu_immat, :capital, :description, :status)";
    $sth = $dbh->prepare($sql);

    $sth->execute(array(':entite_juridique' => $entite_juridique,
                        ':enseigne_commerciale' => $enseigne_commerciale,
                        ':raison_sociale' => $raison_sociale,
                        ':adresse' => $adresse,
                        ':adresse2' => $adresse2,':cp' => $cp,
                        ':ville' => $ville,
                        ':country' => $country, 
                        ':region' => $region,
                        ':departement' => $departement,
                        ':tel' => $tel , 
                        ':email' => $email,
                        ':website' => $website,
                        ':categorie'=> $categorie,
                        ':facebook' => $facebook,
                        ':twitter' => $twitter,
                        ':google' => $google,
                        ':siren' => $siren,
                        ':lieu_immat' => $lieu_immat,
                        ':capital' => $capital,
                        ':description' => $description,
                        ':status' => $status ));

    $sth= $dbh->exec($sql);
    echo 'OK';
}
catch(Exception $e) //en cas d'erreur
{
    echo 'KO';
    echo 'Erreur : '.$e->getMessage().'<br />';
    echo 'N° : '.$e->getCode();
    exit();
}

The thing is the values are well inserted but I got the following error :

Erreur : SQLSTATE[42000]: Syntax error or access violation: 1064 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 ':entite_juridique, :enseigne_commerciale, :raison_sociale,:adresse, :adresse2,:c' at line 2 N° : 42000

I do not understand why an error is raised and the values are inserted. Note : when i remove from the config file this $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); no error is resaided.

Answer 1

$sth= $dbh->exec($sql);

You've already executed your prepared query with $sth->execute().

You are executing the query a second time without binding the parameters by running exec($sql).

The reason you don't get an error when you disable ERRMODE_EXCEPTION is because you disabled the exceptions, or PDO errors, you are "catching". Nowhere in your code do you look for the error message, your code is set up to catch the exception thrown by PDO on an error.

IF you disabled the ERRMODE_EXCEPTION and then executed something like this:

if (!$dbh->exec($sql)) {
  print_r($dbh->errorInfo());
}

Then you'd get the same error.