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

Answer 1

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!".