SQL Near error for inserting data through HTML form

Question

I've been trying to insert some data into my database for an events page. I have an html form and a seperate script, as seen below and the submit seems to go through for the ename id and imgsrc values but nothing past that. Anything more and I get a 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 'when, descr, positions) VALUES (test, 1 ,www.vzdc.org,2017-1-20 23:59:00' at line 1I've done some reasearch but maybe it's just a weird error on my end? I'm fairly new to mysql and I would love some help! Thanks, code below.

<!-- HTML form -->
 <form id="newevent" action="insertevent.php" method="post">
                <p>Event Name:</p><input name="ename" type="text" width="100">
                <p>ID:</p><input name="id" type="text" size="5">
                <p>Banner Link:</p><input name="imgsrc" type="text" size="50">
                <p>Description</p><input name="descr" type="text" height="1000px" >
                <p>Date / Time (yyyy-mm-dd HH:MM:SS):</p><input name="when" type="text">
                <p>Positions (ONE per line)</p><textarea name="positions" form="newevent" rows="10" cols="50"></textarea><br>
                <input value="Add Event" type="submit">
            </form>

/* PHP script on insertevent.php */
<?php
$link = mysqli_connect("localhost", "root", "xxx", "xxx");

// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Escape user inputs for security
$ename = mysqli_real_escape_string($link, $_POST['ename']);
$id = mysqli_real_escape_string($link, $_POST['id']);
$imgsrc = mysqli_real_escape_string($link, $_POST['imgsrc']);
$when = mysqli_real_escape_string($link, $_POST['when']);
$descr = mysqli_real_escape_string($link, $_POST['descr']);
$positions = mysqli_real_escape_string($link, $_POST['positions']);

// attempt insert query execution
$sql = "INSERT INTO events (ename, id, imgsrc, when, descr, positions) VALUES (`$ename`, $id , `$imgsrc`, `$when`, `$descr`, `$positions`)";
if(mysqli_query($link, $sql)){
    echo "Records added successfully.";
} else{
    echo "ERROR: Could not execute $sql. " . mysqli_error($link);
}

// close connection
mysqli_close($link);
?>

Logan Wayne · Accepted Answer · 2017-01-09 03:55:16Z

2

Don't use back-ticks for binding variables to your query, use single ticks instead. You can use back-ticks for the table and column name:

INSERT INTO `events` (`ename`, `id`, `imgsrc`, `when`, `descr`, `positions`) 
VALUES ('$ename', '$id', '$imgsrc', '$when', '$descr', '$positions')

WHEN is also a reserved word, so better change its name.

And since you're using mysqli_* API already, check prepared statement

answered Jan 9, 2017 at 3:55

Logan Wayne

5,98716 gold badges34 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

will0wtree Over a year ago

Thanks! That did it. I saw that prepared statement here and there, but know I know. Much appreciated.

shalvah · Accepted Answer · 2017-01-09 07:39:01Z

0

You are using an SQL reserved word as a column name.

$sql = "INSERT INTO events (ename, id, imgsrc, when, descr, positions) VALUES (`$ename`, $id , `$imgsrc`, `$when`, `$descr`, `$positions`)";

You really shouldn't, but if you want to get away with this, surround your table/column names with back ticks ```, like this:

$sql = "INSERT INTO `events` (`ename`, `id`, `imgsrc`, `when`, `descr`, `positions`) VALUES ('$ename', '$id' , '$imgsrc', '$when', '$descr', '$positions')";

I've removed the back ticks you put around your values because, well, they shouldn't be there.

Please learn and use MySQLi prepared statements. They'll help.

edited Jan 9, 2017 at 7:39

answered Jan 9, 2017 at 4:00

shalvah

89510 silver badges23 bronze badges

3 Comments

Grand Marshal Braev Over a year ago

What is the difference between @LoganWayne's answer to yours?

shalvah Over a year ago

I felt it wasn't really in-depth, so I added an explanation of why the OP had the error, plus some other elaboration. No rule against that, is there?

EhsanT Over a year ago

Unfortunately your code also will generate errors, because you have not enclosed string variables in the query in single quotes.(and you have missed one back-tick)

Collectives™ on Stack Overflow

SQL Near error for inserting data through HTML form

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related