CODEWITHSUNDEEP
phpmysqlserver
Ryan
Ryan

Reputation: 1

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

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

Upvotes: 0

Views: 31525

Answers (4)

spencer7593
spencer7593

Reputation: 108480

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);

Upvotes: 0

Richard
Richard

Reputation: 2815

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.

Upvotes: 0

bugs2919
bugs2919

Reputation: 358

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

Upvotes: 1

Kirk Powell
Kirk Powell

Reputation: 910

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

Upvotes: 0

Related Questions