PhP mysql insert syntax error

Question

Below I am getting a 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 'call, county, id, location, callcreated, station, units, calltype, lat, lng) VAL' at line 1, and cant figure out why! Any help would be greatly appreciated!

<?php

mysql_connect("localhost", "test", "test") or die(mysql_error());
mysql_select_db("firecom") or die(mysql_error());

$data = file_get_contents("http://208.71.205.35/PITS/");//thanks WCCCA!
$pattern = "/id=\"hidXMLID\" value=\"([^\"]+)\"/";//looking for the rnd xml id#
preg_match_all($pattern, $data, $xmlext);

$url = "http://208.71.205.35/PITS/xml/fire_data_" . $xmlext[1][0] . ".xml";//putting together the secret xml url
$xml = simplexml_load_file($url);

foreach ($xml->marker as $element) {

$lat = $element->attributes()->lat;
$lng = $element->attributes()->lng;
$countydirty = $element->AGENCY;// gets agency
$wcccanumberdirty = $element->CALL_NO;
$iddirty = $element->TWO_DIGIT_CALL_NO;// gets call id#
$calldirty = $element->CALL_TYPE_FINAL_D;// gets call type
$locationdirty = $element->LOCATION;// gets location
$callcreateddirty = $element->CALL_CREATED_DATE_TIME;
$stationdirty = $element->BEAT_OR_STATION;// get first marker station
$unitsdirty = $element->UNITS;// get first marker units
$calltypedirty = $element->TYPE; 

//this next section removes the "~" from the start of all the lines
$county = str_replace('~','',$countydirty);
$wcccanumber = str_replace('~','',$wcccanumberdirty);
$id = str_replace('~','',$iddirty);
$call = str_replace('~','',$calldirty);
$location = str_replace('~','',$locationdirty);
$callcreated = str_replace('~','',$callcreateddirty);
$station = str_replace('~','',$stationdirty);
$units = str_replace('~','',$unitsdirty);
$calltype = str_replace('~','',$calltypedirty);

mysql_query("INSERT INTO calls (wcccanumber, call, county, id, location, callcreated, station, units, calltype, lat, lng) VALUES('$wcccanumber', '$call', '$county', '$id', '$location', '$callcreated', '$station', '$units', '$calltype', '$lat', '$lng')") or die(mysql_error()); 

echo "$call - $county - $wcccanumber - $id - $location - $callcreated - $station - $units - $calltype <br />";
}

?>

I assume you have checked to be sure you have the column names right? — NappingRabbit
– NappingRabbit, Commented Dec 10, 2012 at 20:23

Kermit · Accepted Answer · 2012-12-10 20:21:38Z

5

call is a reserved word, it must be encased in back ticks:

INSERT INTO calls (wcccanumber, `call`, ...

answered Dec 10, 2012 at 20:21

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jeroen · Accepted Answer · 2012-12-10 20:21:46Z

2

call is a reserved word in mysql, so if you use it as a column name you need to quote it in backticks:

wcccanumber, `call`, county...

Apart from that you need to switch to PDO / mysqli and prepared statements to fix the potential sql injection problem you have.

answered Dec 10, 2012 at 20:21

jeroen

91.8k22 gold badges118 silver badges132 bronze badges

4 Comments

Jon Erickson Over a year ago

Thanks, that was it. What are you talking about the possible sql injection?

jeroen Over a year ago

@Jon Erickson If one of your fields contains for example a ' character, it will break your sql statement and this can be abused. Without changing to PDO or mysqli you should at least use mysql_real_escape_string on your variables before you add them to your query.

Jon Erickson Over a year ago

Alright, thanks. By the way, the XML document has no way to be abused. It is coming from a governmental agency where the URL to the xml link is constantly changing and completely random.

jeroen Over a year ago

@Jon Erickson Okay, but there still could be (valid) ' characters in the data and that will break your application.

Marc B · Accepted Answer · 2012-12-10 20:21:51Z

1

call is a reserved word. You'll have to quote it with backticks:

mysql_query("INSERT INTO calls (wcccanumber, `call`, county, id, ...

P.S. For a database problem (especially syntax errors), you don't need to include all of that DOM stuff. how you get the values for a query is pretty much always irrelevant.

answered Dec 10, 2012 at 20:21

Marc B

362k44 gold badges433 silver badges508 bronze badges

Collectives™ on Stack Overflow

PhP mysql insert syntax error

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related