php error Column count doesn't match value count at row 1?

Question

I have checked for similar questions regarding the error however they didn't match the issue I seem to be having.

Getting the following error when attempting to insert data into database:

Column count doesn't match value count at row 1?

Here is a screenshot of the database table:

In the HTML a form, with an action of the php file, has multiple inputs with the names: name, surname, DateOfBirth, email, password, confirm-password

PHP:

<?php

// Only process the form if $_POST isn't empty
if ( ! empty( $_POST ) ) {

  // Connect to MySQL
  $mysqli = new mysqli( 'localhost', 'root', '', 'pptpp' );

  // Check our connection
  if ( $mysqli->connect_error ) {
    die( 'Connect Error: ' . $mysqli->connect_errno . ': ' . $mysqli->connect_error );
  }

  // Insert our data
  $sql = "INSERT INTO user ( Forename, Surname, DateOfBirth, Email, Password ) VALUES ( '{$mysqli->real_escape_string($_POST['name, surname, DateOfBirth, email, password '])}' )";
  $insert = $mysqli->query($sql);


  // Print response from MySQL
  if ( $insert ) {
    echo "Success! Row ID: {$mysqli->insert_id}";
  } else {
    die("Error: {$mysqli->errno} : {$mysqli->error}");
  }

  // Close our connection
  $mysqli->close();
}

?>

`$mysqli->real_escape_string($_POST['name, surname, DateOfBirth, email, password ']` that's not how it works. — Qirel, Apr 18 '17 at 14:56
`real_escape_string` doesn't take arrays/comma separated values as arguments for multiple rows. — Funk Forty Niner, Apr 18 '17 at 14:56
@Fred-ii- Could they be stored in php variables and listed in the same format? — Xander, Apr 18 '17 at 14:59
you can't use real escape string on arrays, I had a similar problem when trying to escape an array, I had to do a foreach to escape every single one — Cr1xus, Apr 18 '17 at 15:02
@EthanBristow you need to pre-define the variables with separate calls to the escaping function; there's way around it. You can use PDO's prepared statements in an array though. — Funk Forty Niner, Apr 18 '17 at 15:03
sience you're not using a framework, try to do something like $name = $mysqli->real_escape_string($_POST['name']).. etc — Cr1xus, Apr 18 '17 at 15:03

score 2 · Accepted Answer · edited May 23 '17 at 12:09

The following part

$mysqli->real_escape_string($_POST['name, surname, DateOfBirth, email, password ']

is invalid for two reasons:

mysqli::real_escape_string() takes only 1 argument at a time, a string (unless using procedural, mysqli_real_escape_string(), then the first is the connection, then the string as the second)
The $_POST can't be accessed in that way, I highly doubt you have one field named all that. You'll have to specify the index, as $_POST['name'] etc.

The solution is to match each column with the respective escaped value from $_POST,
like $mysqli->real_escape_string($_POST['name']) for the name,
$mysqli->real_escape_string($_POST['email']) for the email and so on, an example could be assigning it to variables, and using those in the query, as shown below.

$name      = $mysqli->real_escape_string($_POST['name']);
$surname   = $mysqli->real_escape_string($_POST['surname']);
$dob       = $mysqli->real_escape_string($_POST['DateOfBirth']);
$email     = $mysqli->real_escape_string($_POST['email']);
$password  = $mysqli->real_escape_string($_POST['password']);

$sql = "INSERT INTO user (Forename, Surname, DateOfBirth, Email, Password) VALUES ('$name', '$surname', '$dob', '$email', '$password')";
$insert = $mysqli->query($sql);

Then you should note that even with mysqli::real_escape_string(), it's not secure against SQL injection, and that you should use parameterized queries. An example of that is given below.

// Insert our data
$sql = "INSERT INTO user (Forename, Surname, DateOfBirth, Email, Password) VALUES (?, ?, ?, ?, ?)";
if ($stmt = $mysqli->prepare($sql)) {
    $stmt->bind_param("sssss", $_POST['name'], $_POST['surname'], $_POST['DateOfBirth'], $_POST['email'], $_POST['password']);
    if (!$stmt->execute())
        die("Execution failed: ".$stmt->error);

    echo "Success! Row ID: ".$stmt->insert_id;
    $stmt->close();
} else {
  die("Error: {$mysqli->errno} : {$mysqli->error}");
}

Usage of PHP error-reporting would've likely mentioned something about undefined indexes when you use the current escape. Always (in development) let PHP give you the errors, by enabling error-reporting with error_reporting(E_ALL); ini_set("display_errors", 1); at the top of your file.

Also note that storing passwords in plain-text is a big no. You should use functions like password_hash() / password_verify() to properly and securely store your users passwords.

References

Thank you, SQL injections aren't an issue for now as this is only a test simulation. In your solution how would you lay it out? — Xander, Apr 18 '17 at 15:17
I suggest you learn by doing it the proper way first anyhow, and use parameterized queries right off the bat. I've edited my answer with how to use `mysqli::real_escape_string()` individually, and added a note about password security. — Qirel, Apr 18 '17 at 15:23

php error Column count doesn't match value count at row 1?

1 Answers1

References