1

This has been driving me nuts all day - now I need some help please....

I have tried two ways to insert data into the database and both fail in ways i've not seen before!

Just using mysqli->query("...") - causes trailing commas at the end of the actual query as seen in the mysqld.general.log. There are as many extra commas as there are fields.

Using the mysqli->prepare method causes the actual prepare statement to appear in the log!

I include my code below along with what I see in the logs..

The global $mysqli object is created in an include file first - that definately works because select statements work fine...

Any help much appreciated.

CODE:-

function addplaces($placeID,$placename,$easting,$northing,$latitude,$longitude,$parish,$inuse,$shire)
{
 global $mysqli;

 $mysqli->query("INSERT INTO `places` (`placeID`,`placename`,`easting`,`northing`,`latitude`,`longitude`,`parish`,`inuse`,`shire`) VALUES ($placeID,$placename,$easting,$northing,$latitude,$longitude,$parish,$inuse,$shire)");

}

LOG OUTPUT:-

9594 Query INSERT INTO places (placeID,placename,easting,northing,latitude,longitude,parish,inuse,shire) VALUES ("12672","Accrington","375500","428500","53.752300262451","-2.3730099201202","Accrington","1",Lancashire,,,,,,,,) 9594 Quit

CODE:-

function addplaces($placeID,$placename,$easting,$northing,$latitude,$longitude,$parish,$inuse,$shire)
{
global $mysqli;
$stmt = $mysqli->prepare("INSERT INTO places (placeID,placename,easting,northing,latitude,longitude,parish,inuse,shire) VALUES (?,?,?,?,?,?,?,?,?)") or die ("Could not Prepare Statement");
$stmt->bind_param('sssssssss', $placeID,$placename,$easting,$northing,$latitude,$longitude,$parish,$inuse,$shire );
$stmt->execute();
}

LOG OUTPUT:-

9595 Prepare INSERT INTO places ( placeID, placename, easting, northing, latitude, longitude, parish, inuse, shire ) VALUES (?,?,?,?,?,?,?,?,?) 9595 Close stmt 9595 Quit

I have tried enclosing the Values fields in single and double quotes but still get the same result...

Also as this is my first post I'm not sure i've got the hang of this editor yet so please forgive me if I've got it wrong..

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
Bob Howlett
  • 33
  • 2
  • What steps are you taking to sanitize your values before the insert? Is it possible the ghost commas are coming from the $shire variable? – Matt R. Wilson Sep 15 '13 at 19:19
  • Echo out your `$shire` variable. – Ben Fortune Sep 15 '13 at 19:19
  • So the second block doesn't work? – Explosion Pills Sep 15 '13 at 19:23
  • 1
    **By building SQL statements with outside variables, you are leaving yourself wide open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php has examples to get you started, and [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. – Andy Lester Sep 15 '13 at 19:41
  • 2
    For future code: You don't need to quote/backtick your column & table names. They only add visual clutter and are just one more way for you to make syntax errors. The only reason you need them is if you have a column name that is a reserved word, and using column names that are reserved words is a terrible idea, so that's two bad habits you can avoid at once. – Andy Lester Sep 15 '13 at 19:41
  • The data comes from a trusted source so I am sure that there is no problem with rouge characters etc. Ihe data for this particular insert is shown in the code anyway and should work just fine surely.. The shire variable contains just the word "Lancashire" so it's not that that's causing the problem either! Strange.... – Bob Howlett Sep 15 '13 at 23:11
  • 1
    You are almost certainly mistaken about the contents of the variable $shire. Most likely you are reading this from a CSV source, there are extra blank fields at the end, and you're not parsing them off the value before using it. – Larry Lustig Sep 16 '13 at 04:42

1 Answers1

-1

Looks to me like your $shire variable does not have quotes around it.

Clint Priest
  • 210
  • 1
  • 11