Odd Mysql issue on insert

问题:

Hy all,

Not sure what's going on here, but if I run this:

$query = 'INSERT INTO users 
(`id`, `first_name`, `second_name`, `register_date`, `lastlogin_date`) 
VALUES 
("'. $user_id . '", "' . $first_name .'", "'. $second_name . '", "' . $date . '", "' . $date . ");'; 
$result = mysql_query($query);

I get no return, but if I change it to this it's fine:

$query = 'INSERT INTO users (`id`, `first_name`, `second_name`, `register_date`, `lastlogin_date`) 
VALUES ("21021212", "Joe", "Bloggs", "20090202", "20090202");'; 
$result = mysql_query($query);

User id = bigint(20)

first name = varchar(30)

second name = varchar(30)

date = int(8)

At first I thought it was a issue with the vars but they are exactly the same and still don't work.

Any help appreciated.

回答1:

Get into the habit of escaping all database inputs with mysql_real_escape_string- really, you should use some kind of wrapper like PDO or ADODb to help you do this, but here's how you might do it without:

$query = sprintf("INSERT INTO users ".
    "(id, first_name, second_name, register_date, lastlogin_date)".
    "VALUES('%s','%s','%s','%s','%s')",
    mysql_real_escape_string($user_id),
    mysql_real_escape_string($first_name),
    mysql_real_escape_string($second_name),
    mysql_real_escape_string($date),
    mysql_real_escape_string($date));

 $result = mysql_query($query);

and also check for errors with mysql_error

 if (!$result)
 {
     echo "Error in $query: ".mysql_error();
 }

回答2:

What's the result from "mysql_error()"? Always check this, especially if something doesn't seem to be working.

Also, echo out $query to see what it really looks like. That could be telling.

回答3:

Maybe the value of $date was "1111'); DELETE FROM users;"?

Seriously though? The problem is that isn't how you interact with your database. You shouldn't be passing in your data with your query. You need to specify the query, the parameters for the query, and pass in the actual parameter values when you execute the query. Anything else is inefficient, insecure and prone to bugs like the one you have.

By using PDO or something that supports parametrized queries, you'll find these kinds of issues go away because you are calling the database property. It is also much more secure and can speed up the database.

$sth = $dbh->prepare("INSERT INTO users (`id`, `first_name`, `second_name`, `register_date`, `lastlogin_date`) VALUES (?,?,?,?,?)")
$sth->execute(array($user_id ,$first_name , $second_name , $date, $date ));

回答4:

In addition to echoing the query and checking mysql_error() as @GoatRider suggests:

1. Are you escaping your data properly? See mysql_real_escape_string()
2. You shouldn't end your queries with a semicolon when using mysql_query()

回答5:

in $query = 'INSERT INTO users (id, first_name, second_name, register_date, lastlogin_date) VALUES ("' . $user_id . '", "' . $first_name . '", "' . $second_name . '", "' . $date . '", "' . $date . '"); are u giving the correct date format?? it might be the issue. otherwise the syntax is all fine.