在第 1 行的"1"附近找不到要使用的正确语法 [mysqli]

Question

我得到

">您的SQL语法有错误；请查看手册与您的MySQL服务器版本相对应，以便使用正确的语法在第1行的'1'附近">

运行此时的消息

private function saveBookingData(){
global $mysqli;
$sql = $mysqli->query("INSERT INTO bsi_bookings 
(booking_id, booking_time, start_date, end_date, client_id, 
total_cost, payment_amount, payment_type, special_requests) 
values(".$this->bookingId.", NOW(), 
'".$this->mysqlCheckInDate."', 
'".$this->mysqlCheckOutDate."', 
".$this->clientId.", 
".$this->grandTotalAmount.", 
".$this->totalPaymentAmount.", 
'".$this->paymentGatewayCode."', 
'".$this->clientdata['message']."')");
foreach($this->reservationdata as $revdata){
foreach($revdata['availablerooms'] as $rooms){              
$sql = $mysqli->query("INSERT INTO bsi_reservation 
(bookings_id, room_id, room_type_id) 
values(".$this->bookingId.",  ".$rooms['roomid'].", 
".$revdata['roomtypeid'].")");
}   
}
if (mysqli_connect_errno()) {
printf("Connect failed: %sn", mysqli_connect_error());
exit();
}
$result = $mysqli->query($sql);
if (!$result) {
printf("%sn", $mysqli->error);
exit();
}
}   

其他信息
表1

CREATE TABLE `bsi_bookings` (
`booking_id` int(10) UNSIGNED NOT NULL,
`booking_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`start_date` date NOT NULL DEFAULT '0000-00-00',
`end_date` date NOT NULL DEFAULT '0000-00-00',
`client_id` int(10) UNSIGNED DEFAULT NULL,
`child_count` int(2) NOT NULL DEFAULT '0',
`extra_guest_count` int(2) NOT NULL DEFAULT '0',
`discount_coupon` varchar(50) DEFAULT NULL,
`total_cost` decimal(10,2) UNSIGNED NOT NULL DEFAULT '0.00',
`payment_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
`payment_type` varchar(255) NOT NULL,
`payment_success` tinyint(1) NOT NULL DEFAULT '0',
`payment_txnid` varchar(100) DEFAULT NULL,
`paypal_email` varchar(500) DEFAULT NULL,
`special_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
`special_requests` text,
`is_block` tinyint(4) NOT NULL DEFAULT '0',
`is_deleted` tinyint(4) NOT NULL DEFAULT '0',
`block_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表2

CREATE TABLE `bsi_reservation` (
`id` int(11) NOT NULL,
`bookings_id` int(11) NOT NULL,
`room_id` int(11) NOT NULL,
`room_type_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

您可以使用PHP MySQLi Prepared Statements来防止SQL注入

实验：

$stmt = $mysqli->prepare("INSERT INTO bsi_bookings (booking_id, booking_time, start_date, end_date, client_id, total_cost, payment_amount, payment_type, special_requests) VALUES (?, ?, ?, ...)");
$stmt->bind_param("si", $this->bookingId, $var2, ....);
$stmt->execute();
$stmt->close();

阅读更多