-1

So I have this script that takes a bunch of json files and sends them to my database. The script works fine for 8777 of the files, but for 70 of the files I get the following mysql syntax 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 'Shea', '', '190', '1981-4-30', 'Right', '106', '13', '25', '65', '39', '64', '61' at line 1

Here is the loop I am using, and I apologize for the long query:

For($id=1; $id<=205600; $id++)
{
if (file_exists('data/players/'.$id.'.json'))
    {
        //read json file
        $json_file = file_get_contents('data/players/'.$id.'.json');
        $file_data = json_decode($json_file, true);

        //my super long query
        $query =  'INSERT INTO `db`.`table` (`id`, `first_name`, `last_name`, `common_name`, `height`, `dob`, `foot`, `club_id`, `league_id`, `nation_id`, `attribute1`, `attribute2`, `attribute3`, `attribute4`, `attribute5`, `attribute6`, `rare`, `rating`, `type`) VALUES (\''.$id.'\', \''.$file_data['Player']['FirstName'].'\', \''.$file_data['Player']['LastName'].'\', \''.$file_data['Player']['CommonName'].'\', \''.$file_data['Player']['Height'].'\', \''.$dob.'\', \''.$file_data['Player']['PreferredFoot'].'\', \''.$file_data['Player']['ClubId'].'\', \''.$file_data['Player']['LeagueId'].'\', \''.$file_data['Player']['NationId'].'\', \''.$file_data['Player']['Attribute1'].'\', \''.$file_data['Player']['Attribute2'].'\', \''.$file_data['Player']['Attribute3'].'\', \''.$file_data['Player']['Attribute4'].'\', \''.$file_data['Player']['Attribute5'].'\', \''.$file_data['Player']['Attribute6'].'\', \''.$file_data['Player']['Rare'].'\', \''.$file_data['Player']['Rating'].'\', \''.$file_data['Player']['ItemType'].'\');';
        mysql_query($query);

        //record loop status
        if (mysql_error())
        {
            $error = mysql_error();
            echo $error.'<br/>';
            $errors ++;
        }
        else
        {
            $entries ++;
        }
    }
}

So how can I resolve this error?

Steven Baltay
  • 514
  • 1
  • 4
  • 15
  • I would presume those 70 files have some formatting or character(s) present that the other 8707 don't. Do you know what it is? – Jon Newmuis Aug 01 '12 at 22:42
  • if `'Shea', '', '190', '1981-4-30', 'Right',` is supposed to correspond to ``id`, `first_name`, `last_name`, `common_name`, `height`` I think something is wrong in the field order, or in some records a field may be missing? – fvu Aug 01 '12 at 22:45
  • 1
    I was just going through the list and most of the names seem irish. so maybe theres an ' in there last name like O'shea and O'Connor – Steven Baltay Aug 01 '12 at 22:46
  • Part of the Irish bail-out money should go to eradicating `mysql_query`, don't you think? – tadman Aug 01 '12 at 22:56

2 Answers2

5

Use mysql_real_escape_string on your variables before adding them to a query:

$commonName = mysql_real_escape($file_data['Player']['CommonName']);

Then use $commonName in your query.

Although you really should be using PDO. See First steps with PDO for an introduction.

Michael Robinson
  • 27,775
  • 10
  • 102
  • 126
4

Escaping your SQL would go a long way towards fixing this.

If you're using this in production you're lucky you have that many successful transactions.

A fixed up example looks more like this:

$dbh = new PDO(...);

$stmt = $dbh->prepare('INSERT INTO `db`.`table` (`id`, `first_name`, `last_name`, ...)
  VALUES (:id, :Player_FirstName, :Player_LastName, ...)');
$stmt->bindParam(':id', $id);
$stmt->bindParam(':Player_FirstName', $file_data['Player']['FirstName']);
$stmt->bindParam(':Player_LastName', $file_data['Player']['LastName']);
// ... Etc...

// insert one row
$stmt->execute();
tadman
  • 194,930
  • 21
  • 217
  • 240