将DATETIME MySQL与DateTime PHP进行比较

Question

I have problems comparing dates between a date created with new dateTime () in php, and a date taken from a DATETIME field of a Mysql table.

With the following code, save a date in a DATETIME field of a MySQL table:

$now = new DateTime();
$update = $mysqli->query('INSERT INTO bonus (idplayer,lastlogin) VALUES ("'.$_GET["idplayer"].'","'.$now.'")');

Then I would like to retrieve the date from the tables and compare it with a date created using the php code:

$resetTime = new DateTime();
date_time_set($resetTime, 12, 00, 00);
$lastLogin = $mysqli->query('SELECT lastlogin FROM bonus WHERE idplayer = "'.$_GET["idgiocatore"].'"');
if ($resetTime < $lastLogin) {
        echo "OK!<br>";
    }

Using this code I can't comparate the dates because I get an error (I can't even do an echo of the date retrieved from the table). Can anyone tell me where I'm wrong and how can I solve the problem?

Answer 1

Try this

$resetTime = (new DateTime)->format('Y-m-d 12:00:00'); //need it as a string
//$resetTime = date('Y-m-d 12:00:00'); //-- this is fine too

$stmt = $mysqli->parpare('SELECT lastlogin FROM bonus WHERE idplayer = ?');
$stmt->bind_param("s", .$_GET["idplayer"]);
$stmt->execute();
list($lastLogin) = $stmt->get_result()->fetch_array();

if ($resetTime < $lastLogin) {
        echo "OK!<br>";
}

Basically your comparing the query result set, to your timestamp, instead of the value of the first column of the first row. Consider you code:

$lastLogin = $mysqli->query('SELECT lastlogin FROM bonus WHERE idplayer = "'.$_GET["idgiocatore"].'"');
if ($resetTime < $lastLogin) {

mysqli::query

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE.

https://www.php.net/manual/en/mysqli.query.php

Your also full of SQL Injection errors, an input such as this:

  $_GET["idgiocatore"] = '" OR 1 ORDER BY lastlogin DESC LIMIT 1 --'

Will turn your query into this

'SELECT lastlogin FROM bonus WHERE idplayer = "" OR 1 ORDER BY lastlogin DESC LIMIT 1 -- "'

Everything after the -- is a comment so we can ignore that ending ". This avoids creating a syntax error, and is a very common tactic (nothing new).

This will select all records from the DB because Anything plus OR 1 is always true, then it will sort them by your lastlogin value DESC so the highest value is first and Limit to 1 return row, well just because I can. Basically this will satisfy your if condition if ($resetTime < $lastLogin) Which I guess is a "good thing" (well for me, the haxor).

Essentially this is because you are just pasting user input right into the SQL, so it becomes part of the command if formulated correctly (not a good thing for you).

Anyway Hope it helps you.

*PS it's been an age (like 6 years) sense I used MySqli (normally I use PDO) so forgive me any errors there, most of that came from a basic tutorial over at W3Schools

One last thing instead of setting the time, consider removing it altogether with the MySql DATE() function:

$resetTime = (new DateTime)->format('Y-m-d');
//...
$stmt = $mysqli->parpare('SELECT DATE(lastlogin) FROM bonus WHERE idplayer = ?');