Sunday 3 December 2017

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

itemprop="text">



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




Remove all the quotes in (for your
columns)



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



Those
aren't the correct column
identifiers





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.





/>


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.



$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 rel="nofollow">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!".



No comments:

Post a Comment

php - file_get_contents shows unexpected output while reading a file

I want to output an inline jpg image as a base64 encoded string, however when I do this : $contents = file_get_contents($filename); print &q...