only one field not working, sql syntax error

Question

iv'e created a database in mySQL with the following as mentioned in the code :

<?php

// Create connection
$con=mysqli_connect("myURL","myUSER","myPassword","NewActivities");

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }



$sql="INSERT INTO NewActivities1 (address, title, description, area, category, frequency, when)
VALUES
('$_POST[address]','$_POST[title]','$_POST[description]','$_POST[area]','$_POST[category]','$_POST[frequency]','$_POST[when]')";
if (!mysqli_query($con,$sql))
  {
  die('Error: ' . mysqli_error($con));
  }
echo "1 record added";
mysqli_close($con);

?>

i keep getting this error: Error: 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) VALUES('fff','fff','fff','Stockholm','Activities','One time','fff')' at line 1

if i removed the "when" from the query the database gets updated in the mentioned fields.

the field "when" in the form is defined with the attribute "name="when""

what may seem to be the problem?

thank you

Answer 1

WHEN is a Reserved Word. As documented under Schema Object Names:

If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it. (Exception: A reserved word that follows a period in a qualified name must be an identifier, so it need not be quoted.)

[ deletia ]

The identifier quote character is the backtick (“`”):

mysql> SELECT * FROM `select` WHERE `select`.id > 100;

If the ANSI_QUOTES SQL mode is enabled, it is also permissible to quote identifiers within double quotation marks:

mysql> CREATE TABLE "test" (col INT);
ERROR 1064: You have an error in your SQL syntax...
mysql> SET sql_mode='ANSI_QUOTES';
mysql> CREATE TABLE "test" (col INT);
Query OK, 0 rows affected (0.00 sec)

Therefore:

$sql="INSERT INTO NewActivities1 (address, title, description, area, category, frequency, `when`)

You also really ought to read up on proper string escaping (and how failure to do so exposes your application both to bugs and commonly exploited attack vectors): I thoroughly recommend @deceze's blog article, The Great Escapism (Or: What You Need To Know To Work With Text Within Text).

In particular, as he describes in his article, concatenating unescaped strings supplied by the user directly into your SQL not only makes you vulnerable to SQL injection attack, but furthermore introduces bugs where the strings contain characters that have special meaning within SQL string literals (for example ').

The solution is to prepare SQL statements with placeholders for parameters that get substituted with your variables upon command execution.