Syntax error in MySQL INSERT INTO statement

Question

there is some problem with my code. Everytime I try to insert something into the database, I get the syntax error.

Here is my database structure:

CREATE TABLE `notes` (
  `id` int(12) NOT NULL,
  `type` varchar(15) NOT NULL,
  `title` varchar(43) NOT NULL,
  `text` varchar(43) NOT NULL,
  `group` varchar(32) NOT NULL,
  `uid` int(64) NOT NULL,
  `creator` int(64) NOT NULL,
  `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `notes`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `notes`
  MODIFY `id` int(12) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=79;

And thats my code

<?php
    session_start();
    require '../config.php';

    $notetype    = $_POST['type'];
    $notetitle   = $_POST['title'];
    $notetext    = $_POST['text'];
    $notegroup   = $_POST['group'];
    $noteuid     = $_POST['uid'];
    $notecreator = $_POST['creator'];
    $notetbname  = $note['tbname'];

    $conn = new mysqli($databaseconfig['ip'], $databaseconfig['user'], $databaseconfig['pass'], $databaseconfig['dbname']);

    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    $sql = "INSERT INTO $notetbname (type, title, text, group, uid, creator)
    VALUES ('$notetype', '$notetitle', '$notetext', '$notegroup', $noteuid, $notecreator);";

    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }

    $conn->close();
?>

This is what I get as error message:

Error: INSERT INTO notes (type, title, text, group, uid, creator) VALUES ('player', 'Hello there', 'Good morning everybody', 'Cop', 3325, 103);

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 'group, uid, creator) VALUES ('player', 'Hello there', 'Good morning everybody'' at line 1

Your code is vulnerable to SQL injection. You should use prepared statements. — Dharman, May 26 '19 at 07:14

icy · Accepted Answer · 2019-05-25T23:52:58.633

2

This is because group is a mysql's reserved word.

change the fieldname or try this (notice the backtick " ` " before and after the word group:

$sql = "INSERT INTO $notetbname (type, title, text, `group`, uid, creator)
VALUES ('$notetype', '$notetitle', '$notetext', '$notegroup', $noteuid, $notecreator);";

Here you can find a list of all reserved word (mysql 5.5) https://dev.mysql.com/doc/refman/5.5/en/keywords.html#keywords-5-5-detailed-G

edited May 25 '19 at 23:52

answered May 25 '19 at 23:47

icy

1,468
3
16
36

Well, I'm glad to help. If you resolved your issue consider to accept my answer :-) – icy May 25 '19 at 23:54
Yes I will, but need to wait some mins :) – th3_sh0w3r May 25 '19 at 23:55
@andreah if we use backticks then it will be inserted into database? – Mohit Kumar May 26 '19 at 00:16
@MohitKumar yes it works with backticks :) – th3_sh0w3r May 26 '19 at 01:06

Syntax error in MySQL INSERT INTO statement

1 Answers1