Ensure no missing column value is sent to INSERT statement

Question

I recently changed my website from shared hosting to a dedicated server (major upgrade..), and some things didn't work out of the box. One of these things was the INSERTs. I was used to code INSERTS without bothering about having all the columns values in my statement. The missing columns were automatically filled with 0s and threw basic warning I ignored.

I thought it was normal MySQL\MariaDB behaviour. With my new dedicated server (not hosted locally), the database sends an error and refuses to insert the line if all the columns values are not declared in the INSERT statement. This is a problem. I add columns, from time to time, in many tables, as I improve the site's functionalities. I don't want to be revising all the INSERTS in the thousands of lines in my PHP scripts every time I add a column to a table.

So, instead, I created this insert function. Basically:

Call the function, send an array with table name and key+values I want to insert
Function reads the table name, selects one line, and list the columns name
Function prepares an INSERT with the passed key+values, and generates bulk (depending on the types) values for the columns without values passed
Proceed to INSERT

Now the questions:

Is there a simpler way of doing this?
Am I breaching something?
Is it a recurrent need?

And here the function code:

// INSERTION DB GÉNÉRALE
function insert_db($valeurs)
{
    global $date;
    global $no;
    global $bdd;
    
    // Pour le log
    $log = 'Valeurs reçues BDD : ';
    foreach ($valeurs as $colonne => $valeur)
    {
        $log .= $colonne.' = '.$valeur.' || ';
    };
    
    // On obtient la table dans $valeurs['db_table']
    $resultat = $bdd->query('SELECT * FROM '.$valeurs['db_table'].' LIMIT 1');
    $tous_les_champs = array_keys($resultat->fetch(PDO::FETCH_ASSOC)); // On crée un tableau avec toutes les entêtes de colonnes
    
    $champs = array(); // Les champs qui seront mis au début de la requête
    $valeurs_preparees = array(); // Les valeurs préparées (avec un : devant) pour mettre dans la deuxième portion de la requête
    
    foreach ($tous_les_champs as $champ_unique) // On valide qu'on a des données pour chaque colonne
    {
        $valeur = "0";
        if (strpos($champ_unique, 'DATE') !== false) { $valeur = $date; }; // Si le titre du champs contiens « DATE », on prends en considération qu'il faut y mettre quelque chose en type datetime
        if (strpos($champ_unique, 'RAW_DN') !== false) { $valeur = "0000-00-00 00:00:00"; }; // Pour le champs custom de date de naissance de USAGERS
        if (strpos($champ_unique, 'DATE_DEPART') !== false) { $valeur = "0000-00-00 00:00:00"; }; // Si le titre du champs contiens « DATE », on prends en considération qu'il faut y mettre quelque chose en type datetime
        if (strpos($champ_unique, 'DESCRIPTION') !== false) { $valeur = "Aucune description"; };
        if (strpos($champ_unique, 'COMMENTAIRE') !== false) { $valeur = "Aucun commentaire"; };
        if (strpos($champ_unique, 'CREE_PAR') !== false) { $valeur = $_SESSION['DOCTUS'][$no]['ID']; };
        if (strpos($champ_unique, 'MODIF_PAR') !== false) { $valeur = $_SESSION['DOCTUS'][$no]['ID']; };
        if (strpos($champ_unique, 'CREATEUR') !== false) { $valeur = $_SESSION['DOCTUS'][$no]['ID']; };
        if (strpos($champ_unique, 'MODIFICATEUR') !== false) { $valeur = $_SESSION['DOCTUS'][$no]['ID']; };
        if (strpos($champ_unique, 'PAYE_') !== false) { $valeur = "1"; };
        
        if (!isset($valeurs[$champ_unique]) || $valeurs[$champ_unique] == "") { $tableau[$champ_unique] = $valeur; }else{ $tableau[$champ_unique] = $valeurs[$champ_unique]; }; $champs[$champ_unique] = $champ_unique; $valeurs_preparees[$champ_unique] = ":".$champ_unique;
    };
    
    // On crée les champs pour la requête
    $requete_champs = "";
    foreach ($champs as $champ_unique)
    {
        if ($requete_champs != "") { $requete_champs .= ', '; };
        $requete_champs .= $champ_unique;
    };
    
    // On crée les valeurs pour la requête
    $requete_valeurs = "";
    foreach ($valeurs_preparees as $valeur_unique)
    {
        if ($requete_valeurs != "") { $requete_valeurs .= ', '; };
        $requete_valeurs .= $valeur_unique;
    };
    
    $req_insert_db = $bdd->prepare('INSERT INTO '.$valeurs['db_table'].'('.$requete_champs.') VALUES('.$requete_valeurs.')');
    $req_insert_db->execute($tableau);
    $id_insert = $bdd->lastInsertId();
    
    // On log l'insertion
    $log .= "\n    ID inséré : ".$id_insert;
    if ($id_insert == 0) { $log .= '  || ERREUR'; };
    $fichier = './log/insertions_log.txt';
    file_put_contents($fichier, $log, FILE_APPEND);
    
    return $id_insert;
};

score 2 · Accepted Answer · answered Aug 19 '20 at 17:31

The cause is a different configuration of MySql/MariaDb server between old and new server (see the documentation of Strict SQL Mode for more details).

The cleanest way is to do an ALTER TABLE of all your tables and give to any fields a DEFAULT value (at least to all fields that you don't want to specify a value): in this case the engine don't need all fields in INSERT statements. (see this answer to see the syntax)

Ensure no missing column value is sent to INSERT statement

1 Answers1