It's a common misconception that user input can be filtered. PHP even has a (now deprecated) "feature", called magic-quotes, that builds on this idea. It's nonsense. Forget about filtering (or cleaning, or whatever people call it).
What you should do, to avoid problems, is quite simple: whenever you embed a a piece of data within a foreign code, you must treat it according to the formatting rules of that code. But you must understand that such rules could be too complicated to try to follow them all manually. For example, in SQL, rules for strings, numbers and identifiers are all different. For your convenience, in most cases there is a dedicated tool for such an embedding. For example, when you need to use a PHP variable in the SQL query, you have to use a prepared statement, that will take care of all the proper formatting/treatment.
Another example is HTML: If you embed strings within HTML markup, you must escape it with htmlspecialchars
. This means that every single echo
or print
statement should use htmlspecialchars
.
A third example could be shell commands: If you are going to embed strings (such as arguments) to external commands, and call them with exec
, then you must use escapeshellcmd
and escapeshellarg
.
Also, a very compelling example is JSON. The rules are so numerous and complicated that you would never be able to follow them all manually. That's why you should never ever create a JSON string manually, but always use a dedicated function, json_encode()
that will correctly format every bit of data.
And so on and so forth ...
The only case where you need to actively filter data, is if you're accepting preformatted input. For example, if you let your users post HTML markup, that you plan to display on the site. However, you should be wise to avoid this at all cost, since no matter how well you filter it, it will always be a potential security hole.
IMPORTANT EDIT:
It is now possible to achieve this with DATETIME fields since MySQL 5.6.5, take a look at the other post below...
Previous versions can't do that with DATETIME...
But you can do it with TIMESTAMP:
mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)
mysql> desc test;
+-------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+-------------------+-------+
| str | varchar(32) | YES | | NULL | |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------+-------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)
mysql> insert into test (str) values ("demo");
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+---------------------+
| str | ts |
+------+---------------------+
| demo | 2008-10-03 22:59:52 |
+------+---------------------+
1 row in set (0.00 sec)
mysql>
CAVEAT: IF you define a column with CURRENT_TIMESTAMP ON as default, you will need to ALWAYS specify a value for this column or the value will automatically reset itself to "now()" on update. This means that if you do not want the value to change, your UPDATE statement must contain "[your column name] = [your column name]" (or some other value) or the value will become "now()". Weird, but true. I am using 5.5.56-MariaDB
Best Answer
Given it's a raw expression, you should use
DB::raw()
to setCURRENT_TIMESTAMP
as a default value for a column:This works flawlessly on every database driver.
As of Laravel 5.1.25 (see PR 10962 and commit 15c487fe) you can now use the new
useCurrent()
column modifier method to achieve the same default value for a column:Back to the question, on MySQL you could also use the
ON UPDATE
clause throughDB::raw()
:Again, as of Laravel 8.36.0 (see PR 36817) you can now use the new
useCurrentOnUpdate()
column modifier method together with theuseCurrent()
modifier to achieve the same default value for a column:Gotchas
MySQL
Starting with MySQL 5.7,
0000-00-00 00:00:00
is no longer considered a valid date. As documented at the Laravel 5.2 upgrade guide, all timestamp columns should receive a valid default value when you insert records into your database. You may use theuseCurrent()
column modifier (from Laravel 5.1.25 and above) in your migrations to default the timestamp columns to the current timestamps, or you may make the timestampsnullable()
to allow null values.PostgreSQL & Laravel 4.x
In Laravel 4.x versions, the PostgreSQL driver was using the default database precision to store timestamp values. When using the
CURRENT_TIMESTAMP
function on a column with a default precision, PostgreSQL generates a timestamp with the higher precision available, thus generating a timestamp with a fractional second part - see this SQL fiddle.This will led Carbon to fail parsing a timestamp since it won't be expecting microseconds being stored. To avoid this unexpected behavior breaking your application you have to explicitly give a zero precision to the
CURRENT_TIMESTAMP
function as below:Since Laravel 5.0,
timestamp()
columns has been changed to use a default precision of zero which avoids this.Thanks to @andrewhl for pointing out the Laravel 4.x issue in the comments.
Thanks to @ChanakaKarunarathne for bringing out the new
useCurrentOnUpdate()
shortcut in the comments.