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 Perspective

Question

Code:

$sql = "
INSERT INTO book
     ( id
     , account_no
     , admin_id
     , title
     , author
     , edition
     , book_publisher
     , book_copies
     , book_isbn
     , print_place
     , book_year
     , book_pages
     , book_price
     , entry_date
     ) VALUES 
     ( ''
     , '$account_id'
     , '$admin_id'
     , '$title'
     , '$author'
     , '$edition'
     , '$publisher'
     , '$copies'
     , '$isbn'
     , '$place'
     , '$year'
     , '$pages'
     , '$price'
     , '$date'
)
";

While inserting data using this query I am getting this 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 Perspective','Randal E. Bryant','2','Prentice Hall','2','978-0136108047','USA'' at line 3

can anyone tell how to resolve this error?

Obviously there's some value like `Someone's Perspective` which ruins query. Use PDO, prepared statements, escape data etc. — u_mulder, Oct 08 '14 at 08:21
Are you sure the id column is not set to auto_increment in the database? If it is, exclude from both the tables, and the values list. — Alex Szabo, Oct 08 '14 at 08:22
use `mysqli_real_escape_string()` function for the variable which has value `'s Perspective` ?? — TBI, Oct 08 '14 at 08:22
possible duplicate of [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — andy, Oct 08 '14 at 08:24
@TBI I think we've moved on from that. See prepared statements. — Strawberry, Oct 08 '14 at 08:28
@Arif_suhail_123 The error is an unescaped apostrophe in a string which contains " 's Perspective ", E.g. " A Teacher's Perspective" — Strawberry, Oct 08 '14 at 08:57

score 1 · Answer 1 · answered Oct 08 '14 at 08:23

1

Name of the book "A Programmer's Perspective" contains "'" character, so you have to escape this variable with appropriate function: http://php.net/manual/ru/function.mysql-escape-string.php for example, in case of PHP

answered Oct 08 '14 at 08:23

SuddenHead

1,463
8
8

score 1 · Answer 2 · answered Oct 08 '14 at 09:10

You can try replacing ' with \". Sample for publisher field below. Just apply the same for other field which will have possible ' or ".

$sql = "
INSERT INTO book
     ( id
     , account_no
     , admin_id
     , title
     , author
     , edition
     , book_publisher
     , book_copies
     , book_isbn
     , print_place
     , book_year
     , book_pages
     , book_price
     , entry_date
     ) VALUES 
     ( ''
     , '$account_id'
     , '$admin_id'
     , '$title'
     , '$author'
     , '$edition'
     , \"$publisher\"
     , '$copies'
     , '$isbn'
     , '$place'
     , '$year'
     , '$pages'
     , '$price'
     , '$date'
)
";

score 0 · Accepted Answer · answered Oct 08 '14 at 09:36

0

I just used this function of mysql to insert the value

mysql_real_escape_string()

For Example:

$title = mysql_real_escape_string($_POST['title']);

it worked...

answered Oct 08 '14 at 09:36

Shhzdmrz

45
10

score -2 · Answer 4 · answered Oct 08 '14 at 08:24

try this without insert id value:

$sql = "INSERT INTO `book`
(`account_no`, `admin_id`, `title`, `author`, `edition`, `book_publisher`, `book_copies`, `book_isbn`, `print_place`, `book_year`, `book_pages`, `book_price`, `entry_date`) 
VALUES ('$account_id','$admin_id','$title','$author','$edition','$publisher','$copies','$isbn','$place','$year','$pages','$price','$date')";

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 Perspective

4 Answers4