Reputation:

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.

Upvotes: 0

Answers (5)

Paul Dixon

Reputation: 300825

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();
 }

Upvotes: 6

terrific

Reputation: 1667

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.

Upvotes: 0

Cory R. King

Reputation: 2796

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 ));

Upvotes: 1