STRING to DATE conversion in UPDATE

Question

I want to store a string variable as a date in a DATE type column using this code.

$affectedRows = $pdo->exec("UPDATE `$table_final` SET `Date_B` = $Date_Courant, `Id_SB` = $NoStation");

where $Date_Courant is defined as the string: 2022-07-01

I get this error message:

( ! ) Fatal error: in C:\Users\Jean Claude\Documents\ADNA06\Développements\FormationWamp\LoadData_Infile_Pics_Temp_Jours_Stations.php on line 116
( ! ) PDOException: in C:\Users\Jean Claude\Documents\ADNA06\Développements\FormationWamp\LoadData_Infile_Pics_Temp_Jours_Stations.php on line 116

Call Stack
1   0.0072  361904  {main}( )   ...\LoadData_Infile_Pics_Temp_Jours_Stations.php:0
2   0.1586  429024  exec( $query = 'UPDATE `picbruit` SET `Date_B` = (DATE) 2022-07-01, `Id_SB` = 1' )  ...\LoadData_Infile_Pics_Temp_Jours_Stations.php:116

I tried with the CAST instruction as follows: same error message.

$affectedRows = $pdo->exec("UPDATE `$table_final` SET `Date_B` = (DATE) $Date_Courant, `Id_SB` = $NoStation");

What is the proper way of doing this?

`'2022-07-01'` is a date but `2022-07-01` is just `2014`. You're following one of the many terrible PHP tutorials that teach code that's vulnerable to SQL injection. Also, I don't know what framework you're using but your error messages actually lack the error message. — Álvaro González, Jul 20 '22 at 08:13
I am using WAMP framework. I must admit that I am in the learning process of these tools. Following my question I have been refered to these already discussed answers "How can I prevent SQL injection in PHP?". All recommendations converge to the use of prepared queries. Since I have more than one single pdo->exec querries in my code, this could explain why inserting some number of rows, results in 4 times this number inserted. Is it what is called "injections"? — JCRCAN, Jul 20 '22 at 09:39
No, injection is what you're suffering in this question. You have a static text and it becomes an arithmetic operation. (WAMP is a software package with PHP, MySQL, etc. A framework is Symfony, Laravel, Slim...). — Álvaro González, Jul 20 '22 at 13:25
You can do exactly what's shown in the accepted answer of the linked question. There's no special case to be handled differently. — Álvaro González, Jul 21 '22 at 18:03
Fine. I am now trying to apply a prepared UPDATE query : $stm = $pdo->prepare("UPDATE `$databasetable_final` SET `Id_1` = name =:name WHERE Id_S BETWEEN $Row_From AND $MaxId) "); $stm->execute(['name'=>$Id_SB]) which results in the message: execute( $bound_input_params = ['name' => 11] ). The name parameter is definitly not well defined. Shoud it be prepared as an array type? — JCRCAN, Jul 23 '22 at 08:54

STRING to DATE conversion in UPDATE

0 Answers0