PHP form won't insert into MYSQL

Question

There are many such errors but this one is mine.

I have a PHP ticketing type form that I want to insert information into a MYSQL database.

Here is the PHP form:

<html lang="en" dir="ltr">
  <head>
    <meta charset="utf-8">
    <title></title>
  </head>
  <body>
    <form name="ticket-form" id="ticket-form" action="get_response.php" method="POST">
      <p>Please provide your first name: <input type="text" name="firstName" id="firstName" placeholder="John" required></p>
      <p>Please provide your last name: <input type="text" name ="lastName" id="lastName" placeholder="Smith" required></p>
      <p>Please indicate if you are a company or a contractor:
        <input type="radio" name="clientType" id="clientType" value="company">Company
        <input type="radio" name="clientType" id="clientType" value="contractor" checked>Contractor</p>
      <p>Please provide an email address: <input type="email" name="email" id="email" placeholder="John123@example.com"><br></p>
      <p>Please provide a phone number if you'd prefer: <input type="text" name="number" id="number" max="13" placeholder="07654321234"><br></p>
      <p>Please detail your query, going in to as much detail as possible: </p>
      <textarea name="query" id="query" rows="8" cols="80" placeholder="Please detail the nature of your issue, going in to as much detail as possible."></textarea><br><br>
      <input type="submit" name="submit" value="Submit">
    </form>
  </body>
</html>

And here is the get_response.php

<?php
require_once("config.php");
require_once("index.php");
$datetime = new DateTime();
$datestring = $datetime->format('d-m-Y H:i:s');
$first_name = mysqli_real_escape_string($conn, $_POST['firstName']);
  $second_name = mysqli_real_escape_string($conn, $_POST['lastName']);
  $client_type = mysqli_real_escape_string($conn, $_POST['clientType']);
  $email_address = mysqli_real_escape_string($conn, $_POST['email']);
  $query = mysqli_real_escape_string($conn, $_POST['query']);
  $phone_number = mysqli_real_escape_string($conn, $_POST['number']);
  $sql = "INSERT INTO
    tickets (first_name, second_name, client_type, email_address, phone_number, query)
    VALUES
    ('$first_name', '$second_name', '$client_type', '$email_address', '$phone_number', '$query')";
    if($conn->query($sql)){
      echo "<p>Thank you for your email!</p>
      <p> We aim to respond to your query as soon as possible.
      Please allow 2 business days for a response and check spam folders.</p>";
    }
    else
    {
        die('There was an error running the query [' . mysqli_error($conn) . ']');
      }
  // else
  // {
  //   echo "<p>Please supply valid information.</p>";
  // }
mysqli_close($conn);
 ?>

php config.php

Will indicate a successful connection with the $conn variable, but to eliminate the issue:

<?php
$servername = "xxxxx";
$username = "xxxxx";
$password = "xxxxx";
$dbname = "tickets";
$conn = mysqli_connect($servername, $username, $password, $dbname);
if($conn->connect_error){
  die("Connection failed: " . $conn->connect_error);
} ?>

php get_response.php

Will insert empty data into the database. Yet the form does not work by posting the information.

I know there might be other bugs. I'm very new to PHP and MYSQL. Any help is much appreciated. Thanks in advance.

Edit: Some error logs as requested.That's all there is from today.

[Fri Nov 01 12:24:51.342604 2019] [mpm_event:notice] [pid 673:tid 140589056359360] AH00489: Apache/2.4.38 (Ubuntu) configured -- resuming normal operations
[Fri Nov 01 12:24:51.343298 2019] [core:notice] [pid 673:tid 140589056359360] AH00094: Command line: '/usr/sbin/apache2'

Edit2 : Solved, sorta.

I tried remaking the form in my regular Mac OS X operating system and it works fine. There seems to have been something fundamentally wrong with either Virtual box, how it communicates with modern OS X software or some personal configuration. Although I can connect to mysql in the Linux command line it won't work through the form.

Your script is at risk for [SQL Injection Attacks](http://stackoverflow.com/questions/60174/). Use [prepared statements](https://bobby-tables.com/php). Even [escaping](http://stackoverflow.com/questions/5741187/) the string is not safe! — Jason K, Nov 01 '19 at 15:00
So it is inserting, but inserting empty values? Please turn on error reporting and then report back with the errors - https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display and also https://phpdelusions.net/mysqli/error_reporting — waterloomatt, Nov 01 '19 at 15:05
Hi, it's not inserting anything from form only from ```php get_response.php``` on command line. which makes me think it's to do with how the form is connected. Roger on the SQL vulnerability, SQL injections still a topic I'm learning about, that will all be rectified before I ever made it live. I'll edit with error logs. — Shaun, Nov 01 '19 at 15:12
I think you're mixing the OO style of connecting with the procedural style. Can you please alter config.php's if statement to: `if (!$conn) { die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error()); }`. See `Procedural Style` section in https://www.php.net/manual/en/mysqli.construct.php#refsect1-mysqli.construct-examples. Also, turn on error reporting. This will show you where you're going wrong very quickly. — waterloomatt, Nov 01 '19 at 15:16
Hi @waterloomatt, I have done so but the connection isn't the problem. Config works so there is a $conn, it just doesn't post. — Shaun, Nov 01 '19 at 15:29
You need to help us out by doing some debugging. Can you please show us the output of `var_dump($_POST)`, `var_dump($first_name)`, and `var_dump($sql)`? — waterloomatt, Nov 01 '19 at 15:45
Hi, again none are updating with manual form submission which makes me think it isn't being executed and is to do with how I've configured it. On executing get_response.php from command line, they update as would be expected. — Shaun, Nov 01 '19 at 16:50
OK - so if I understand you correctly, your form isn't even posting to your script. If your form action is `action="get_response.php"` then it is posting to http://localhost/get_response.php, right? Is that the correct path or is that file buried in a sub directory? — waterloomatt, Nov 01 '19 at 17:18
Thanks, yes it is configured to the right path and is all the same level — Shaun, Nov 03 '19 at 16:25

Professor Abronsius · Answer 1 · 2019-11-01T16:59:24.047

I had a spare 20mins whilst installing MS SQL Server so quickly refactored your code to make use of prepared statements to mitigate SQL injection attacks. It is not tested so there might be minor errors which I overlooked but I hope it'll prove useful

<?php

    $status=false;

    if( $_SERVER['REQUEST_METHOD']=='POST' ){

        $errors=array();
        $args=array(
            'firstName'     =>  FILTER_SANITIZE_STRING,
            'lastName'      =>  FILTER_SANITIZE_STRING,
            'clientType'    =>  FILTER_SANITIZE_STRING,
            'email'         =>  FILTER_SANITIZE_STRING,
            'query'         =>  FILTER_SANITIZE_STRING,
            'number'        =>  FILTER_SANITIZE_STRING
        );

        foreach( array_keys( $args ) as $field ){
            if( !isset( $_POST[ $field ] ) ) $errors[]=sprintf( 'The field "%s" is not set', $field );
        }

        foreach( $_POST as $field => $value ){
            if( !in_array( $field, array_keys( $args ) ) )$errors[]=sprintf( 'Unknown field "%s"', $field );
        }

        if( empty( $errors ) ){
            $_POST=filter_input_array( INPUT_POST, $args );
            extract( $_POST );


            require_once 'config.php';
            require_once 'index.php';



            $sql='insert into `tickets` 
                ( `first_name`, `second_name`, `client_type`, `email_address`, `phone_number`, `query` ) 
                values 
                ( ?, ?, ?, ?, ?, ? )';
            $stmt=$conn->prepare( $sql );
            if( !$stmt )$errors[]='The SQL Prepared Statement failed';

            $stmt->bind_param('ssssss', $firstName, $lastName, $clientType, $email, $query, $number );
            $stmt->execute();

            $status=$conn->affected_rows;

        }
    }
?>
<html lang='en' dir='ltr'>
    <head>
        <meta charset='utf-8'>
        <title></title>
    </head>
    <body>
        <form name='ticket-form' method='POST'>
            <?php

                if( $_SERVER['REQUEST_METHOD']=='POST' ){
                    if( !empty( $errors ) ){

                        printf( '<pre>%s</pre>',print_r($errors,true) );

                    } else {

                        if( $status ){

                            echo "
                            <p>Thank you for your email!</p>
                            <p> We aim to respond to your query as soon as possible.
                            Please allow 2 business days for a response and check spam folders.</p>";
                        }
                    }
                }

            ?>
            <label>Please provide your first name: <input type='text' name='firstName' placeholder='John' required></label>
            <label>Please provide your last name: <input type='text' name ='lastName' placeholder='Smith' required></label>

            <label>
                Please indicate if you are a company or a contractor:
                <input type='radio' name='clientType' value='company'>Company
                <input type='radio' name='clientType' value='contractor' checked>Contractor
            </label>

            <label>Please provide an email address: <input type='email' name='email' id='email' placeholder='John123@example.com'></label>
            <label>Please provide a phone number if you'd prefer: <input type='text' name='number' id='number' max='13' placeholder='07654321234'></label>

            <label>
                Please detail your query, going in to as much detail as possible:
                <textarea name='query' rows='8' cols='80' placeholder='Please detail the nature of your issue, going in to as much detail as possible.'></textarea>
            </label>

            <input type='submit' />
        </form>
    </body>
</html>

Thank you, appreciated. Please note, my solicited issue remains unresolved. — Shaun, Nov 01 '19 at 15:40
The form itself POSTs fine so I think your issue lies elsewhere — Professor Abronsius, Nov 01 '19 at 15:58
Hi, quite possibly, but connection is fine and only form doesn't work, sorry. — Shaun, Nov 01 '19 at 16:50
at the top of `get_response.php` put `exit( print_r( $_POST,1) );` or similar to begin debugging. Enable error reporting too — Professor Abronsius, Nov 01 '19 at 16:55
one thing you might need to check is that the required files are actually being included correctly. - `var_dump( get_included_files() );` — Professor Abronsius, Nov 01 '19 at 17:01

PHP form won't insert into MYSQL

1 Answers1