1

As my first topic here, I'll try not to write something too bad a post ^^

First, a little context: I'm building a website that intends to use MySQL databases to store various data, as meetings data (date, hour, subject, room, etc.). I want to allow users to add entries to the Meetings table via a form, which I believe works well for now.

My problem is the following: the Hour data I'm storing in my table uses the hh:mm:ss format, but it seems that the : isn't appreciated.

Each $meetingStuff is something I get from the form, $meetingHour following the hh:mm format, and here follows the codelines defining the query I send to my SQL server.

$dateToInsert = str_replace('/', '-', $meetingDate);
$hourToInsert = $meetingHour . ':00');
$sqlStmt = "INSERT INTO Meetings (Date, Hour, Committee, Title, Room, Type, Agenda) VALUES ($dateToInsert, $hourToInsert, $meetingCommittee, $meetingTitle, $meetingRoom, $meetingType, $meetingAgenda);";

Here is the error message I get, after having sent the query:

/Back-end_Conf_PP_2022/php/meetingFormAction.phpError: INSERT INTO Meetings (Date, Hour, Committee, Title, Room, Type, Agenda) VALUES (03-07-2019, 17:20:00, COM 27, Abracadabra, H, Official, );
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':20:00, COM 27, Abracadabra, H, Official, )' at line 1

Which let me think that the problem is the colon operator :, but I didn't manage to find how to get past it.

Thank you all for your time and attention :)

PS: Oh, by the way, the agenda value is empty "" for now but that's "normal".

Jueverhard
  • 111
  • 11
  • 1
    you're open to SQL injection and should resolve imminently – treyBake Jul 09 '19 at 09:53
  • Use PDO and the prepared statements, cause you risk SQL injection – Giacomo M Jul 09 '19 at 09:55
  • 1
    Make sure that your data type for date, hour is a datetime and time , I guess that your are using `integer` with it , however, you are open to SQL injections – hassan Jul 09 '19 at 09:56
  • The problem is you need to quote the value e.g: `...VALUES ('$dateToInsert', '$hourToInsert', '$meetingCommittee',...` but like others suggest, your script is open to SQL injection, use prepare statement for better security – catcon Jul 09 '19 at 10:01

1 Answers1

3

Use PDO with prepared statement to solve your problem but, MOST OF ALL, to avoid SQL Injections.

$pdo has to be a PDO object.

$sql = "INSERT INTO Meetings (Date, Hour, Committee, Title, Room, Type, Agenda) VALUES (?, ?, ?, ?, ?, ?, ?)";
$stmt= $pdo->prepare($sql);
$stmt->execute([$dateToInsert, $hourToInsert, $meetingCommittee, $meetingTitle, $meetingRoom, $meetingType, $meetingAgenda]);
Giacomo M
  • 4,450
  • 7
  • 28
  • 57
  • Thank you I'm going to try this! By the way, the inputs are being checked in order to prevent SQL injections – Jueverhard Jul 09 '19 at 10:01
  • Why do a job when PHP does for you? :) – Giacomo M Jul 09 '19 at 10:02
  • 1
    Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's – RiggsFolly Jul 09 '19 at 10:04