0

I'm practicing PHP and now, I'm studying inserting values to database using MySQLi, but I'm getting this 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 '' at line 1 and I don't know what I'm doing wrong!. Here is my code:

header('Content-Type: text/html; charset=utf-8');
ini_set("display_errors", 1);

function getUserIP()
{
    // Get real visitor IP behind CloudFlare network
    if (isset($_SERVER["HTTP_CF_CONNECTING_IP"])) {
              $_SERVER['REMOTE_ADDR'] = $_SERVER["HTTP_CF_CONNECTING_IP"];
              $_SERVER['HTTP_CLIENT_IP'] = $_SERVER["HTTP_CF_CONNECTING_IP"];
    }
    $client  = @$_SERVER['HTTP_CLIENT_IP'];
    $forward = @$_SERVER['HTTP_X_FORWARDED_FOR'];
    $remote  = $_SERVER['REMOTE_ADDR'];

    if(filter_var($client, FILTER_VALIDATE_IP))
    {
        $ip = $client;
    }
    elseif(filter_var($forward, FILTER_VALIDATE_IP))
    {
        $ip = $forward;
    }
    else
    {
        $ip = $remote;
    }

    return $ip;
}

if(isset($_GET['lat']) && !empty($_GET['lat']))
{
    $lat = $_GET['lat'];
} else {
    die();
}

if(isset($_GET['lon']) && !empty($_GET['lon']))
{
    $lon = $_GET['lon'];
} else {
    die();
}

$ip = getUserIP();
require('database.php');

$query = mysqli_query($mysqli, "SELECT * FROM `tracking` WHERE ip='".$ip."'");
if (!$query)
{
    die('Error: ' . mysqli_error($mysqli));
}
if(mysqli_num_rows($query) > 0){
    mysqli_query($mysqli,"UPDATE `tracking` SET lat='".$lat."', lon='".$lon."' WHERE ip='".$ip."'") or die(mysqli_error($mysqli)); echo "x1";
}
if(mysqli_num_rows($query) == 0){
    mysqli_query($mysqli,"INSERT INTO `tracking` (id, ip, lat, lon) VALUES (NULL, '".$ip."', '".$lat."', '".$lon."'") or die(mysqli_error($mysqli));
}
mysqli_close($mysqli);

The line that is giving the error is:

mysqli_query($mysqli,"INSERT INTOtracking(id, ip, lat, lon) VALUES (NULL, '".$ip."', '".$lat."', '".$lon."'") or die(mysqli_error($mysqli));

I hope that you understand what I'm trying to say here. Can you help me? Thank you!

Barmar
  • 741,623
  • 53
  • 500
  • 612
Karlna
  • 67
  • 2
  • 11
  • 5
    Your code is *wide open* to **SQL injection**. As a result, aside from security vulnerabilities, you're also not entirely controlling the SQL code you're executing. What is the runtime value of that string of SQL code you're building? That is, what is the actual SQL query you're executing? Seems that it has a syntax error. (Very likely the missing closing parenthesis at the end.) – David Aug 29 '18 at 20:15
  • I'm just running this at the browser > `http://127.0.0.1/update.php?lat=1&lon=1`. Just for testing – Karlna Aug 29 '18 at 20:17
  • 3
    @Dice: The OP is already using `mysqli`, which is perfectly capable of prepared statements and query parameters. There's no need to completely change the technologies being used here. – David Aug 29 '18 at 20:19
  • I meant to bind parameters and not put them in a raw way – Dice Aug 29 '18 at 20:20
  • 2
    @Dice: https://secure.php.net/manual/en/mysqli-stmt.bind-param.php – David Aug 29 '18 at 20:21
  • @Karlna: The syntax error is *almost certainly* the missing closing parenthesis. As such, I'm voting to close this question as a typo. However, it is *highly recommended* that you learn more about prepared statements and query parameters here: https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – David Aug 29 '18 at 20:23
  • Helps to debug things if you can print out your query before you run it. Like this: `$query = "INSERT INTO tracking (id, ip, lat, lon) VALUES (NULL, '".$ip."', '".$lat."', '".$lon."'")"; echo $query; mysqli_query($mysqli,$query) or die(mysqli_error($mysqli));` – dmikester1 Aug 29 '18 at 20:24
  • Is now working @dmikester1, thank you!! – Karlna Aug 29 '18 at 20:27
  • But I'm afraid about sql injection, I must change or not the code? – Karlna Aug 29 '18 at 20:27
  • 2
    @Karlna: You should definitely change the code in that regard, yes. The question I linked above contains lots of information and helpful examples. There's no shortage of that elsewhere on the internet as well. SQL injectable code brings with it two main problems: (1) It's vulnerable to malicious users, (2) It's highly bug-prone. Correcting it will save you a lot of problems in the future. – David Aug 29 '18 at 20:29
  • 3
    You should also learn how to use `ON DUPLICATE KEY UPDATE`, instead of doing separate `SELECT`, `UPDATE`, and `INSERT` queries. – Barmar Aug 29 '18 at 20:29
  • Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. – tadman Aug 29 '18 at 20:46
  • **WARNING**: Using the error-suppressing YOLO operator (`@`) obscures problems with your code and makes debugging issues like this a whole lot more complicated. That's a tool of last resort and should only be used in exceptional circumstances. You should display an error message for the user, log a problem, initiate some kind of retry, or all of these things in conjunction. – tadman Aug 29 '18 at 20:46

0 Answers0