Error inserting DateTime values on database

Question

I searched for this online and some posts even here on StackOverflow talk about this but never resolve to my problem. I am having a problem inserting DateTime into the database. The first problem is that I only know how to make StringRequests from java, so I convert date to String (output example: "2000-04-23 10:25:06").

This is my java code:

StringRequest stringRequest = new StringRequest(Request.Method.GET, testInserirPontoURL,
    new Response.Listener<String>() {
        @Override
        public void onResponse(String response) {
            Toast.makeText(getApplicationContext(), response, Toast.LENGTH_SHORT).show();
        }
    },
    new Response.ErrorListener() {
        @Override
        public void onErrorResponse(VolleyError error) {}
    }) {
  @RequiresApi(api = Build.VERSION_CODES.O)
  @Override
  public Map<String, String> getHeaders() throws AuthFailureError {
      String codColab = getIntent().getStringExtra("codigo colaborador");
      LocalDate ldHoraEntrada = LocalDate.of(ponto.getEntrada().get(Calendar.YEAR), ponto.getEntrada().get(Calendar.MONTH) + 1, ponto.getEntrada().get(Calendar.DAY_OF_MONTH));
      String horaEntrada = ldHoraEntrada + " " + sdfHora2.format(ponto.getEntrada().getTime());
      LocalDate ldSaidaAlmoco = LocalDate.of(ponto.getSaidaAlmoco().get(Calendar.YEAR), ponto.getSaidaAlmoco().get(Calendar.MONTH) + 1, ponto.getSaidaAlmoco().get(Calendar.DAY_OF_MONTH));
      String saidaAlmoco = ldSaidaAlmoco + " " + sdfHora2.format(ponto.getSaidaAlmoco().getTime());
      LocalDate ldEntradaTarde = LocalDate.of(ponto.getEntradaTarde().get(Calendar.YEAR), ponto.getEntradaTarde().get(Calendar.MONTH) + 1, ponto.getEntradaTarde().get(Calendar.DAY_OF_MONTH));
      String entradaTarde = ldEntradaTarde + " " + sdfHora2.format(ponto.getEntradaTarde().getTime());
      LocalDate ldHoraSaida = LocalDate.of(ponto.getSaida().get(Calendar.YEAR), ponto.getSaida().get(Calendar.MONTH) + 1, ponto.getSaida().get(Calendar.DAY_OF_MONTH));
      String horaSaida = ldHoraSaida + " " + sdfHora2.format(ponto.getSaida().getTime());

      Map<String, String> map = new HashMap<String, String>();
      map.put("horaEntrada", horaEntrada);
      map.put("saidaAlmoco", saidaAlmoco);
      map.put("entradaTarde", entradaTarde);
      map.put("horaSaida", horaSaida);
      map.put("cod_colab", codColab);
      return map;
  }
};
RequestQueue requestQueue = Volley.newRequestQueue(PontoActivity.this);
requestQueue.add(stringRequest);

note: The codColab is a String in here but an int in the database, but it works just fine because I already use it for login.

This is my php code:

    if($_SERVER['REQUEST_METHOD']=='GET'){

    $horaEntrada = $_GET['horaEntrada'];
    $saidaAlmoco = $_GET['saidaAlmoco'];
    $entradaTarde = $_GET['entradaTarde'];
    $horaSaida = $_GET['horaSaida'];
    $cod_colab = $_GET['cod_colab'];

    $sql= "INSERT INTO ponto (hora_entrada,saida_almoco,entrada_tarde,hora_saida,cod_colab) VALUES ('".$horaEntrada."', '".$saidaAlmoco."', '".$entradaTarde."', '".$horaSaida."', '".$cod_colab."')";
    //$sql= "INSERT INTO `ponto` (`id`, `hora_entrada`, `saida_almoco`, `entrada_tarde`, `hora_saida`, `cod_colab`) VALUES (NULL, '2019-05-15 10:25:41', '2019-05-09 14:25:37', '2019-05-16 11:20:13', '2019-05-09 13:25:30', '1')";
    //$sql= "INSERT INTO ponto (hora_entrada,saida_almoco,entrada_tarde,hora_saida,cod_colab) VALUES ('$horaEntrada', '$saidaAlmoco', '$entradaTarde', '$horaSaida', '$cod_colab')";

    if (mysqli_query($conn, $sql)){
        echo "registado";
    } else {
        echo "erro a registar";
    }

Note: the comment lines are the ones I tried. the 1st comment line works but I want the values of the variables

Possible duplicate of [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Dharman, May 23 '19 at 08:59
@Dharman That is not the problem, the problem is that it doesn't insert. — Pedro Filipe, May 23 '19 at 09:03
Have you already fixed SQL statements and you are still facing the original problem? Then please update the code in question. — Dharman, May 23 '19 at 09:06
As an aside, in your Java code you are mixing old and modern date-time classes, which is not a good idea. If you cannot get `ponto.getEntrada()` to return a modern type, I recommend that you do `((GregorianCalendar) ponto.getEntrada()).toZonedDateTime()` (on API level 26+) or `DateTimeUtils.toZonedDateTime(ponto.getEntrada())` (if using ThreeTenABP) to convert to a `ZonedDateTime`, which you then format using a `DateTimeFormatter`. — Ole V.V., May 23 '19 at 09:09
[Micaso](https://stackoverflow.com/users/8037399/micaso) would like to ask: Did you check the values of your variables? Maybe it is url_encoded? — Ole V.V., May 23 '19 at 09:12
What is the value of `$conn->error` after your failed attempt? — Ole V.V., May 23 '19 at 09:17
@OleV.V. it's not an error in the connection. I use the same connection for the login and it works. — Pedro Filipe, May 23 '19 at 09:22
@Micaso it outputs `C:\xampp\htdocs\MetaloAppTest\inserir.php:12:null` — Pedro Filipe, May 23 '19 at 10:21
And that is your problem... because your value is: '' and that is an invalid datetime — Micaso, May 23 '19 at 10:25
@Micaso you are right I thought I did the request the same way I did for login(both StringRequests) but I wrote something wrong. — Pedro Filipe, May 23 '19 at 13:28

score 0 · Answer 1 · answered May 23 '19 at 10:42

Since $horaSaida can be NULL the output will be something like this:

INSERT INTO ponto (hora_entrada,saida_almoco,entrada_tarde,hora_saida,cod_colab) VALUES ('',...)

and '' isn't a right datatime value

You could this to get the right SQL:

$sql= "INSERT INTO ponto (hora_entrada,saida_almoco,entrada_tarde,hora_saida,cod_colab) VALUES (". (null === $horaEntrada ? "NULL" : "'$horaEntrada'") . ", '".$saidaAlmoco."', '".$entradaTarde."', '".$horaSaida."', '".$cod_colab."')";

Error inserting DateTime values on database

1 Answers1