How do I send user input from a HTML textform to my SQL database?

Question

We started working for the first time with PHP and my SQL.

There are 2 pages the user can go to:

The first page that opens had a form with 3 input fields: First name, Last name, your favorite color (hexcode color picker)
Once you filled in the info, you get send to the 2nd page where the text says "Welcome [first name] [Last name]. Nice to see you. Looks like you also like [color] huh?" and the color of the background changes to the color you have chosen.

I have no issues POST-ing it, but I need to send this info to the SQL database and I cannot seem to figure out what to do next.

index.php

<?php 


// server settings
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "bezoeker";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} else {
    echo 'connection succesfull';
}

$name = $_POST['name'];
$lastName = $_POST['lastname'];
$favColor = $_POST['favcolor'];

//add to database
$sql = "INSERT INTO formulier (naam, achternaam, kleur) VALUES ('$name', '$lastName', $favColor)";


//database addition confirmation
if(mysqli_query($conn, $sql)){
    echo "Records inserted successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);
}


// Close connection
$conn->close();



    include 'template-parts/header.php'; 


    include 'template-parts/main.php'; 


    include 'template-parts/footer.php'; 
?>

Main.php

<main>

<center>
  <form action="welcome.php" method="post">
     Naam: <input type="text" name="name"><br>
     Achternaam: <input type="text" name="lastname"><br>
     Je favoriete kleur: <input type="color" name="favcolor" value="#ff0000">
     <input type="submit">
     </form>
 </center> 


 </main>

Welcome.php

<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <link href="css/reset.css" rel="stylesheet">
    <link href="css/style.css" rel="stylesheet">
    <title>PHP</title>
</head>

<body>

    <div class= "welcome-message">
        <h1>Welcome <?php echo $_POST["name"]; ?> <?php echo $_POST["lastname"]; ?>  !<br> </h1>
        <h2>Leuk dat je er bent!</h2>

        <h3>Wouw, mijn favoriete kleur is ook <?php echo $_POST["favcolor"]; ?> !</h3> 

        <?php echo '<body style="background-color: ' . $_POST['favcolor'] . '">'; ?>
    </div>



</body>
</html>

My database Bezoeker has table called Formulier with the structure : naam , achternaam , kleur

With the current code I get the error

connection succesfullERROR: Could not able to execute INSERT INTO formulier (naam, achternaam, kleur) VALUES ('', '', ). 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 ')' at line 1

and I cannot figure out what it means or what to do next.

`VALUES ('', '', )` is the syntax problem, specifically the `, )` - because you've built and executed the query without checking whether you had any values to send to it. And it's unclear how you would send any values to it anyway, because your form posts back to "welcome.php", but the code to do the database insert is in index.php... And please tell us, how is index.php being executed? Because it doesn't seem to be happening as a result of submitting your form, and therefore there is no data for it to collect. The structure of your application doesn't make sense, from what you've shown. — ADyson, Nov 19 '19 at 14:59
Also, when you do get it working, you're vulnerable to [SQL injection attacks](https://bobby-tables.com/). Make sure you know how to use parameterised queries and prepared statements to bind your input data to the query securely, without leaving you vulnerable to malicious input (or silly syntax errors!). See [here](https://bobby-tables.com/php) for a simple example — ADyson, Nov 19 '19 at 15:00
Thanks for the comment, I have been looking all over, but im just totally un sure on how to fix this/go any further with it. Especially with my lack of knowledge in PHP at the moment I am just a little. Could mind to help me into the right direction? and thanks for the concern. Please know that what I am creating right here is just for a small project that gets discarded/stored away once it has been marked. But I appreciate the security check up — , Nov 19 '19 at 15:05
Check out that link that @Adyson recommends at the end of their comment. Switch to binding your parameters as in that example and not only will your very big sql injection issue go away, but your code will also start working since the methods we use to bind parameters to a sql statement will also know to stick `NULL` in instead of nothing in your list of values. — JNevill, Nov 19 '19 at 15:08
do something like this `if($_POST["name"]) { //put all your code here } ` when you are able to read the post data you can move forward step by step. — Afia, Nov 19 '19 at 15:08
@ADyson could you probably elaborate on what you mean with "How is index.php being executed"? — , Nov 19 '19 at 15:48
Does this answer your question? [How to include a PHP variable inside a MySQL statement](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) — Dharman, Nov 19 '19 at 15:53

Alex · Accepted Answer · 2019-11-19T20:38:34.950

Your <form action="welcome.php" method="post"> tells the form what URL to go to next and process. All your code for inserting into the DB is on index.php, not welcome.php. If you browse directly to index.php all of the $_POST fields will be empty because a form has not POSTed to it. You need to move your php code to welcome.php

welcome.php

<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <link href="css/reset.css" rel="stylesheet">
    <link href="css/style.css" rel="stylesheet">
    <title>PHP</title>
</head>

<body>

    <div class= "welcome-message">

    <?php 
        //check the form was filled out correctly and you have the expected values
        if (isset ($_POST['name']) && ($_POST['lastname']) && ($_POST['favcolor'])) 
        {
            //....
            //Your PHP code to insert into DB
            //....
     ?>

    <h1>Welcome <?php echo $_POST["name"]; ?> <?php echo $_POST["lastname"]; ?>  !<br> </h1>
    <h2>Leuk dat je er bent!</h2>

    <h3>Wouw, mijn favoriete kleur is ook <?php echo $_POST["favcolor"]; ?> !</h3> 

    <?php echo '<body style="background-color: ' . $_POST['favcolor'] . '">'; ?>

    <?php
        }
     else
        { ?>

    <h1>Form was not filled out properly</h1>

  <?php } ?>
    </div>  

</body>
</html>

Also, please do take note about the SQL Injection vulnerabilities that several others have mentioned. Using values directly from form inputs to a SQL query is dangerous. The proper way to do this is with Prepared Statements.

From all the things I had tried I guess I totally didn't think about this solution.. it works, thank you. And also thanks for the explanation. The thing about the SQL injections: I am not really thinking about continueing with PHP after i finished my class (a manditory class). I appreciate the warnings, and didnt mean to ignore a few on it, but just cause its a "one and done" project my focus wasnt really put on that. — , Nov 19 '19 at 20:08
@vorkot1 That's fair enough. These paradigms and vulnerabilities are not exclusive to PHP though. This is how all server-side languages for a website work. Be it PHP, C#, Node, etc. It works with HTML protocols to process commands before sending the HTML response back to the user (or client). Any server-side script will need to sanitize inputs from the user before using them. — Alex, Nov 19 '19 at 20:58

score -1 · Answer 2 · answered Nov 19 '19 at 15:20

-1

<?php 
    if (isset ($_POST['name']) && ($_POST['lastname'])) {

    $name = $_POST['name'];
    $lastName = $_POST['lastname'];
    $favColor = $_POST['favcolor'];

    // server settings
        $servername = "localhost";
        $username = "root";
        $password = "root";
        $dbname = "bezoeker";

        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbname);

        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        } else {
            echo 'connection succesfull';
        }

        //add to database
        $sql = "INSERT INTO formulier (naam, achternaam, kleur) VALUES ($name, $lastName, $favColor)";

        //database addition confirmation
        if(mysqli_query($conn, $sql)){
            echo "Records inserted successfully.";
        } else{
            echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);
        }

        // Close connection
        $conn->close();
    }

        include 'template-parts/header.php';     
        include 'template-parts/main.php'; 
        include 'template-parts/footer.php'; 
    ?>

answered Nov 19 '19 at 15:20

Afia

683
5
17

Your code is vulnerable to SQL injection. You should use prepared statements. This code example is full of bad programming practices. – Dharman Nov 19 '19 at 15:52
@Dharman I can get it's a bad practise (as mentioned by others), but it is just a learning excercise that won't be put up to the public. My main issue so far is just how I can get the stuff into the database + go to the 2nd page. Still a hard time figuring it out. – Nov 19 '19 at 15:57
1

@vorkot1 Why would you want to learn that. Don't learn stuff that the community actively discourages people from learning. This makes no sense. – Dharman Nov 19 '19 at 16:00
@Dharman I only have to learn/use PHP for the current course I am in (not by choice from myself) to pass the class. I will personaly not use it myself in the future. Probably should've mentioned it. I will try however to add it correctly – Nov 19 '19 at 16:02
1

@Dharman that is his original code, the intention was for him to understand how to first get the data and then look for ways to improve. The only thing I added was the if condition. – Afia Nov 19 '19 at 16:03
@AfiaUdofia I have added the if-statement and the code to protect from injections (hoping its correct). I just have a very very hard time to figure out how I can post the stuff from the Forms (going for the blind jab that it in fact is not correct) and still hop over to the 2nd page that shows the information displayed on screen. Any other hint/tip you can give? I removed the SQL-connection check out of the IF-statement, and it says its connected no issue. So thats a good thing at least. – Nov 19 '19 at 16:27
@vorkot1 it will be best you comment out your includes and use the information on this page [PHP Form Handling](https://www.w3schools.com/php/php_forms.asp) to first get a simple post data to the server. then work on integrating it with your code. – Afia Nov 19 '19 at 17:11

How do I send user input from a HTML textform to my SQL database?

2 Answers2