INSERT fails on server with magic_quotes on

Question

Ive been struggling to get my data to insert into my table. The data is pulled from an xml file into an array and echo'ing the results are fine but it fails on insertion.

 $dbhost = 'XXXX';
 $dbuser = 'XXXX';
 $dbpass = 'XXXX';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

if($conn)
 {
echo "Database Connection Successfull...<br /><br />";
 }

$dbname = 'a4027212_footy';
mysql_select_db($dbname) or die('Couldnt connect to database table');

if($dbname)
{
echo "Database ".$dbname." Selected..<br /><br />";
}

  $tweetmeme = "http://api.tweetmeme.com/stories/popular.xml?category=sports-soccer&amp;count=30" ; 

  $xml = @simplexml_load_file($tweetmeme) or die ("no file loaded") ; 
  if($xml)
  {
echo "Tweetmeme XML loaded with ".count($xml->stories->story)." stories in the file..<br /><br />";
  }
 if(get_magic_quotes_gpc())
 {
echo "Magic Quotes is ON<br /><br />";
 }
foreach($xml->stories->story as $story)
{
    $title=$story->title;
$title=mysql_real_escape_string($title);

$url=$story->url;
$url=mysql_real_escape_string($url);

$media_type=$story->media_type;
$media_type=mysql_real_escape_string($media_type);

$created=$story->created_at;
$created=mysql_real_escape_string($created);


$url_count=$story->url_count;
$url_count=mysql_real_escape_string($url_count);

$comment_count=$story->comment_count;
$comment_count=mysql_real_escape_string($comment_count);

$excerpt=$story->excerpt;
$excerpt=mysql_real_escape_string($excerpt);

$sql = "INSERT INTO ft_tweets (title,url,media_type,created_at,mention_count,comment_count,excerpt) VALUES ($title,$url,$media_type,$created,$url_count,$comment_count,$excerpt)";

$result = mysql_query($sql) or die(mysql_error());
if($result)
{
echo "added to database<br />";
}
}
echo "<br /><br />";

ive been told that insertion will fail if there are special characters in the string and mysql_real_escape_string() would help but it hasnt. Ive tried with and without escaping to no avail.

the error message returned is: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 'off. Ankle looks bad here. on Twitpic),mysql_real_escape_string(http://twitpic.c' at line 1

and it fails on the sql insertion.

Answer 1

$sql = "INSERT INTO ft_tweets (title,url,media_type,created_at,mention_count,comment_count,excerpt) VALUES ($title,$url,$media_type,$created,$url_count,$comment_count,$excerpt)";

Where are the single quotes around the $title , $url etc? It should be:

$sql = "INSERT INTO ft_tweets (title,url,media_type,created_at,mention_count,comment_count,excerpt) VALUES ('$title','$url','$media_type','$created','$url_count','$comment_count','$excerpt')";

Ofcourse you can ignore the single quotes for numerical fields.

Answer 2

Right where that "off" begins is the character you're having trouble with. I'd echo out the $sql first, followed by an quit(); command. Then, you'd see the missing quotes around the strings as Sabeen mentions.

I also found this that references the server having magic quotes turned on:

<?php
function check_input($value)
{
// Stripslashes
if (get_magic_quotes_gpc())
  {
  $value = stripslashes($value);
  }
// Quote if not a number
if (!is_numeric($value))
  {
  $value = "'" . mysql_real_escape_string($value) . "'";
  }
return $value;
}