Error: You have an error in your SQL syntax; near ')' at line 1

Question

Im having a problem with my PHP code, it says the error is "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 ')' at line 1"

It connects to the database ok as it echos "Database Connection Successful" but it dosnt insert the data into the database. This worked fine before, but now all of a sudden its stopped working. Can anyone help?

<?php   
$username = "student";
$password = "student";
$hostname = "localhost"; 
$db       = "details";
$link = new mysqli($hostname, $username, $password, $db);

 if ($link->connect_errno)
    printf("Connect failed: %s\n", $link->connect_error);
 else 
    echo "Database Connection Successful \n";

 echo nl2br("\n");

 $Urgency = "Urgency";

if(isset($_POST['submit'])){
  $TypeOfProblem = $_POST['problemtype'];
  $ProblemDescription = $_POST['problem'];
  $RoomNo = $_POST['roomno'];
  $Problem = $_POST['reporter'];
  $Urgency = $_POST['Urgency'];
  $Date = $_POST['date'];

 //Insert into Database
  $sql = "INSERT INTO `details`.`problem` (`Type Of Problem`, `Problem Description`, `RoomNo`, `Urgency`, `UserIDProblem`,`Date` ) VALUES ('$TypeOfProblem', '$ProblemDescription', '$RoomNo', '$Urgency', '$Problem', $Date)";

if (!mysqli_query($link, $sql))
       {
       die('Error: ' . mysqli_error($link));
       }

     echo "\n Thank you. Your Helpdesk Call has been submitted.";

    mysqli_close($link);

}//////// end isset submit if ////////
 ?>

Thanks

Your date needs to be inside quotes. Also, sql injection warning. Learn to bind your queries. It is easy and helps. — Jonathan Kuhn
– Jonathan Kuhn, Commented May 19, 2015 at 20:27
Try to set an echo after the $sql = echo $sql; I think also that the problem is the single quote details.problem (` should be 'details'.'problem' (' — bugs2919
– bugs2919, Commented May 19, 2015 at 20:28
You are vulnerable to sql injection attacks, and you should show us what $sql looks like. Most likely $Date is empty, leaving you with ...,'foo',), which is invalid. — Marc B
– Marc B, Commented May 19, 2015 at 20:29

bugs2919 · Accepted Answer · 2015-05-19 20:30:24Z

1

Try using this, the problem is the single quote ` should be '

 $sql = "INSERT INTO 'details'.'problem' ('Type Of Problem', 'Problem Description', 'RoomNo', 'Urgency', 'UserIDProblem','Date' ) VALUES ('$TypeOfProblem', '$ProblemDescription', '$RoomNo', '$Urgency', '$Problem', '$Date')"

Or try to set an echo $sql and test the query directly on de dbms

answered May 19, 2015 at 20:30

bugs2919

3581 silver badge8 bronze badges

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

Comments

Kirk Powell · Accepted Answer · 2015-05-19 20:30:16Z

0

The date '$Problem', $Date)"; needs single-quotes '$Problem', '$Date')";

answered May 19, 2015 at 20:30

Kirk Powell

9089 silver badges14 bronze badges

Comments

Richard · Accepted Answer · 2015-05-19 20:32:10Z

0

First, it is a good idea to leave out the database name:

$sql = "INSERT INTO `problem` (`Type Of Problem`, `Problem Description`, `RoomNo`, `Urgency`, `UserIDProblem`, `Date`) VALUES ('$TypeOfProblem', '$ProblemDescription', '$RoomNo', '$Urgency', '$Problem', $Date)";

Are you sure, that your column names have spaces in it? I mean this would work, but this is not a good idea, I think.

I cannot find another problem in your query, maybe you should quote the date:

$sql = "INSERT INTO `problem` (`Type Of Problem`, `Problem Description`, `RoomNo`, `Urgency`, `UserIDProblem`, `Date`) VALUES ('$TypeOfProblem', '$ProblemDescription', '$RoomNo', '$Urgency', '$Problem', '$Date')";

Otherwise, please provide us with the full query:

die("INSERT INTO `problem` (`Type Of Problem`, `Problem Description`, `RoomNo`, `Urgency`, `UserIDProblem`, `Date`) VALUES ('$TypeOfProblem', '$ProblemDescription', '$RoomNo', '$Urgency', '$Problem', $Date)");

And you SHOULD notice, that your code is exploitable with SQL-Injections! Use mysqli_real_escape_string.

answered May 19, 2015 at 20:32

Richard

2,8154 gold badges27 silver badges37 bronze badges

4 Comments

Jay Blanchard Over a year ago

Leaving out the database name is not a good idea. Why in the world would that be a good idea?

Richard Over a year ago

@JayBlanchard: The database name is already specified with the MySQLi connect.

Jay Blanchard Over a year ago

True, but that doesn't mean you have to leave it out, right?

Richard Over a year ago

@JayBlanchard: You do not have to, but why should you? Should it be necessary to change hundreds of positions instead of one, if you change the database name?

spencer7593 · Accepted Answer · 2015-05-19 20:50:09Z

For debugging this, output the actual SQL text that is being submitted to the database, using echo or vardump e.g.

$sql = "INSERT INTO ...";
echo "SQL=" . $sql ;

That will show you the actual statement that's going to be submitted to the database, and you can usually debug the problem from there.

If date isn't a numeric, if it represents a DATE datatype or a string, the value needs to be enclosed in single quotes. Otherwise, it's likely going to be interpreted in a numeric context.

Note that this code appears to be vulnerable to SQL Injection, because it includes potentially unsafe values in the SQL text. Consider what happens when a value contains "special" characters, like a single quote, or comma.

Potentially unsafe values must be properly escaped. With mysqli, you can use the mysqli_real_escape_string function.

A better pattern is to use a prepared statement with bind placeholders.

As an example of what that would look like (before it's cluttered up with code to checks for errors from the return of the mysqli_ function calls)

$sql = "INSERT INTO `details`.`problem`
       (`Type Of Problem`,`Problem Description`,`RoomNo`,`Urgency`,`UserIDProblem`,`Date`)
       VALUES (?,?,?,?,?,?)";

$sth = mysqli_prepare($link,$sql);
if (!$sth) {
   echo "error:" . mysqli_error($link);
)

mysqli_stmt_bind_param($sth,"ssssss"
    ,$TypeOfProblem,$ProblemDescription,$RoomNo,$Urgency,$Problem,$Date); 

mysqli_stmt_execute($sth);

Collectives™ on Stack Overflow

Error: You have an error in your SQL syntax; near ')' at line 1

4 Answers 4

Comments

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related