Mysql error.Error in the syntax of the sql query

Question

The code below is giving an error.The query alone is working fine on phpmyadmin directly but but in my php code:

Database 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 '\'to\',\' message\',\'from\')' at line.. but i cannot figure out why?

<?php
$con = mysqli_connect("localhost","","","");
if (!$con)
  {
     echo" Not connected to database";
  die('Could not connect: ' . mysqli_error());
  }
if(isset($_POST['submit'])){
$username=$_SESSION["username"];
$sql = "INSERT INTO `dbase.mail`(`Date`, `to`, `message`, `from`) VALUES (CURDATE(),\'$_POST[username1]\',\'$_POST[message]\',\'$username\')";
$xy=mysqli_query($con,$sql);
if (!$xy)
  {
  die('Database Error ' . mysqli_error($con));
  }
echo "Your message is stored";


}

Please `var_dump($sql);` to show what the `INSERT`-statement looks like. Possibly one of the `$_POST` variables is not as expect it to be. By the way, never use `$_POST` like this, always sanitize it before using them in a query. — Bjoern, Jan 25 '14 at 19:58
try "INSERT INTO mail values (CURDATE(),\\'$_POST[username1]\\',\\'$_POST[message]\\',\\'$username\\')"; or Tell me what value do u want to insert ... — Ashish Ratan, Jan 25 '14 at 19:59
I used the above but its still showing the error.On using vardump() Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in xxx.php bool(false) Database 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 '\'dkand\',\'smdald \',\'username\')' at line 1 — reaper1, Jan 25 '14 at 20:05
I feel I must point out: [use prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — Rob Apodaca, Jan 25 '14 at 20:10

score 3 · Accepted Answer · edited May 23 '17 at 10:31

Several problems:

You must delimit the database name and table separately.

INSERT INTO `dbase`.`mail` -- RIGHT

not this:

INSERT INTO `dbase.mail` -- WRONG

You don't need to backslash single-quotes inside a double-quoted string.
```
$string = "that's the way"; -- RIGHT
```
not this:
```
$string = "that\'s the way"; -- WRONG
```

The error message suggests that you are using straight single-quotes to delimit your columns, not back-ticks.

INSERT INTO `dbase`.`mail`(`Date`, `to`, `message`, `from`) -- RIGHT

not this:

INSERT INTO 'dbase.mail'('Date', 'to', 'message', 'from') -- WRONG

You must not interpolate $_POST variables directly into your SQL! This will allow hackers to attack your website easily. See What is SQL injection? and How can I prevent SQL injection in PHP?

This is not the source of the error you asked about in this question, but it's a security practice you must learn how to handle properly before you put your code on the internet. If you were an electrician, this is analogous to safe wiring to prevent fires.

Mysql error.Error in the syntax of the sql query

1 Answers1