SQL不接受INSERT INTO，其中包含冒号（“：”）的值[复制]

Question

This question already has an answer here:

• When to use single quotes, double quotes, and backticks in MySQL 12 answers

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".

</div>

Answer 1

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]);