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

[oskare100]

Hello,
I'm trying to fix this login script, but I always get the error Column count doesn't match value count at row 1 . Can you see any errors in the code/database structure or do you know another way of doing it?

The script:
[code=php:0]<?php
session_start();

include 'db_info.php';
// Connect to server and select databse.
mysql_connect("$sqlhost", "$sqlusername", "$sqlpassword")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// username and password sent from signup form
$vusername=$_POST['vusername'];
$vpassword=$_POST['vpassword'];

$sql="SELECT * FROM $user_tbl WHERE username='$vusername' and password='$vpassword'";
if ($result=mysql_query($sql) or die( mysql_error() )) {

    if(mysql_num_rows($result) == 1) {
        // If result matched $vusername and $vpassword, table row must be 1 row
        $row = mysql_fetch_assoc($result);
        $vuserid = $row['user_id'];
   
        //Register session variables
        $_SESSION["vusername"] = $vusername;
        $_SESSION["vpassword"] = $vpassword;
       
        // Log the login in the ip log table
        $sql="INSERT INTO $login_logs_tbl (user_id, ip, logged) VALUES('$vuserid', '".$_SERVER['REMOTE_ADDR']."', CURRENT_DATE, NOW())";
        mysql_query($sql) or die( mysql_error() );
   
        // Set the latest login in the user table
        $sql="UPDATE $user_tbl SET (latest_login, num_logins) VALUES (CURRENT_DATE, NOW(),num_logins+1) where user_id = '$vuserid'";
        mysql_query($sql) or die( mysql_error() );
   
        // Check if the IP is already logged in the database
        $sql="update $ip_logs_tbl set (latest) values (CURRENT_DATE, NOW()) where ip = '".$_SERVER['REMOTE_ADDR']."' AND user_id='$vuserid'";
        mysql_query($sql) or die( mysql_error() );
        if (mysql_affected_rows() == 0) {
            // It's a new IP for that user - log it
            $sql="INSERT INTO $ip_logs_tbl (ip, user_id, latest) VALUES('".$_SERVER['REMOTE_ADDR']."', '$vuserid', CURRENT_DATE, NOW())";
            mysql_query($sql) or die( mysql_error() );
           
            // And add 1 to the number of different IPs in the user table
            $sql="UPDATE $user_tbl SET num_ips = num_ips+1 where user_id = '$vuserid'";
            mysql_query($sql) or die( mysql_error() );
        }
        echo "logged in";
    } else {
        echo "Wrong Username or Password";
    }
}
?> [/code]

The database (I'm trying with):
[CODE]--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL auto_increment,
  `active` varchar(10) NOT NULL default '',
  `username` varchar(50) NOT NULL default '',
  `password` varchar(40) NOT NULL default '',
  `ebay_username` varchar(50) NOT NULL default '',
  `ebay_email` varchar(100) NOT NULL default '',
  `ebay_status` varchar(20) NOT NULL default '',
  `paypal_email` varchar(100) NOT NULL default '',
  `paypal_status` varchar(15) NOT NULL default '',
  `num_downloads` int(5) NOT NULL default '0',
  `num_logins` int(5) NOT NULL default '0',
  `num_ips` int(5) NOT NULL default '0',
  `num_purchases` int(5) NOT NULL default '0',
  `first_name` varchar(30) NOT NULL default '',
  `last_name` varchar(50) NOT NULL default '',
  `address_street` varchar(50) NOT NULL default '',
  `address_city` varchar(30) NOT NULL default '',
  `address_state` varchar(30) NOT NULL default '',
  `address_zip` varchar(20) NOT NULL default '',
  `address_country` varchar(30) NOT NULL default '',
  `address_status` varchar(11) NOT NULL default '',
  `latest_login` datetime NOT NULL default '0000-00-00 00:00:00',
  `latest_updated` datetime NOT NULL default '0000-00-00 00:00:00',
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`user_id`, `active`, `username`, `password`, `ebay_username`, `ebay_email`, `ebay_status`, `paypal_email`, `paypal_status`, `num_downloads`, `num_logins`, `num_ips`, `num_purchases`, `first_name`, `last_name`, `address_street`, `address_city`, `address_state`, `address_zip`, `address_country`, `address_status`, `latest_login`, `latest_updated`, `created`) VALUES
(1, '', 'test', 'test2', '', '', '', '', '', 0, 1, 1, 0, '', '', '', '', '', '', '', '', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '0000-00-00 00:00:00');[/CODE]

Thanks in advance,
/Oskar

[effigy]

It means just that. In the following statement you've told MySQL to put 4 values into 3 columns.

[code]INSERT INTO $login_logs_tbl (user_id, ip, logged) VALUES('$vuserid', '".$_SERVER['REMOTE_ADDR']."', CURRENT_DATE, NOW())[/code]

[mmatos]

[code]$vusername=$_POST['vusername'];
$vpassword=$_POST['vpassword'];

$sql="SELECT * FROM $user_tbl WHERE username='$vusername' and password='$vpassword'"; [/code]

You are wide open for a SQL injection attack right there.

[oskare100]

Hello,
OK, thanks, that solved the problem..
Now I've another one;
"(CURRENT_DATE,'num_logins+1') where user_id = '$vuserid'"; "

gives me this error;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(latest_login, num_logins) VALUES (CURRENT_DATE,'num_logins+1') where user_id = ' at line 1

What I want to do is add 1 to the current number of logins from the database.

AND;
[quote]You are wide open for a SQL injection attack right there.[/quote]

Do you mean that the user can write something that looks like a part of the querey themselves and in that way go around the username/password? Would that really work? If, How can/should I solve that problem?

Thanks,
/Oskar

[GingerRobot]

You've enclosed num_logins and plus 1 in single quotes - you could either leave out the quotes in this section entirely, or to make it obvious what you are doing, place the field name in backticks (`) and the number in single quotes. Backticks are used to refer to a field within mysql - they are not necessary for the script to work, but they can help you to see what you are doing.

"(CURRENT_DATE,`num_logins`+'1') where user_id = '$vuserid'";

As for your second question i would take a look at this page:

http://uk.php.net/manual/en/function.mysql-real-escape-string.php

It is for the function that you should use to prevent these types of attacks and also gives you an example of how they would work. It probably explains it better than i could.

[oskare100]

Hello,
I changed that but it didn't solve the problem, what else can it be?

Best Regards
/Oskar R