INSERT statement not inserting some values

Question

I am trying to make a quiz using PHP and HTML. Some of the selected options are inserted into the results table and others are not. The PHP code is below:

<?php
error_reporting(E_ALL ^ E_NOTICE);
$conn = mysqli_connect("localhost", "student", "student") or die(mysqli_error());
mysqli_select_db($conn,'game') or die(mysqli_error());  
if (isset ($_POST['submit']))

$Q2 = $_POST['Q2'];
$Q3 = $_POST['Q3'];
$Q4 = $_POST['Q4'];
$Q5 = $_POST['Q5'];
$Q6 = $_POST['Q6'];

echo "<h2>Data has been inserted</h2>";
$query = "INSERT into results values('$Q2','$Q3','$Q4','$Q5','$Q6')";
$result = mysqli_query($conn, $query) or die(mysqli_error($conn));
?>

And here is the HTML code:

<p>Question 1 : When was the PS3 first released? </p>
<p align="left">
  <input type="radio" name="Q2" value="2005">
  2005
  <br>
  <input type="radio" name="Q2" value="2006">
  2006
</p>

The problem I am having is that everything is being inserted into the table apart from $Q2. Why is this happening?

Your code is prone to SQL injection. You should fix it first. — Michas, Apr 27 '15 at 15:51
You need curly braces to define the `if` code block, otherwise it only affects the one line of code after it. If $Q2 is never inserted make sure your submit button has a name attribute set to "submit". — John McMahon, Apr 27 '15 at 15:53

Zorgarath · Answer 1 · 2015-04-27T18:43:21.560

3

Your if statement is only operating on the line immediately following it because its missing its braces.

You want it to be this:

    if (isset ($_POST['submit']))
    {
      $Q2 = $_POST['Q2'];
      $Q3 = $_POST['Q3'];
      $Q4 = $_POST['Q4'];
      $Q5 = $_POST['Q5'];
      $Q6 = $_POST['Q6'];

      echo "<h2>Data has been inserted</h2>";
      $query = "INSERT into results values('$Q2','$Q3','$Q4','$Q5','$Q6')";
      $result = mysqli_query($conn, $query) or die(mysqli_error($conn));
    }

and your HTML following:

<form action="your-pagename" method="POST">
    <p>Question 1 : When was the PS3 first released? </p>
      <p align="left">
        <input type="radio" name="Q2" value="2005">
        2005
      <br>
      <input type="radio" name="Q2" value="2006">
        2006</p>

    <br />
    <input type="submit" value="Submit Test" />

</form>

But this is vulnerable to SQL injection! Please sanitize and either bind or prepare your statement before executing it.

edited Apr 27 '15 at 18:43

answered Apr 27 '15 at 15:51

Zorgarath

681
8
21

Hi there, I tried doing that but nothing happens then at all :( – Ryan Apr 27 '15 at 15:59
This is the gist of the answer. As for your current problem, it sounds like `$_POST['submit']` is not being properly passed. The conditional was only being run on $Q2 before. Now it's being run on the entire set of variables. – David Wyly Apr 27 '15 at 16:03
@Ryan You have to submit POST data in order for it to insert into your database. Is what you posted in your question the full HTML? Do you know how to make a form and submit POST data? – Zorgarath Apr 27 '15 at 16:03
@Ryan I've added some sample HTML in my answer to help with submitting your data. – Zorgarath Apr 27 '15 at 16:09
I kind of still don't understand. I don't know what the difference between Q2 and Q3 is. It looks the exact same, yet Q3 works and 2 doesn't? Very confused at this all. – Ryan Apr 27 '15 at 16:28
The IF statement only worked on Q2 and none of the rest. This is because you are missing the braces. Remove the IF statement altogether and it will insert all the time. – Zorgarath Apr 27 '15 at 16:44
Sorry, I just have to downvote answers with SQL injection vulnerabilities in them. – Chris Wesseling Apr 27 '15 at 17:42
@ChrisWesseling That was code from his question, but I went ahead and added a warning – Zorgarath Apr 27 '15 at 18:42
The fact that you copied the code from elsewhere doesn't make it correct. :-) – Chris Wesseling May 01 '15 at 14:09

hakiko · Answer 2 · 2015-04-27T16:15:11.873

Any mysql insert query works like below (see)

Also from doc

If both the column list and the VALUES list are empty, INSERT creates a row with each column set to its default value: INSERT INTO tbl_name () VALUES(); In strict mode, an error occurs if any column doesn't have a default value. Otherwise, MySQL uses the implicit default value for any column that does not have an explicitly defined default.

OR

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

so you need to COLUMN NAMES to work properly

The format he used will also work well for him.. still 1+ for your post.. :) — Choxx, Apr 27 '15 at 16:12
No, you don't... He uses the first form and, though adding the column names is good practice, they are in [] brackets and hence optional. This answer does not address the problem the OP has. And if you wish to focus on the SQL, please don't let the injection vulnerability unmentioned. — Chris Wesseling, Apr 27 '15 at 17:29

score 0 · Answer 3 · edited May 23 '17 at 12:04

First things first!

As some have mentioned already mentioned, you have a sql injection vulnerability in your code. Instead of fixing it in your code, I think it's more helpful to read: How can I prevent SQL injection in PHP? I could fix this code for you, but since it's such a common error (#1 in the OWASP top 10) it would do your future code much good if you understood the problem.

Your question

The problem im having is that everything is being inserted into the db table apart from '$Q2' which is so strange, and i cant seem to find the problem. The HTML is below;

I think isset($_POST['submit']) is false. And every $Q... is assigned a value from $_POST apart from the $Q2, because its assignment is conditional. It only happens if (isset($_POST['submit']))

How `if` works

As advertised in PHP only the single one statement directly following the if is conditional.

if ( isset($_POST['submit']) )
$Q2 = $_POST['Q2']; #this statement is conditional
$Q3 = $_POST['Q3']; #this one isn't
$Q4 = $_POST['Q4'];
$Q5 = $_POST['Q5'];
$Q6 = $_POST['Q6'];

#if there is no submit in $_POST, $Q2 is still uninitialized
#insert using $Q2, $Q3, $Q3 etc.

You can group several statements into one with curly braces {}. You probably intend to only do the the whole insertion block conditionally:

if ( isset($_POST['submit']) ) { # { starts the conditional statement group
    $Q2 = $_POST['Q2'];
    $Q3 = $_POST['Q3'];
    $Q4 = $_POST['Q4'];
    $Q5 = $_POST['Q5'];
    $Q6 = $_POST['Q6']; 
    #insert using $Q2, $Q3, $Q3 etc.
} # } ends the statement group

Note that I used indentation to clearly convey the block of the if-statement. Some nicely designed (as opposed to organically grown) languages use indentation as part of the syntax of grouping statements.

Your HTML

The reason why $_POST['submit'] is not set, is probably, because it isn't given a value in your form. You could try setting the name-attribute on the <input> of your submission button.

<input type="submit" name="submit" />

Making PHP more helpful

PHP could have helped you point to the error in your code. You used the $Q2 variable in the insert code uninitialized. This should be an error and, to paraphrase the Zen of Python:

Errors should never pass silently.
Unless explicitly silenced.

Especially during development you should set your error_reporting to something strict, like E_ALL.