MySQL not able to execute INSERT INTO [table]. Column count doesn't match value count at row 1

Question

I'm trying to pull information from an HTML form and put this into a database using the following code:

$link = mysqli_connect("localhost", "user", "password", "MyDB");

if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

$sql = "INSERT INTO interest (name, email, dob, address) 
        VALUES ('$fullname', '$email', '$dob' '$addr')";
if(mysqli_query($link, $sql)){
    echo "Records added successfully.";
}else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

mysqli_close($link);

It was working, and I've managed to get 2 test runs in, but now I'm getting the following error at the top of my submission page

ERROR: Could not able to execute INSERT INTO MyDB (name, email, dob, address) VALUES ('test name', 'test@email.com', '2003-02-01' 'address'). Column count doesn't match value count at row 1

I have another variant of this which sends a PHP email, which is the file I'm using to base this database connection on.

There is also an autoincrement on ID column which is set as the primary key in the database if that makes a difference? SQL isn't my strong point unfortunately!

so where are these coming from? `$fullname', '$email', '$dob' '$addr'` and do they hold values? http://php.net/manual/en/function.error-reporting.php Edit: ok, you do have values, but it's a syntax error. Missing a comma and is ***Off-topic.*** — Funk Forty Niner, Jan 29 '16 at 15:00
Nice injection you have there. Enjoy your server being pawned. — Funk Forty Niner, Jan 29 '16 at 15:08
If an answer solved your problem, consider accepting the answer. Here's how http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work then return here and do the same with the tick/checkmark till it turns green. This informs the community, that a solution was found. Otherwise, others may think the question is still open and may want to post (more) answers. *Welcome to Stack!* — Jay Blanchard, Jan 29 '16 at 18:03

score 5 · Answer 1 · edited May 23 '17 at 11:44

Given the syntax error you have in your query, being a missing comma in '$dob' '$addr'; you are open to an SQL injection and should be using a prepared statement.

Therefore, I am submitting this complementary answer for your own safety.

Here is an example of a prepared statement using the MySQLi API.

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$link = mysqli_connect('localhost', 'xxx', 'xxx', 'my_db');

if (!$link) {
    die('Connect Error: ' . mysqli_connect_error());
}

// assuming these are the POST arrays taken from your HTML form if you're using one.
$fullname = $_POST['fullname'];
$email = $_POST['email'];
$dob = $_POST['dob'];
$addr = $_POST['addr'];

   $sql = ("INSERT INTO interest (name, email, dob, address) VALUES (?, ?, ?, ?)");

    $stmt = $link->prepare($sql) or die("Failed Execution");
    $stmt->bind_param('ssss', $fullname, $email, $dob, $addr);

    $stmt->execute();
    echo $stmt->error;

echo "SUCCESS";

    exit();

References:

Foonotes:

If using the following failed because of the AI'd column:

$sql = ("INSERT INTO interest (name, email, dob, address) VALUES (?, ?, ?, ?)");

You may also try: (I used id as the AI'd column as an example)

$sql = ("INSERT INTO interest (id, name, email, dob, address) VALUES ('', ?, ?, ?, ?)");

This could be the case, as I have seen this type of SQL failure behaviour before.

Thank you - I'll give this a go and see how I get on. I do know a little about SQL Injection but I've only recently started working with PHP and SQL to this extent. I'll do a bit more research next time before making myself look a fool haha — Aaron, Feb 01 '16 at 13:01

score 2 · Answer 2 · answered Jan 29 '16 at 15:01

2

You have missed comma here:

VALUES ('$fullname', '$email', '$dob' '$addr')

Thus (as it was clearly said in error text) column count doesn't mach values count.

It should be

VALUES ('$fullname', '$email', '$dob', '$addr')

answered Jan 29 '16 at 15:01

Andrey Korneyev

26,353
15
70
71

Given your probable SQL experience, you should make note that you're still leaving them open to an SQL injection. If you're going to go after low-hanging fruit like that (off-topic syntax error, btw), at the very least give them a safer solution. – Funk Forty Niner Jan 29 '16 at 15:07
@Fred-ii- PHP is not my "native" programming language, so I can't construct parameterized query for `mysqli_query` without reading documentation. If you can do it - I think your suggestion will be useful to the question author. – Andrey Korneyev Jan 29 '16 at 15:10
This isn't PHP, it's MySQL, *2 different animals altogether*. ;-) – Funk Forty Niner Jan 29 '16 at 15:11
Ah ok Andy. I see you're quite strong in C ;-) *Cheers* – Funk Forty Niner Jan 29 '16 at 15:36

score 2 · Answer 3 · answered Jan 29 '16 at 15:01

2

You missed a comma

$sql = "INSERT INTO interest (name, email, dob, address) 
        VALUES ('$fullname', '$email', '$dob', '$addr')";
                                             ^here

answered Jan 29 '16 at 15:01

Ikhlak S.

8,578
10
57
77

score 0 · Answer 4 · answered Aug 14 '17 at 19:27

0

You missed a comma:

VALUES ('$fullname', '$email', '$dob' '$addr')

answered Aug 14 '17 at 19:27

Seth Connell

867
1
9
12

MySQL not able to execute INSERT INTO [table]. Column count doesn't match value count at row 1

4 Answers4