Not finding the right syntax to use near '1' at line 1 [mysqli]

Question

I got

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1"

message when I run this

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: %s\n", mysqli_connect_error());
              exit();
        }

        $result = $mysqli->query($sql);
        if (!$result) {
             printf("%s\n", $mysqli->error);
             exit();
        }
    }

Other info
table 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;

table 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;

Can you please post your `data types` of the `database attributes`? — Robin Gillitzer, Feb 18 '20 at 11:03
@rick Your code is wide open to SQL injection (or would be, assuming that in reality the checkInDate or checkOutDate values would probably originate from user input. Either way you should use parameters as standard practice, to avoid any doubt). — Foued MOUSSI, Feb 18 '20 at 12:36
@rick there's a nice simple example for PDO (and other libraries) at [bobby-tables.com/php](https://bobby-tables.com/php), and [bobby-tables.com](https://bobby-tables.com) has a cartoon which gives a simple and funny explanation of how SQL injection can happen and what the risks are. Parameterising your queries is the only sure way to protect against it. — Foued MOUSSI, Feb 18 '20 at 12:37
If you used prepared statements it is likely you would never run into this issue. By trying to make it unsafe you made your code too complicated. Keep it simple. Use prepared statements and [mysqli exceptions.](https://stackoverflow.com/questions/22662488/mysqli-fetch-assoc-expects-parameter-call-to-a-member-function-bind-param) — Dharman, Feb 18 '20 at 13:05

score 2 · Answer 1 · answered Feb 18 '20 at 11:02

2

You may use PHP MySQLi Prepared Statements to Prevent SQL Injection

Expl :

$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();

Not finding the right syntax to use near '1' at line 1 [mysqli]

1 Answers1