INSERT INTO doesn't write anything in the database

Question

Basically, we are trying to add some values in a database. We are doing it using a GET command to get the value called "valeur" and writing this in the database. However it is not working, the values are not added to the database

<?php
try
{ // connection a la base de donnees
// connection to mySQL
$bdd = new

PDO('mysql:localhost;dbname=test1', 'root', '');

}

catch(Exception $e) //in case of error, display it and stop everything

{
die('Erreur : '.$e->getMessage()); 
}

if (isset($_GET['temp1'])) // test if the variable exists

{

$_GET['temp1'] = floatval($_GET['temp1']); 

echo ('donnee ' .$_GET["temp1"]. ' en cours d\'ecriture</br>');

$bdd->exec('INSERT INTO temp (valeur) VALUES('.$_GET["temp1"].')');

echo ('donnee ' .$_GET['temp1']. ' ecrite!');
}
?>

If we put a value in (in our case) http://localhost/test1/add.php?temp1=(thevalue) then it should be inserted into our table called temp in the column "valeur". Instead, it doesn't write anything.

Edit : We are using PHP version 5.6.19 and MySQL 5.7.11 and WAMPserver

EDIT2: I have finally resolved the problem, though I have no idea how. Php looks fun

Don't mash strings together to make SQL. Use bound arguments. — Quentin, Jan 18 '19 at 08:52
You need to look at the return value of `exec` and ask what errors came back from the database. Check the PDO manual for details. — Quentin, Jan 18 '19 at 08:53
`` — There is no `` tag in HTML. The `br` element has a *mandatory* **start** tag and a *forbidden* **end** tag. — Quentin, Jan 18 '19 at 08:53
Please check [How to squeeze error message out of PDO?](https://stackoverflow.com/questions/3726505/how-to-squeeze-error-message-out-of-pdo) and [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/11610605). — Álvaro González, Jan 18 '19 at 08:56
indeed what @ÁlvaroGonzález says `$_GET['temp1'] = floatval($_GET['temp1']); ` should not be used to protect against SQL injections.. `floatval()` works in this case to prevent it **but ideally it should not be used and you need to use prepared statements always** when working with a database even when you think the data is safe.. For example the data was fetched from the database and reinserted in a other table.. If you don't use a prepared statements then stored SQL injections (a.k.a second order SQL injections) are possible. — Raymond Nijland, Jan 18 '19 at 09:01

score 0 · Answer 1 · answered Jan 18 '19 at 09:03

You should assign a variable for the SQL query for debugging target.
And echo to print how is your query string. After that, you paste your $query in SQL tab at Phpmyadmin to know what is your error.

$query = "INSERT INTO temp (valeur) VALUES('.$_GET['temp1'].')";
echo $query;

score 0 · Answer 2 · answered Jan 18 '19 at 09:53

As you are using PDO it makes sense to utilise some of the strengths of it - primarily in this case prepared statements and bound parameters to make the sql much safer from malicious users. If you separate the database connection from the remaining code you have a database connection which can be used elsewhere quickly and easily simply by including it at runtime, so the first piece of code below could be the db connection file.

( I see you have solved the problem yourself just before posting this... )

<?php
    /*******************
        dbo-conn.php
    */
    try{

        $options=array( 
            PDO::ATTR_CURSOR                    =>  PDO::CURSOR_SCROLL,
            PDO::ATTR_PERSISTENT                =>  false,
            PDO::MYSQL_ATTR_USE_BUFFERED_QUERY  =>  true,
            PDO::ATTR_EMULATE_PREPARES          =>  true,
            PDO::MYSQL_ATTR_INIT_COMMAND        =>  'SET NAMES \'utf8mb4\' COLLATE \'utf8mb4_general_ci\', @@sql_mode = STRICT_ALL_TABLES, @@foreign_key_checks = 1'
        );
        $dbuser='root';
        $dbpwd='';

        $bdd=new PDO( 'mysql:host=localhost;dbname=test1;port=3306;charset=UTF8', $dbuser, $dbpwd, $options );


    }catch( PDOException $e ){
        exit( $e->getMessage() );
    }
?>


On the page that does the database inserts


<?php

    try{

        # test that the variable is set and available...
        if( !empty( $_GET['temp1'] ) ){

            # rudimentary check for number
            if( !is_numeric( $_GET['temp1'] ) )throw new Exception( sprintf( 'Supplied parameter "%s" does not appear to be a number', $_GET['temp1'] ) );

            $valeur = $_GET['temp1'];


            # include the db connection
            # the path used here depends where the file `dbo-conn.php` is saved
            # - this assumes the same directory 
            require 'dbo-conn.php';

            # generate sql & prepared statement
            $sql='insert into `temp` ( `valeur` ) values ( :valeur )';
            $stmt = $bdd->prepare( $sql );

            # check the prepared statement was created ok before attempting to execute it
            if( !$stmt ) throw new Exception( 'Failed to prepare sql "INSERT" query' 

            # bind the placeholder to the supplied user input
            $stmt->bindParam( ':valeur', $valeur, PDO::PARAM_STR );

            # commit the query
            $result = $stmt->execute();

            if( !$result )throw new Exception( 'oops! something went wrong' );

            # display a message to the user
            printf('donnee %s ecrite!', $valeur );
        }

    }catch( Exception $e ){
        exit( sprintf( 'Erreur: %s', $e->getMessage() ) );
    }

?>

INSERT INTO doesn't write anything in the database

2 Answers2