Trying to UPDATE SQL DATE column via HTML form; Is this even possible?

Question

When I run this Script the strtotime and date functions work, but when the SQL query runs the date column in the db remains blank.

$date = mysqli_real_escape_string($conn, $_POST['date']);
    $day1 = strtotime($date);
    $day1 = date('Y-m-d', $day1);
    $id = 1;
    echo $day2;

    $sql = "UPDATE essay SET date = $day1 WHERE id = $id";

Learn to use parameters rather than munging query strings with literal values. — Gordon Linoff, Apr 18 '19 at 14:47
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Apr 18 '19 at 15:23

score 0 · Answer 1 · answered Apr 18 '19 at 14:47

0

You have to add a quote over the $day1 like this way :

$sql = "UPDATE essay SET date = '$day1' WHERE id = '$id'";

Another way to do it by concatenate :

$sql = "UPDATE essay SET date = ".$day1." WHERE id = ".$id;

answered Apr 18 '19 at 14:47

Yassine CHABLI

3,459
2
23
43

Let's not teach/propagate sloppy and dangerous coding practices. If you post an answer without prepared statements [you may want to consider this before posting](http://meta.stackoverflow.com/q/344703/). Additionally [a more valuable answer comes from showing the OP the right method](https://meta.stackoverflow.com/a/290789/1011527). – Jay Blanchard Apr 18 '19 at 15:22

score 0 · Answer 2 · answered Apr 18 '19 at 14:51

0

Unless an SQL field is an integer type or similar numeric type, data written to it should be quoted in an insert statement. In this case, your $day1 is something like "2019-04-18" so your SQL should read:

$sql = "UPDATE essay set date = '$day1' where id = $id";

The single quote should allow the query to succeed. Note that debugging this sort of thing is fairly easy, but isn't taught in some tutorials; if the query fails, try logging or echoing the MySQL(i) error:

$query = $db->query($sql);
if (!$query) echo $db->error;

answered Apr 18 '19 at 14:51

Kevin_Kinsey

2,285
1
22
23

Let's not teach/propagate sloppy and dangerous coding practices. If you post an answer without prepared statements [you may want to consider this before posting](http://meta.stackoverflow.com/q/344703/). Additionally [a more valuable answer comes from showing the OP the right method](https://meta.stackoverflow.com/a/290789/1011527). – Jay Blanchard Apr 18 '19 at 15:22
I'm a tad off center today, so I'll apologize. That was uncalled for, you're correct. I'm still not going to write him a book about the Right Way to program an entire application for a question that has an applicable three-word answer like "Quote the variable". – Kevin_Kinsey Apr 18 '19 at 15:47
Doesn't have to be university. You could answer the question as you did and then suggest they look into parameterized queries as the "better thing to do", with perhaps a link to the information. We just want people to learn how to program PHP safely. – Jay Blanchard Apr 18 '19 at 15:50
I can agree we want people to write secure programs, but the OP is fairly obviously still at a very basic stage of PHP usage, so I thought it best to address the simple issue and not go into full Preacher Mode. – Kevin_Kinsey Apr 18 '19 at 15:58
My only counter to this is this - they should learn correctly from the start or they will have to go back and unlearn bad habits. – Jay Blanchard Apr 18 '19 at 16:09

Trying to UPDATE SQL DATE column via HTML form; Is this even possible?

2 Answers2