Mysql Insert Error can not find the issue

Question

Hi I am using the code below, and have been through it lots of times but can not see why this isn't working, I think it is just a case of not being able to see the problem for looking, so I would be grateful if somebody else could have a look and identitfy the problem.

this is the error I am getting;

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
'Azur', 'Antibes', '43.5624', '7.1278', '', '', 'Europe/Paris', 'EU', '', 'Amadeu'
at line 6

And here is the code:

  if (!isset($params['i'])) $params['i'] = '82.150.248.29';

  $query = 'https://geoip.maxmind.com/e?' . http_build_query($params);

  $omni_keys = 
  array(
    'country_code',
    'country_name',
    'region_code',
    'region_name',
    'city_name',
    'latitude',
    'longitude',
    'metro_code',
    'area_code',
    'time_zone',
    'continent_code',
    'postal_code',
    'isp_name',
    'organization_name',
    'domain',
    'as_number',
    'netspeed',
    'user_type',
    'accuracy_radius',
    'country_confidence',
    'city_confidence',
    'region_confidence',
    'postal_confidence',
    'error'
    );

   $curl = curl_init();
   curl_setopt_array( $curl, 
               array(
                     CURLOPT_URL => $query,
                     CURLOPT_USERAGENT => 'MaxMind PHP Sample',
                     CURLOPT_RETURNTRANSFER => true
                     )
               );

               $resp = curl_exec($curl);

               if (curl_errno($curl)) {
               throw new Exception('GeoIP Request Failed');
               }

            $omni_values = str_getcsv($resp);
            $omni = array_combine( $omni_keys, $omni_values);

           //print_r($omni_values);

          $country_code= $omni['country_code'];
          $country_name= $omni['country_name']; 
          $region_code= $omni['region_code']; 
          $region_name= $omni['region_name']; 
          $city_name= $omni['city_name']; 
          $latitude= $omni['latitude']; 

          $longitude= $omni['longitude'];
          $metro_code= $omni['metro_code']; 
          $area_code= $omni['area_code']; 
          $time_zone= $omni['time_zone']; 
          $continent_code= $omni['continent_code']; 
          $postal_code= $omni['postal_code']; 

          $isp_name= $omni['isp_name'];
          $organization_name= $omni['organization_name']; 
          $domain= $omni['domain']; 
          $as_number= $omni['as_number']; 
          $netspeed= $omni['netspeed']; 
          $user_type= $omni['user_type']; 

          $accuracy_radius= $omni['accuracy_radius'];
          $country_confidence= $omni['country_confidence']; 
          $city_confidence = $omni['city_confidence']; 
          $region_confidence= $omni['region_confidence']; 
          $postal_confidence= $omni['postal_confidence']; 
          $error= $omni['error'];

          //Insert to MasterTable
          mysql_query("INSERT INTO mastertable 
          (country_code, country_name, region_code, region_name, city_name, latitude,           longitude, metro_code, area_code, time_zone, continent, postal_code, isp_name, organisation, domain_name, as_number, netspeed, user_type, accuracy_radius, country_confidence_factor, city_confidence_factor, region_confidence_factor, postal_code_confidence_factor, error) 

        VALUES

        ('$country_code', '$country_name', '$region_code', '$region_name', '$city_name', '$latitude', '$longitude', '$metro_code', '$area_code', '$time_zone', '$continent_code', '$postal_code', '$isp_name', '$organization_name', '$domain', '$as_number', '$netspeed', '$user_type', '$accuracy_radius', '$country_confidence', '$city_confidence', '$region_confidence', '$postal_confidence', '$error' ) ") 
or die(mysql_error());

There is something about using `mysql_*` functions .. hmm can't remember .. — dbf, Apr 18 '13 at 11:35
[Please, don't use `mysql_*` functions](http://stackoverflow.com/q/12859942/1190388) in new code. They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the red box? Learn about prepared statements instead, and use [tag:PDO] or [tag:MySQLi]. — hjpotter92, Apr 18 '13 at 11:37
I think we should stop warning users about `mysql_()`, no one listens anyways — Mr. Alien, Apr 18 '13 at 11:37
check the query before using mysql_query. try echoing query before mysql_query and use that query in database....that will give you proper error. — Shwet, Apr 18 '13 at 11:38
First - switch to PDO or mysqli_* as mysql_* is now deprecated. Second, echo out your full SQL statement and see if there is anything obvious wrong with it. I don't see much attempt at ensuring your variables are safe, either. — GarethL, Apr 18 '13 at 11:39
@hjpotter92 I don't think kids listen to their parents either — Mr. Alien, Apr 18 '13 at 11:39
Instead of `mysqli_()` or `PDO` we should post comments for sanitizing the data and SQL injections — Mr. Alien, Apr 18 '13 at 11:41
@Evan but it's a serious issue on SO, the comment space being used for people warning about functions they should absolutely not use is ridiculous compared to these _few_ comments .. — dbf, Apr 18 '13 at 11:41
@dbf But if the OP's *really* cared, don't you think they would see the ***huge*** red warnings at the top of each `mysql*` page? I sure do .. — What have you tried, Apr 18 '13 at 11:43
@Evan if every OP would care that much, SO wouldn't be a mess (done). — dbf, Apr 18 '13 at 11:45
@Evan http://meta.stackexchange.com/questions/143379/automatically-warn-against-the-use-of-mysql-functions-on-stack-overflow — Mr. Alien, Apr 18 '13 at 11:46

score 5 · Answer 1 · edited Apr 18 '13 at 11:40

5

It's because you need to escape your data before inserting ! For example it's seems you try to insert 'cotes d'azur' but the ' in cotes d'azur is not escaped

edited Apr 18 '13 at 11:40

Yogesh Suthar

30,424
18
72
100

answered Apr 18 '13 at 11:39

AFrenchDev

151
7

1

+1 Because I like seeing your score go up by 400% in a single question :) – What have you tried Apr 18 '13 at 11:53

score 2 · Accepted Answer · answered Apr 18 '13 at 11:38

2

Change all the following(similar) statements:

$country_code= $omni['country_code'];

to this:

$country_code= mysql_real_escape_string( $omni['country_code'] );

answered Apr 18 '13 at 11:38

hjpotter92

78,589
36
144
183

1

@user1691024 Actually there was no explanation, the reason why you need to use `mysql_real_escape_string()` is to escape quotes :) – Mr. Alien Apr 18 '13 at 12:03

score 0 · Answer 3 · answered Apr 18 '13 at 11:47

0

after using

 $omni = array_combine( $omni_keys, $omni_values);

add this line

 $omni = array_map('addslashes', $omni);

it will sort out your problem

see the mysql_real_escape_string as well.

answered Apr 18 '13 at 11:47

Muhammad

3,169
5
41
70

score 0 · Answer 4 · answered Apr 18 '13 at 11:49

0

Well, the question seems to be answered correctly but anyhow:

Why the ... are you asigning each named member of your aray to a variable with the same name? That's assumably not best practice.

answered Apr 18 '13 at 11:49

6bb6

16
1

Mysql Insert Error can not find the issue

4 Answers4