Get a date from HTML date form and insert it into a PostgreSQL table using PHP

Question

I've got a HTML form with a Date

<input type="date" name="mydate">

This is how it look like:

My PHP script starts when the form is submitted. It look like this more or less:

$mydate = $_POST['mydate'];
$query = "insert into datetable(when) values({$mydate})";
$res = pg_query($db, $query);

It just doesn't work. When I try to manually run the generate query in my psql CLI I get an error like this:

ERROR:  column "when" is of type date but expression is of type integer 
LINE 1: ..., when) values (1990-01-01...
                           ^ 
HINT:  You will need to rewrite or cast the expression.

However I've got no idea how I can do it.

I've tried to convert mydate using date() and timetostr in PHP. I've tried to do something with to_char(), to_date() as well. However I still cannot do anything to make it run...

You should read about SQL injection.

Michas
– Michas

2016-01-31 20:03:27 +00:00
Commented Jan 31, 2016 at 20:03 — Michas
– Michas, Commented Jan 31, 2016 at 20:03
Use pg_query_params or add apostrophes.

Michas
– Michas

2016-01-31 20:12:16 +00:00
Commented Jan 31, 2016 at 20:12 — Michas
– Michas, Commented Jan 31, 2016 at 20:12

Michas · Accepted Answer · 2016-01-31 20:44:28Z

1

You should pass the date as string. Few examples.

$mydate = pg_escape_literal($_POST['mydate']);
/*        ^^^^^^^^^^^^^^^^^ */
$query = "insert into datetable(when) values({$mydate})";
$res = pg_query($db, $query);


$mydate = pg_escape_string($_POST['mydate']);
/*        ^^^^^^^^^^^^^^^^                   v         v */
$query = "insert into datetable(when) values('{$mydate}')";
$res = pg_query($db, $query);


$mydate = $_POST['mydate'];
$query = "insert into datetable(when) values($1)";
/*     vvvvvvvvvvvvvvv              vvvvvvvvv^^ */
$res = pg_query_params($db, $query, [$mydate]);

http://php.net/manual/en/function.pg-escape-literal.php
http://php.net/manual/en/function.pg-escape-string.php
http://php.net/manual/en/function.pg-query-params.php

answered Jan 31, 2016 at 20:44

Michas

9,5966 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get a date from HTML date form and insert it into a PostgreSQL table using PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related