0

I am working on a membership script, and can't for the life of me figure out whats wrong with my query... anyone have any idea's? Think I need a second set of eyes.. Originally I was just sending

$sqlquery2 = "UPDATE users SET lastvisit = now() WHERE id = '" . $id ."'";
but it was updating the joined_date column as well. So I tried this and broke it further.

CODE EXCERPT :

            //These variables are pulled from prior query

            $id = $row['id'];
            $hashed_password = $row['password'];
            $username = $row['username'];
            $joined = $row['join_date'];
            $salt = $row['salt'];
            $email = $row['email'];

            //compare password pulled from database
            if(password_verify($password,$hashed_password)){
                $sqlquery2 = "UPDATE users SET lastvisit = now(), join_date = ".$joined." WHERE id = '" . $id ."'";

//$joined is equal to 2016-10-19 17:24:08

JTsToolbox
  • 3
  • 1
  • You are puzzled on why `joined_date column` is being updated yet you have in your query `join_date = ".$joined."`? – Irvin Oct 20 '16 at 00:31
  • Thats what I changed it to when join date was being updated to the same as lastlogin when it was $sqlquery2 = "UPDATE users SET lastvisit = now() WHERE id = '" . $id ."'"; – JTsToolbox Oct 20 '16 at 00:32
  • 1
    `$joined` is a string, the query should fail. Are you executing that? – chris85 Oct 20 '16 at 00:35
  • Yes it fails. it tells me I have syntax errors, how would i properly preserve the join_date column? originally I was using $sqlquery2 = "UPDATE users SET lastvisit = now() WHERE id = '" . $id ."'"; but that was changing my join_date column as well as the lastvisit column, so I attempted to store the result from a select query into $joined and re enter back in to the database...obviously i wasn't aware that i was using the wrong data type, or which one i need to use, or how to convert it properly. – JTsToolbox Oct 20 '16 at 00:40
  • It is correct, your syntax is invalid. That string would need to be quoted... BUT why update a column you dont want to update? This also is open to second level SQL injections. Use parameterized queries. – chris85 Oct 20 '16 at 00:42
  • So based on this being marked as duplicates and chris85's responses, I think what you are getting at is i should have used backticks, not single quotes?? That would work then? – JTsToolbox Oct 20 '16 at 01:05
  • `join_date = '".$joined."' ` would work because strings need to be quoted. But you should use parameterized queries and let the driver quote the values. – chris85 Oct 20 '16 at 01:06

1 Answers1

0

Please check whether your joined_date is a TIMESTAMP column. If so following will help you. http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html

  • While `TIMESTAMP` and `DATETIME` are currently fine to work with, DATETIME is preferred as `TIMESTAMP` has a [limitation](https://en.wikipedia.org/wiki/Year_2038_problem) – Xorifelse Oct 20 '16 at 00:38
  • If a timestamp column has specified with ON UPDATE CURRENT_TIMESTAMP, no matter which field you updated in the table. The column will get set to current time automatically. Please check your table definition if that is the case. If not you should have update it somewhere else in the program. Otherwise it should not getting changes automatically. –  Oct 20 '16 at 00:43
  • Thanks, this is exactly what it was, so, back to my original query, and everything working perfectly. Thanks!!! – JTsToolbox Oct 20 '16 at 00:46