-2

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:\xampp\htdocs\mini_projet\class.php:107 Stack trace: #0 C:\xampp\htdocs\mini_projet\class.php(107): PDO->query('INSERT INTO pub...') #1 C:\xampp\htdocs\mini_projet\transformation.php(24): Connexion->query('INSERT INTO pub...') #2 {main} thrown in C:\xampp\htdocs\mini_projet\class.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 */;
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
Med
  • 3
  • 1
  • It seems you are using MySQL - in which case please remove the SQL Server tag. – Dale K Apr 20 '21 at 21:05
  • this code is **vulnerable** to **sql injection** so use only **prepared statements with parameters** see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – nbk Apr 20 '21 at 21:26
  • and for your problem $auteur. and . $categorie have to **exist** in the database **before** you insert your new row. – nbk Apr 20 '21 at 21:28

1 Answers1

0

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)
Amir Abouei
  • 126
  • 8
  • I solved the problem ```the problem lies in the definition of the integrity constraints of the foreign keys. I guess if we define NO ACTION in the ON DELETE or ON UPDATE clauses, it will have the same meaning as RESTRICT. I made SET NULL ``` – Med Apr 22 '21 at 10:08