Fatal error: Uncaught PDOException: SQLSTATE[23000…

during the realization of a project I encountered this problem can you help me please Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (bdd_sql.publications, CONSTRAINT publications_ibfk_1 FOREIGN KEY (auteur) REFERENCES utilisateurs (id) ON DELETE CASCADE ON UPDATE CASCADE) in C:xampphtdocsmini_projetclass.php:107 Stack trace: #0 C:xampphtdocsmini_projetclass.php(107): PDO->query(‘INSERT INTO pub…’) #1 C:xampphtdocsmini_projettransformation.php(24): Connexion->query(‘INSERT INTO pub…’) #2 {main} thrown in C:xampphtdocsmini_projetclass.php on line 107

enter image description here

code php

<?php
session_start();
require 'class.php';
    
    $id = $_GET['id'] ?? "";
    $contenu = $_GET['contenu'] ?? ""; 
    $auteur = $_GET['auteur'] ?? "";
    $categorie = $_GET['categorie'] ?? "";


    $A = $pdo->query("SELECT id From publications where id= ' " .$id. "' " );
    $A->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, 'Publication');
    $B = $A->fetch();

    if ($B == TRUE) {

        $A = $pdo->query("UPDATE publications  SET contenu = '".$contenu."', auteur = '".$auteur."', categorie_id = '".$categorie."' WHERE id = '".$id."' ");

 
        
    }
    else 
    {
        $A = $pdo->query("INSERT INTO publications VALUES ('".$id."','".$contenu."','".$auteur. "', '". $categorie ."')");    

        
    }
           header('Location: publication.php');

?>

database

--
CREATE DATABASE `BDD_SQL` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `BDD_SQL`;

-- --------------------------------------------------------

--
-- Structure de la table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `categorie` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Contenu de la table `categories`
--

-- --------------------------------------------------------

--
-- Structure de la table `publications`
--

CREATE TABLE IF NOT EXISTS `publications` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `contenu` varchar(250) DEFAULT NULL,
  `auteur` int(20) NOT NULL,
  `categorie` int(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `auteur` (`auteur`),
  KEY `categorie` (`categorie`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=31 ;

--
-- Contenu de la table `publications`
--


-- --------------------------------------------------------

--
-- Structure de la table `utilisateurs`
--

CREATE TABLE IF NOT EXISTS `utilisateurs` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `pseudo` varchar(45) DEFAULT NULL,
  `naissance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
-------------------------------------

--
-- Structure de la table `votes`
--

CREATE TABLE IF NOT EXISTS `votes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `utilisateur` int(11) DEFAULT NULL,
  `publication` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `utilisateur` (`utilisateur`),
  KEY `publication` (`publication`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=38 ;

--

--
-- Contraintes pour les tables exportÃ©es
--

--
-- Contraintes pour la table `publications`
--
ALTER TABLE `publications`
  ADD CONSTRAINT `publications_ibfk_1` FOREIGN KEY (`auteur`) REFERENCES `utilisateurs` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `publications_ibfk_2` FOREIGN KEY (`categorie`) REFERENCES `categories` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Contraintes pour la table `votes`
--
ALTER TABLE `votes`
  ADD CONSTRAINT `votes_ibfk_1` FOREIGN KEY (`utilisateur`) REFERENCES `utilisateurs` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `votes_ibfk_2` FOREIGN KEY (`publication`) REFERENCES `publications` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Answer

To prevent SQL injection, it is recommended that you never use variables directly in your connections, and you can use it as a key value to be safe from some attacks. like this:

$id = 10;
$sql = "SELECT * FROM tests WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute(':id'=>$id)

Advertisement

Answer