0

I have this query;

"INSERT INTO download(download_key, ip, file, expire) VALUES ('".$text."', 
'".$mysql->real_escape_string($_SERVER['REMOTE_ADDR'])."', 
'".$searchQuery['file_match']."', 
'NOW() + INTERVAL 10 MINUTES')"

When I insert this in the database, the row expire is always 0000-00-00 00:00:00. I have tried the types:

  • TIMESTAMP
  • DATE
  • DATETIME
  • TIME

But is always 0000-00-00 00:00:00 or 00:00:00.

What is the right type for NOW()? I`m using MYSQL

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
user2897922
  • 79
  • 7
  • Note that it's better to avoid concatenating strings altogether in favor of prepared statements - otherwise you run the risk of SQL Injection (for instance, are you **absolutely** sure that `$text` and `$searchQuery['file_match']` are safe?). Please read [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) for some more info (which states that `$mysql->real_escape_string() is deprecated, too). – Clockwork-Muse Nov 24 '13 at 11:42
  • Yes, $text is random, no user input. $searchQuery['file_match'] is comming out of the database. – user2897922 Nov 29 '13 at 18:42
  • _How_ was `$searchQuery['file_match']` put into the database? ie, does someone have the ability to add random text to the file? Such as a SQL query string? It's best to assume that all text is suspect (Especially from a dynamic source), and to use prepared statements. You don't need a dynamic query (concatenated string) here. There is a use case for dynamic statements, but those are usually small, and the 'attack surface' can be limited even then. For instance, don't concatenate dynamic queries with their parameters - prepare the (new) statement as normal. – Clockwork-Muse Dec 01 '13 at 05:28

4 Answers4

3

Remove the quotes,quotes are for strings

NOW() + INTERVAL 10 MINUTE
Mihai
  • 26,325
  • 7
  • 66
  • 81
  • Still 0000-00-00 00:00:00 type: DATETIME – user2897922 Nov 24 '13 at 11:05
  • 1
    +1 this. I'm gonna add one more thing - quotes are not a mistake for type date, but you need to put the date in correct syntax if you are passing a string for date, so for example generate correct date in PHP or whatever language you are using. So correct SQL would be also: `"', '2013-11-24 12:00:00')"` – Kelu Thatsall Nov 24 '13 at 11:05
  • Are you sure anything is getting inserted?Check if you have the last row. – Mihai Nov 24 '13 at 11:08
1
"INSERT INTO download(download_key, ip, file, expire) VALUES ('".$text."', 
'".$mysql->real_escape_string($_SERVER['REMOTE_ADDR'])."', 
'".$searchQuery['file_match']."', 
NOW() + INTERVAL 10 MINUTES)"
Ed Ganiukov
  • 91
  • 4
1

I'm gonna add a little thing to people already said.

Except the thing you shouldn't pass sql functions as string so don't put NOW() + INTERVAL 10 MINUTES in quotes, you can pass a string for date also. You can generate this date with php for example.

Try this fiddle to check how to put simple DATE in database: http://sqlfiddle.com/#!2/cddc1/1

Also with PHP you can generate the date with this code:

$minutes = 10;
$date = date('Y-m-d H:i:s', time() + $minutes*60);
$query = "INSERT INTO download(download_key, ip, file, expire) VALUES ('$text', 
'{$mysql->real_escape_string($_SERVER['REMOTE_ADDR'])}', 
'{$searchQuery['file_match']}', 
'$date')";
Kelu Thatsall
  • 2,494
  • 1
  • 22
  • 50
1

Thanks to @Kelu Thatsall: Fix:

NOW() + INTERVAL 10 MINUTE
user2897922
  • 79
  • 7