PHP to mySQL Query Fault

Question

I get this error:

Query failed: 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 ' ,,)' at line 2

On this PHP code:

<?php

$id = $_POST['id'];
$longitude = $_POST['longitude'];
$latitude = $_POST['latitude'];
$timestamp = $_POST['stringFromDate'];



$link = mysql_connect('server', 'user', 'pass')
or die('Could not connect: ' . mysql_error());

mysql_select_db('db_name') or die('Could not select database');

// Performing SQL query
$query="INSERT INTO locatie (id, longitude, latitude, timestamp) 
VALUES ($id, $longitude,$latitude,$timestamp)";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
echo "OK";

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);
?>

Im a beginner, so i dont know what im doing wrong

EDIT: This is the code that writes to the php file:

 - (void)myFuntionThatWritesToDatabaseInBackgroundWithLatitude:(NSString *)latitude  longitude:(NSString *)longitude date:

 (NSString *)stringFromDate {


_phonenumber = [[NSUserDefaults standardUserDefaults] objectForKey:@"phoneNumber"];

NSMutableString *postString = [NSMutableString stringWithString:kPostURL];
NSString*jsonString = [[NSString alloc] initWithFormat:@"{\"id\":\"%@\",\"longitude\":\"%@\",\"latitude\":\"%@\",\"timestamp\":\"%@\"}",_phonenumber, longitude , latitude, stringFromDate];

[postString appendString:[NSString stringWithFormat:@"?data=%@", jsonString]];
[postString setString:[postString stringByAddingPercentEscapesUsingEncoding:NSUTF8StringEncoding]];
NSMutableURLRequest *request = [[NSMutableURLRequest alloc] initWithURL:[NSURL URLWithString:postString ]];
[request setHTTPMethod:@"POST"];

[[NSURLConnection alloc] initWithRequest:request delegate:self ];
NSLog(@"Post String =%@", postString);


//    LocationTestViewController*locationTestViewController = [[LocationTestViewController alloc]init];
//    phonenumber = locationTestViewController.telefoonnummer;
NSLog(@"HERE1 : %@", _phonenumber);





 }

Your variables don't contain the values you expect them to, probably because `$_POST` is empty. Verify that the keys exist in `$_POST` and are numeric values before you attempt to do the SQL transaction. Furthermore, this code is vulnerable to SQL injection. At a miniumum, call `mysql_real_escape_string()` on each of those variables. — Michael Berkowski, Oct 09 '12 at 12:59
If you `echo $query;` you will see something like `INSERT INTO locatie (id, longitude, latitude, timestamp) VALUES (,,,,)` - a bunch of null variables. — Michael Berkowski, Oct 09 '12 at 13:00
http://stackoverflow.com/questions/11093634/you-have-an-error-in-your-sql-syntax-check-the-manual-that-corresponds-to-your — Nirav Ranpara, Oct 09 '12 at 13:05
I did an edit where the id, longitude, latitude and timestamp come from.. — David Raijmakers, Oct 09 '12 at 13:11

score 2 · Answer 1 · edited May 23 '17 at 11:43

Problem number 1: You are using the mysql_* functions. These have a red warning box telling you not to use them for a reason.

Problem number 2: You aren't escaping your data. This leaves you vulnerable to XSS attacks and SQL syntax errors caused by unexpected input.

Problem number 3: You aren't quoting your data in the SQL query.

All three of these problems can be solved by using prepared statements and parameterized queries which require a modern API (solving problem 1) and automatically quote and escape the data (solving problems 2 and 3).

score 0 · Answer 2 · answered Oct 09 '12 at 13:01

0

I really suggest, like @MichaelBerkowski said, that you check the content of your POST data. I'm pretty sure that one of these vars are totally empty :

$id = $_POST['id'];
$longitude = $_POST['longitude'];
$latitude = $_POST['latitude'];
$timestamp = $_POST['stringFromDate'];

answered Oct 09 '12 at 13:01

Laurent Brieu

3,331
1
14
15

Indeed. Babboe: have a look at isset(...). – Erwin Moller Oct 09 '12 at 13:02
Or empty() function that will help you a lot :) – Laurent Brieu Oct 09 '12 at 13:03
I dislike empty(). Maybe it happens to "work" in this case, but empty() will return true for all kind of cases. For example: 0 as integer, 0.0 as float, "0" as string, are all considered empty, while they can represent totally valid values from the post. – Erwin Moller Oct 09 '12 at 13:08
Yes, totally... it depends on what kind of data you expect to have. – Laurent Brieu Oct 09 '12 at 13:10

René Höhle · Accepted Answer · 2012-10-09T13:43:23.537

0

Perhaps you should use another notation and prevent SQL injections.

The best way is to use prepared statements.

http://php.net/manual/de/pdo.prepared-statements.php

$id = $_POST['id'];
$longitude = $_POST['longitude'];
$latitude = $_POST['latitude'];
$timestamp = $_POST['stringFromDate'];

$stmt = $dbh->prepare("INSERT INTO locatie (id, longitude, latitude, timestamp) VALUES (?,?,?,?)");
$stmt->bindParam(1, $id);
$stmt->bindParam(2, $longitude);
$stmt->bindParam(3, $latitude);
$stmt->bindParam(4, $timestamp);

edited Oct 09 '12 at 13:43

answered Oct 09 '12 at 13:07

René Höhle

26,716
22
73
82

I did an edit where the id, longitude, latitude and timestamp come from – David Raijmakers Oct 09 '12 at 13:34

score 0 · Answer 4 · answered Oct 09 '12 at 13:08

0

you Need to add the quotes if they are text:

$query="INSERT INTO locatie (id, longitude, latitude, timestamp) 
VALUES ($id, '$longitude','$latitude','$timestamp')";

answered Oct 09 '12 at 13:08

jcho360

3,724
1
15
24

score -2 · Answer 5 · answered Oct 09 '12 at 13:08

-2

You should put variables inside single '' Quote

$query="INSERT INTO locatie (id, longitude, latitude, timestamp) VALUES ('$id', '$longitude','$latitude','$timestamp')";

answered Oct 09 '12 at 13:08

Mohammad Towhidul Islam

264
1
2
12

I did an edit where the id, longitude, latitude and timestamp come from – David Raijmakers Oct 09 '12 at 13:34

PHP to mySQL Query Fault

5 Answers5