MySQL Error in SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

Question

I am trying to insert a sample blog post into my 'posts' table in MySQL (using PHP) however I receive a syntax error whenever a large character post is submitted. If I submit content of say 20 characters it works but something like 500 characters will throw the following 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 ''uid', 'username', 'p_date', 'title', 'content') VALUES('1','Mark Twain', '2014-' at line 1

The 'content' is to be inserted into the database via a varchar(1000) variable. The table is defined in mysql as:

CREATE TABLE posts
(
    pid int NOT NULL AUTO_INCREMENT,
    uid int NOT NULL,
    username varchar(100) NOT NULL,
    p_date date NOT NULL,
    title varchar(225) NOT NULL,
    content varchar(10000) NOT NULL,
    PRIMARY KEY(pid),
    FOREIGN KEY(uid) REFERENCES users(uid)
);

The actual content I am trying to submit is this:

Secondly, these missionaries would gradually, and without creating suspicion or exciting alarm, introduce a rudimentary cleanliness among the nobility, and from them it would work down to the people, if the priests could be kept quiet. This would undermine the Church. I mean would be a step toward that. Next, education -- next, freedom -- and then she would begin to crumble. It being my conviction that any Established Church is an established crime, an established slave-pen, I had no scruples, but was willing to assail it in any way or with any weapon that promised to hurt it. Why, in my own former day -- in remote centuries not yet stirring in the womb of time -- there were old Englishmen who imagined that they had been born in a free country: a "free" country with the Corporation Act and the Test still in force in it -- timbers propped against men's liberties and dishonored consciences to shore up an Established Anachronism with.

The insert statement for this is the following:

$sql = "INSERT INTO posts ('uid', 'username', 'p_date', 'title', 'content') VALUES('$uid','$uname', '$date', '$title', '$content')";

if(!mysql_query($sql,$con)){
    echo "Oops! Something went wrong during the posting process. Please try again. ";
    die('Error: ' . mysql_error($con));
    header('Refresh: 1; URL=postingform.php');      
}else{
    // Now return the user to their post page 
    header('Refresh: 0; URL=postlist.php?uid='.$uid.'');
}

For some reason it is error-ing out during the INSERT process. The one thing strange I notice is that the date is cut off in the error. To call the date I am using. $date = date("Y-m-d");

I have used this same syntax before without issues.

****Edit

A few posters have pointed out that there are single quotations in my INSERT column statements. I have changed these to back tics and completely removed them but the error still results.

New 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 's Court', 'Secondly, these missionaries would gradually, and without creating su' at line 1

There is something still wrong with my insert syntax but everything I am reading says it should be correct.

$sql = "INSERT INTO posts (`uid`, `username`, `p_date`, `title`, `content`) VALUES('$uid','$uname', '$p_date', '$title', '$content')";

Solution

Remove all the quotes in (for your columns)

('uid', 'username', 'p_date', 'title', 'content')

Those aren't the correct column identifiers

• http://dev.mysql.com/doc/refman/5.5/en/identifiers.html

use

(uid, username, p_date, title, content)

or use backticks.

(`uid`, `username`, `p_date`, `title`, `content`)

However and as a quick FYI, backticks are mostly used for reserved keywords, or if a table/column contains spaces, hyphens.

• http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

---

The error message was letting you know here

check the manual that corresponds to your MySQL server version for the right syntax to use near ''uid',
^--« right there

Notice the quote just before 'uid'? That's where the problem starts.

---

Edit:

Try the following using prepared statements and replace xxx with your own credentials.

This should take care of the quotes issue from your input values.

You will need to add the variables according to your inputs.

<?php
$DB_HOST = "xxx";
$DB_NAME = "xxx";
$DB_USER = "xxx";
$DB_PASS = "xxx";

$conn = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($conn->connect_errno > 0) {
  die('Connection failed [' . $conn->connect_error . ']');
}

$uid = ""; // replace with proper value
$uname = ""; // replace with proper value
$date = ""; // replace with proper value
$title = ""; // replace with proper value
$content = ""; // replace with proper value

$stmt = $conn->prepare("INSERT INTO posts (`uid`, `username`, `p_date`, `title`, `content`) VALUES (?, ?, ?, ?, ?)");

$stmt->bind_param('sssss', $uid, $uname, $date, $title, $content);

    if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
    }

        else{
          echo "Success";
        }

$stmt->close(); // Statement
$conn->close(); // MySQLi

---

Footnotes:

In order to allow single and/or double quotes, based yourself on the following, while using the stripslashes() function.

$content = stripslashes($_POST['content']);

This will enter in DB properly:
Bob's sister was here today and said: "Bob, what lovely hair you have!".

Comments

[0000101010]

Still no luck. I have removing quotes and backtics and the error that results when entering the information 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 's Court', 'Secondly, these missionaries would gradually, and without creating su' at line 1

[Funk Forty Niner]

@user3055632 Try using mysql_real_escape_string() for the values. I.e.: mysql_real_escape_string($content) or mysql_real_escape_string($_POST['content']) etc. depending on the form's element name. Or this sometimes helps ('".$uid."','$uname."', '".$date."', '".$title."', '".$content."') you may need to adjust for $uid

[Funk Forty Niner]

@user3055632 I suggest you switch over to either mysqli_ or PDO. It will facilitate the task of dealing with quotes in your values. See the following links mysqli_* with prepared statements, or PDO with prepared statements.

[Funk Forty Niner]

@user3055632 I've put together a prepared statements method for you. Look under my Edit: that should take care of the issues with the quotes from input values.

[Funk Forty Niner]

@user3055632 I also added something else; see Footnotes and go over my entire answer carefully. Entering Bob's sister was here today and said: "Bob, what lovely hair you have!". into a POST form method, was successful and showed up exactly like that when tested.

[0000101010]

Thank you for the well written response. Looks like it was the quotations marks that were causing this error. I thought I had covered this by sanitizing my data beforehand using $data = stripslashes($data); $data = htmlspecialchars($data); To fix the problem while still using good old mysql_ syntax I just had to run my data through the mysql_real_escape_string(); function. This isn't the best way to do it but it will work for now. Cheers!

[Funk Forty Niner]

@user3055632 You're very much welcome and glad to hear that it's been resolved, cheers!