mysql error :error in sql syntax

Question

i want to insert some lines of text(paragraph) in database that is coming from wikipedia page..but mysql is showing this error when i try to insert the data in db: "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 capital." can anyone help me to fix this problem.. here is what i have done so far...

 <?php
    $loc=$_POST["new"];
    $url1 ="https://en.wikipedia.org/w/api.php?format=json&action=query&prop=extracts&exintro=&explaintext=&titles=".$loc;
    $opf = file_get_contents($url1);
    $data = json_decode($opf, true);
    $titles = array();
    foreach ($data['query']['pages'] as $page) {
    $des = $page['extract'];

    }
    $con = mysql_connect("localhost","root","");
    if (!$con) { 
    die('Could not connect: ' . mysql_error());
     } 
    mysql_select_db("location", $con);
    $url = "http://upload.wikimedia.org/wikipedia";
    echo $sql="INSERT INTO `search`(`id`, `name`, `text`) VALUES ('$loc', '$des');";
    mysql_query($sql) or die(mysql_error()); 
    echo "1 record added";
    mysql_close($con);
    ?>

3 fields but only 2 values. if id is auto inc, just leave it out — , Mar 08 '15 at 19:44
And a SQL injection vulnerability that is also causing your error — John Conde, Mar 08 '15 at 19:45
sql injection vulnerability? how to fix it? @Dagon yes id is autoincrement..thats why — candy crush, Mar 08 '15 at 19:48

Leandro Papasidero · Answer 1 · 2015-03-09T15:41:23.287

Doesn't explain why it should work

You have 3 fields and 2 values.

doesn't fix their error

Yes, it does.

uses obsolete code, and is wide open to SQL injections

It isn’t my code. I am adapting OPs code, I am not trying to write it from scratch. Also, I guess, you forgot to mention that mysql function is deprecated since 5.5

Further, although the fact that the code is SQL injectable is good to mention it does not in my opinion constitute an actual answer. It's a comment at best. ie. "hey btw did you know you misspelled a word?" or some such. An editorial nitpick. If questions are going to be closed as duplicates of SQL injection questions then 80% of the questions here would have to be closed as dupes.

If the OPs wants to know about SQL injection please refer to this site

Oh, btw,this is the code:

<?php
$loc=$_POST["new"];
$url1 ="https://en.wikipedia.org/w/api.php?format=json&action=query&prop=extracts&exintro=&explaintext=&titles=".$loc;
$opf = file_get_contents($url1);
$data = json_decode($opf, true);
$titles = array();
foreach ($data['query']['pages'] as $page) {
$des = $page['extract'];

}
$con = mysql_connect("localhost","root","");
if (!$con) { 
die('Could not connect: ' . mysql_error());
 } 
mysql_select_db("location", $con);
$url = "http://upload.wikimedia.org/wikipedia";
echo $sql="INSERT INTO `search`(`name`, `text`) VALUES ('$loc', '$des');";
mysql_query($sql) or die(mysql_error()); 
echo "1 record added";
mysql_close($con);
?>

Doesn't explain *why* it should work, doesn't fix their error, uses obsolete code, and is wide open to SQL injections. — John Conde, Mar 08 '15 at 19:47
Thank you @JohnConde for you constructive feedback. I edited my answer, I hope this fulfil your expectations. — Leandro Papasidero, Mar 09 '15 at 15:42

redreddington · Accepted Answer · 2015-03-08T20:22:46.137

0

Ideally you should escape data before entering it into a database. The problem you have is the apostrophe is ending the SQL query on '$loc' so the query actually reads:

... VALUES ('Giant's Capital',

Syntax highlight should indicate why that's a problem :)

Use something like: mysql_real_escape_string() to escape your $_POST data before inputting.

$loc = mysql_real_escape_string($_POST['new']);

edited Mar 08 '15 at 20:22

answered Mar 08 '15 at 19:52

redreddington

408
2
12

1

means to replace $loc=$_POST["new"]; with $loc = mysql_real_escape_string($_POST['new']); ??? – candy crush Mar 08 '15 at 19:59
Yep, that's just me keeping variables the same. Force of habit. Will update answer. – redreddington Mar 08 '15 at 20:22

mysql error :error in sql syntax

2 Answers2