PHP Form Submission - Syntax Errors

Question

I have an HTML form that is fairly simple:

HTML:

<form method="POST" id="form-1" name="form-1">
   <p>     
      <input type="text" name="fm1q1">
      <input type="number" name="fm1q1-score">
   </p>
   <p>     
      <input type="text" name="fm1q2">
      <input type="number" name="fm1q2-score">
   </p>
   <p>     
      <input type="text" name="fm1q3">
      <input type="number" name="fm1q3-score">
   </p>
   <p>     
      <input type="text" name="fm1q4">
      <input type="number" name="fm1q4-score">
   </p>
   <p>     
      <input type="text" name="fm1q5">
      <input type="number" name="fm1q5-score">
   </p>
   <button type="submit" name="submit">SUBMIT</button>
</form>

I'm using a simple Ajax call:

$('#form-1').on('submit', function(e){
    e.preventDefault();
    $.ajax({
        type:   'POST',
        url:    'submitForm.php',
        data:   $(this).serialize(),
        success: function(data){
            console.log(data);
        },
        error: function(xhr, ajaxOptions, thownError){
            console.log(xhr.status);
            console.log(thrownError);
        }
    });
});

The PHP that inserts the form data into a MySQL DB Table is like this:

require "config.php"; // Contains all my connection information 
$answers = array($_POST['fm1q1'], $_POST['fm1q2'], $_POST['fm1q3'], $_POST['fm1q4'], $_POST['fm1q5']);
$scores = array($_POST['fm1q1-score'], $_POST['fm1q2-score'], $_POST['fm1q3-score'], $_POST['fm1q4-score'], $_POST['fm1q5-score']);

for ($i = 0; $i < 5; $i++) { 
   $sql = "INSERT INTO table_1 (answer, score) VALUES ('$answers[$i]', '$scores[$i]')";
   $result = mysqli_query($conn, $sql);
   if (!$conn->query($result) === TRUE) {
      echo "Error: " . $sql . "--" . $conn->error. "\n";
   }
 }

$conn->close();

The problem I'm running into is that my Developer Tools say I have a Syntax error in the $sql= line, but I can't see what's wrong.

Error: INSERT INTO table_1 (answer, score) VALUES ('test', '123')--You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1' at line 1

not the error but, dont run the query in a loop, you can insert more than one row in a single query. — , Feb 04 '19 at 21:58
This error sometimes displaying when forget the AUTO_INCREMENT. It can be ? — Omer Tekbiyik, Feb 04 '19 at 21:59

score 4 · Accepted Answer · answered Feb 04 '19 at 22:06

You're trying to execute the query twice. Once here:

mysqli_query($conn, $sql)

and once here:

$conn->query($result)

And furthermore, in the second attempt you aren't executing the query but rather trying to execute the results of the query. I'm not sure why it's failing with that exact error message, but I'd certainly expect it to fail somehow.

What has you confused is that you're outputting your first query after having checked if your second query has failed. So you're misleading yourself in your debugging.

Just remove that second query attempt. You already have the results from the first one:

$result = mysqli_query($conn, $sql);
if ($result !== TRUE) {
    echo "Error: " . $sql . "--" . mysqli_error($conn) . "\n";
}

You should definitely make the choice of whether to use the function notation or the object notation with mysqli, and stay consistent with your choice. Trying to mix the two might work in some cases but it's ultimately going to cause confusion like this.

Also, and this is important... Your code is wide open to SQL injection. PHP provides considerable information on what that means here. And this is a great starting point for correcting it. Regardless of how you approach it, the bottom line is that you should never put user-modifiable data directly into a query as though it's part of the code. This allows user to put actual code in your query.

Would this be giving me the syntax error? The duplication of the actual query? Also, this code will only be used locally - I am not concerned with SQL Injection — Murphy1976, Feb 05 '19 at 13:43
@Murphy1976: If you try to execute the same query again, assuming it was successful the first time, it shouldn't give an error the second time. But if you try to execute *the results* of the query then all bets are off, those results could be anything. Judging from the error message it sounds like the results are simply: `1`. Which is probably the number of rows affected by the first query. — David, Feb 05 '19 at 13:45
@Murphy1976: *"I am not concerned with SQL Injection"* - You should be. It's a common source of bugs in code. Writing buggy code will just bring you back to Stack Overflow again. — David, Feb 05 '19 at 13:46

score -1 · Answer 2 · answered Feb 04 '19 at 22:02

-1

Not the whole issue in your code - but the initial error message is due to the table name having an underscore.

Update your SQL query to:

"INSERT INTO `table_1` (answer, score) VALUES ('$answers[$i]', '$scores[$i]')";

That should resolve the mysql error you saw.

However - you also need to look into improving your query. Look at escaping the values (never trust posted data that could be manipulated to just write to your database), or ideally use prepared statements.

answered Feb 04 '19 at 22:02

steve

2,469
1
23
30

2

Underscores are valid characters unquoted. https://dev.mysql.com/doc/refman/8.0/en/identifiers.html "basic Latin letters, digits 0-9, dollar, **underscore**" – user3783243 Feb 04 '19 at 22:07

PHP Form Submission - Syntax Errors

2 Answers2