Php script & mysql query error, can't initialize variable

Question

I have Php script which uploads data to MySql database, I'm sending data from android to this script.

<?php

    $mac=null;
    $android_id=null;
    $con=mysqli_connect("localhost",".....","....","...");
    if(isset($_POST["mac"])){
        $mac=$_POST["mac"];
    }
    if(isset($_POST["android_id"])){
        $android_id=$_POST["android_id"];
    }
    if(isset($_POST["latitude"])){
        $latitude=$_POST["latitude"];
    }
    if(isset($_POST["longitude"])){
        $longitude=$_POST["longitude"];
    }
    if(isset($_POST["latitudeDestination"])){
        $latitudeDestination=$_POST["latitudeDestination"];
    }
    if(isset($_POST["longitudeDestination"])){
        $longitudeDestination=$_POST["longitudeDestination"];
    }
    if(isset($_POST["kindOfUser"])){
        $kindOfUser=$_POST["kindOfUser"];
    }

    $query1="select count(*) from marker where mac='$mac' AND android_id='$android_id'";
    $result = mysqli_query($con,$query1) or die(mysqli_error($con));
    if(mysqli_num_rows($result)==0) {
        $query2="INSERT INTO marker (mac,android_id,latitude,longitude,latitudeDestination,longitudeDestination,kindOfUser) VALUES (?,?,?,?,?,?,?)";
        $statement=mysqli_prepare($con,$query2) or die(mysqli_error($con));
    }
    else {
        $query3="INSERT INTO marker (mac,android_id,latitude,longitude,latitudeDestination,longitudeDestination,kindOfUser) VALUES (?,?,?,?,?,?,?)";
        $statement=mysqli_prepare($con,$query3) or die(mysqli_error($con));
        $query4 ="DELETE FROM marker where mac='$mac' AND android_id='$android_id'";
        $statement2=mysqli_query($con,$query4) or die(mysqli_error($con));
    }
    mysqli_stmt_bind_param($statement,"ssdddds",$mac,$android_id,$latitude,$longitude,$latitudeDestination,$longitudeDestination,$kindOfUser);
    mysqli_stmt_execute($statement);
    $response=array();
    $response["success"]=true;
    echo json_encode($response);
?>

it returns success=true, but nothing changes on database. before I put $mac=null and $android_id=null in the starting lines. The error stated that these two variables were undefined. I have debugged android app and data is present, also tried to insert data in mysql database from database interface itself and it worked. So my question is what is the problem in this script?

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Jun 27 '17 at 20:55
potential for lots of `undefined index` issues if any of the form fields are not set / empty. As pointed out already wide open for sql injection too. — Professor Abronsius, Jun 27 '17 at 20:56
@JayBlanchard what do people not understand about SQL injection? — ctwheels, Jun 27 '17 at 20:58
Some proper code indention would help us (and you) to follow the flow of the code and find potential errors. — M. Eriksson, Jun 27 '17 at 21:00
They either A.) Don't know about it or 2.) They think their code is not vulnerable @ctwheels. I could add an option for `iii` - the laziness option. — Jay Blanchard, Jun 27 '17 at 21:01
I have written it in notepad and uploaded on the 000webhost directly, When I ran this script from it, only undefined indexes and variable errors were shown. These variables are not entered by user so, Sql injection is not my first concern for now. — GiorgiSh, Jun 27 '17 at 21:03
You're not always defining variables (like `$latitude`, `$longitude` and more) but you're always trying to use all of them.. — M. Eriksson, Jun 27 '17 at 21:03
I defined rest of the values too, but nothing changed. I suspect that $_POST is not initializing variables, Even though this code worked before , Only thing I changed is isset checks because of undefined index error. — GiorgiSh, Jun 27 '17 at 21:12
Use `INSERT INTO ... ON DUPLICATE KEY UPDATE` to either insert or update depending on whether the row already exists, rather than doing all these separate queries. — Barmar, Jun 27 '17 at 21:41

score 0 · Answer 1 · answered Jun 27 '17 at 21:47

Your query to test if the row already exists uses SELECT COUNT(*), so it will always return exactly 1 row. Then you're checking what mysqli_num_rows() returns, and it will always be 1, even if there's no matching row.

Either use SELECT 1 instead of SELECT COUNT(*), or fetch the row from this query and test the value in $row[0] to see if it's 0 or not.

But it would be simpler to just use ON DUPLICATE KEY UPDATE in your INSERT query, rather than checking in your code. Just make sure that you have a unique index on (mac, android_id). Then use the query:

INSERT INTO marker (mac,android_id,latitude,longitude,latitudeDestination,longitudeDestination,kindOfUser) 
VALUES (?,?,?,?,?,?,?)
ON DUPLICATE KEY UPDATE latitude = VALUES(latitude), longitude = VALUES(longitude), latitudeDestination = VALUES(latitudeDestination), longitudeDestination = VALUES(longitudeDestination), kindOfUser = VALUES(kindOfUser)

I tried this but nothing changed. https://codepaste.net/7k3sg6 this is my code now and echo count($_POST) is printing 0. So I think problem is that $_POST doesn't have data. — GiorgiSh, Jun 28 '17 at 14:08
Then the problem is with your application that's supposed to post the data to the script. — Barmar, Jun 28 '17 at 15:19

Php script & mysql query error, can't initialize variable

1 Answers1