How to add the time form was submitted

Question

I have this page below, which is the action when you submit a form. I am wondering how you can add a time function to this, to add the time that the form was submitted to my database table in the row "time". I have tried numerous things and can't get them to work :/

<?php
require 'db/connect.php';

if(isset($_POST['submitted']))
{

    $player = $_POST['inputWho'];
    $offense = $_POST['inputWhat'];
    $where = $_POST['inputPlace'];
    $proof = $_POST['inputProof'];
    $comments = $_POST['inputComments'];

    $sqlinsert = "INSERT INTO offenses (player, reason, place, proof, comments) VALUES  ('$player', '$offense', '$where', '$proof', '$comments')";

    if (!mysqli_query($db, $sqlinsert)) {
        die('Error inserting new record.'. "<br />". mysqli_error($db));
    } //end of nested if

} //end of if

header("Location: success.php");

?>

there are a couple of php time functions, just look for it on php.net, or you can just use UNIX_TIMESTAMP() or something like this in your sql query. next, your query is open for sql injections, just use mysql_real_escape_string to escape the strings you want to insert, if you dont an attacker could "build his own query" and execute malicious code. — Sirac, Mar 09 '14 at 20:13
Also, your code example as it stands it a security disaster: you are taking user-submitted input and sending it right to the database. Maybe you just presented it like that to keep your example simple, but I want to point it out because somebody might see your code and want to imitate it. This is a BAD example to follow. — Nate C-K, Mar 09 '14 at 20:20
You should use prepared statements with parameters. Parameters are immune to SQL injection attacks. Any time you see someone inserting inputs right into a SQL string like that it should be a giant red flag that you're looking at a security vulnerability. — Nate C-K, Mar 09 '14 at 20:22
I'd advise against trying to escape inputs as @Sirac advises. It is error-prone. Just use parameterized queries. — Nate C-K, Mar 09 '14 at 20:27
I have heard that people prefer parameterized querys over escaping, but I have never heard of problems with escaping. — Sirac, Mar 09 '14 at 22:52
@Sirac: The main problem with escaping is that sooner or later some programmer forgets to do it and creates an injection vulnerability. When I say it is error-prone, I mostly mean human error. However, there are a few corner cases (e.g. `%` doesn't get escaped) that can cause problems as well. — Nate C-K, Mar 10 '14 at 23:36
An example of a SQL injection vulnerability that escaping won't help you with: `select * from users where username=$username` -- notice that I "forgot" to put quotes around the username. Now the attacker can put any arbitrary SQL code in the username field and it will run. It will not matter if `$username` has been escaped since characters like `;` do not (normally) get escaped. — Nate C-K, Mar 10 '14 at 23:43
Similarly, `select * from users where user_id = $user_id` will also be a security vulnerability if I have forgotten to make sure that `$user_id` is really an integer. Again, escaping doesn't protect me because the string is not in quotes. — Nate C-K, Mar 10 '14 at 23:54
@Sirac: More on this issue: http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/ — Nate C-K, Mar 10 '14 at 23:56
@NateC-K i got it, parameterized queries are much easier to handle and much nicer, but i havent seen an attack that happened when escaping was used. — Sirac, Mar 11 '14 at 20:14
@Sirac: Any one developer's experience is limited, that's why you should learn from the experience of others. The experience across the industry as a whole is that attempting to escape strings and insert them into SQL queries is a bad idea. Let's not promote a bad practice. — Nate C-K, Mar 12 '14 at 02:02
@Sirac: Let's not be the SQL equivalent of this guy: http://detroit.cbslocal.com/2014/02/24/man-shoots-himself-in-the-head-while-demonstrating-gun-safety/ — Nate C-K, Mar 12 '14 at 02:08

score 4 · Answer 1 · edited May 23 '17 at 12:13

4

The way I usually approach this problem is to add a column to the table that looks like this:

created_time datetime default current_timestamp

You don't even have to include it in your INSERT statement, it will get set automatically when you create the row.

EDIT: The above statement only works with type TIMESTAMP, not DATETIME. (Many people are probably still on MySQL 5.5 or earlier at present.) This is discussed in the manual here: http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

You can avoid this version problem by using Skywalker's answer. (Or by not using MySQL.)

If you also need the column set when you update, that's a different issue.

I find it's generally preferable to get the time from inside the database rather than supplying one from the web server. This avoids any problems with your web server time getting slightly out of sync with your database server. (You may even have multiple web servers that are out of sync with each other.)

edited May 23 '17 at 12:13

Community

1
1

answered Mar 09 '14 at 20:14

Nate C-K

5,744
2
29
45

I like your approach too. – skywalker Mar 09 '14 at 20:15
@skywalker: Yours works well too, I guess it's a matter of preference. Personally I just like to keep definitions in the database when I can. – Nate C-K Mar 09 '14 at 20:18
Hey, looking at this, i've made a new row in the table called "time" formatted as "datetime" and set default to CURRENT_TIMESTAMP, but it won't let me add the row – aFreshKiwi Mar 09 '14 at 20:30
SQL query: ALTER TABLE `offenses` ADD `time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `open` ; MySQL said: Documentation #1067 - Invalid default value for 'time' – aFreshKiwi Mar 09 '14 at 20:41
@aFreshKiwi: Sorry, this only works with DATETIME for MySQL v5.6.5 or greater. For earlier versions you can only with this with a column of type TIMESTAMP. – Nate C-K Mar 09 '14 at 20:49
Personally I always use and recommend PostgreSQL over MySQL when possible, and this is an example of why. (MySQL tends to lack many basic SQL features like this one.) – Nate C-K Mar 09 '14 at 20:57

score 1 · Answer 2 · answered Mar 09 '14 at 20:13

1

Make your sql like this:

$sqlinsert = "INSERT INTO offenses (player, reason, place, proof, comments, time) VALUES  ('$player', '$offense', '$where', '$proof', '$comments', NOW())";

Make use of MySQL function NOW().

answered Mar 09 '14 at 20:13

skywalker

826
1
10
18

Grant · Answer 3 · 2014-03-09T20:35:34.607

0

Try this:

date_default_timezone_set('America/Los_Angeles');
$dateEnquiry = date("Y-m-d H:i:s", time());

$sqlinsert = "INSERT INTO offenses (player, reason, place, proof, comments, time) VALUES  ('$player', '$offense', '$where', '$proof', '$comments', '$dateEnquiry')";

edited Mar 09 '14 at 20:35

answered Mar 09 '14 at 20:13

Grant

2,413
2
30
41

This works excellently! However the time of the server that it will be submitted to is GMT -5 .. How can I set it to submit in that timezone? – aFreshKiwi Mar 09 '14 at 20:16
Or does NOW() get the server time? – aFreshKiwi Mar 09 '14 at 20:17
date_default_timezone_set('Africa/Johannesburg'); Choose your time zone. – Grant Mar 09 '14 at 20:18
Sure does, you have to place this above the date() function and select the correct time zone for GMT -5 What part of the world are you in? – Grant Mar 09 '14 at 20:21
I am guessing: date_default_timezone_set('Pacific/Auckland'); – Grant Mar 09 '14 at 20:23
If you always get the time from the database then your times will remain consistent. – Nate C-K Mar 09 '14 at 20:23
I'm in England, but need it at GMT -5. – aFreshKiwi Mar 09 '14 at 20:24
Also, it gets a php error of "not safe to rely on system settings" – aFreshKiwi Mar 09 '14 at 20:25
You have to set the default PHP time zone on your server. Read this: http://stackoverflow.com/questions/20743060/symfony2-and-date-default-timezone-get-it-is-not-safe-to-rely-on-the-system – Grant Mar 09 '14 at 20:31

How to add the time form was submitted

3 Answers3