Minor bug on SQL and PHP

Oldy · 09-08-2020, 10:15 AM

Hi there,
Minor bug on SQL and PHP. Example I have Car with Volvo.

input:

Quote:

output:

Quote:

cars - ip - count
Volvo - 200.0.0.01 - 3
Volvo - 300.5.0.07 - 1
VW - 500.2.0.04 - 1

etc

I added (INSERT) one Volvo ($car) with and IP ($ip). (Volvo, 200.0.0.01, 1)
F5 then I UPDATE Volvo with my IP (Volvo, 200.0.0.01, 2)
Update it again with Volvo and IP (Volvo, 200.0.0.01, 3)
and so on
another man adds Volvo+IP (Volvo, 300.5.0.07, 1)
another man adds VW+IP (VW, 500.2.0.04, 1)
and so on
Do you understand what I mean?

database:

Quote:

CREATE TABLE cars (
cars varchar(100) NOT NULL,
ip varchar(15) NOT NULL,
count mediumint(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

php:

Quote:

//$link = mysqli_connect(...); //success

if(isset($_GET['car'])) {
$car = htmlspecialchars($_GET["car"]); //boughtonp (injection)
$ip=htmlspecialchars($_SERVER['REMOTE_ADDR']); //michaelk (REMOTE_ADDR), boughtonp (injection)
/*
$ip = getenv('HTTP_CLIENT_IP')?:
getenv('HTTP_X_FORWARDED_FOR')?:
getenv('HTTP_X_FORWARDED')?:
getenv('HTTP_FORWARDED_FOR')?:
getenv('HTTP_FORWARDED')?:
getenv('REMOTE_ADDR');
*/
$count=0;

if($car && $ip) { //car and ip already exists, then UPDATE ...
$sql_update="UPDATE cars
SET count = ".++$count."
WHERE car = '$car'"
AND ip = '$ip'";
$sql_update = mysqli_real_escape_string($link, $sql_update); //boughtonp (injection)

if(mysqli_query($link, $sql_update)) {
echo "<br>Records were updated successfully.";
}
else {
echo "<br>Not updated ".mysqli_error($link)." ".mysqli_errno($link);
}

}
else { //car and ip doesn't exist the first time, then INSERT ...
$sql_insert = "INSERT INTO cars (car, ip, count)
VALUES ('".$car."', '".$ip."', 1);";
$sql_update = mysqli_real_escape_string($link, $sql_update); //boughtonp (injection)

if(mysqli_query($link, $sql_insert)) {
echo "<br>Records inserted successfully.";
}
else {
echo "<br>ERROR: Could not able to execute $sql_insert.".mysqli_error($link)." ".mysqli_errno($link);
} //end if mysqli_query
} //end if(car && ip)

// Close connection
mysqli_free_result($link);
mysqli_close($link);

} //end if(isset(_GET['car']))

That works not. Can you fix it?

michaelk · 09-08-2020, 11:13 AM

https://www.w3schools.com/php/php_superglobals_get.asp

https://www.php.net/manual/en/reserv...les.server.php

https://www.w3schools.com/php/php_superglobals_get.asp

The easiest way to see all of the apache environment variables is via phpinfo() function.

I assume that cars.com is not the actual URL you are using because that is a real web site...

Is this homework? You need to understand how php global variables and how specifically get works.

Oldy · 09-08-2020, 11:28 AM

Already have it.

https://www.w3schools.com/php/php_superglobals_get.asp
is equal to this:

Quote:

$car=$_GET["car"];

and $_SERVER[]
https://www.php.net/manual/en/reserv...les.server.php
is equal to this:

Quote:

$indicesServer = array('PHP_SELF',
'argv',
'argc',
'GATEWAY_INTERFACE',
'SERVER_ADDR',
'SERVER_NAME',
'SERVER_SOFTWARE',
...
echo '<tr><td>'.$arg.'</td><td>' . $_SERVER[$arg] . '</td></tr>' ;
...

and phpinfo() is equal to this:

Quote:

PHP Version 7.4.3
System Linux ubuntu-2gb-nbg1-1 5.4.0-45-generic #49-Ubuntu SMP Wed Aug 26 13:38:52 UTC 2020 x86_64
Build Date May 26 2020 12:24:22
Server API FPM/FastCGI
...

boughtonp · 09-08-2020, 11:39 AM

Unrelated but important: before this code becomes Internet-accessible, you need to read about sql injection and parameterised queries.

michaelk · 09-08-2020, 11:55 AM

It is not equal.

Code:

$car=$_car["car"];

The correct syntax is:

Code:

$car=$_GET['car'];

You could use either getenv or $_SERVER.

[code]$ip=$_SERVER['REMOTE_ADDR'];

The first step would be to verify you are able to verify the correct input.

Oldy · 09-08-2020, 12:43 PM

@michaelk
Misspelled _car. Updated it:

Quote:

$car=$_GET["car"];

or

Quote:

$car = htmlspecialchars($_GET["car"]);

@boughtonp
is this correct?

Quote:

$car=htmlspecialchars($_GET["car"]);

and

Quote:

$sql_update = mysqli_real_escape_string($link, $sql_update);

or maybe stmt-something?

See the first post.

Oldy · 09-29-2020, 10:34 AM

I haven't time, until now. I can't edit the first time on code anymore post6163783. But I have update code here.

https://cars.com/cars.php?cars=volvo
When there is new car with new IP, then INSERT, but only 1 INSERT. The rest is UPDATE with the same car and same IP. I wish that it works:

Quote:

CAR - IP - TODAY - COUNT
volvo - 104.00.01.02 - 2020-09-27 16:08 - 1 //insert once

another day:

Quote:

CAR - IP - TODAY - COUNT
volvo - 104.00.01.02 - 2020-09-28 16:08 - 2 //update
lada - 104.00.01.02 - 2020-09-28 19:08 - 1 //insert once
fiat - 54.90.61.43 - 2020-09-29 16:08 - 1 //insert once

But it works not. Now there is only Records were updated successfully UPDATE cars SET count = 1 WHERE ip = '104.00.01.02' AND car='volvo'. I have deleted also with volvo+ip, but still it has updated successfully

Code:

<?php 

//prepare
set_time_limit(0);
ini_set('memory_limit', -1);

$car = htmlspecialchars($_GET["car"]);
$ip = htmlspecialchars($_SERVER['REMOTE_ADDR']);
$link = mysqli_connect(/*...*/); //success
if (mysqli_connect_errno()) {
    var_dump("<br>Connect failed: %s\n", mysqli_connect_error());
    exit();
}



//select, list
$count = 0;
$sql_select = "SELECT car, ip, today, count
                FROM cars";

if($result = mysqli_query($link, $sql_select)) {
    if(mysqli_num_rows($result) > 0) {
        echo "<table>";
            echo "<tr>";
                echo "<th>car</th>";
                echo "<th>ip</th>";
                echo "<th>today</th>";
                echo "<th>count</th>";
            echo "</tr>";
        while($row = mysqli_fetch_array($result)) {
            echo "<tr>";
                echo "<td>" . $row['car'] . "</td>";
                echo "<td>" . $row['ip'] . "</td>";
                echo "<td>" . $row['today'] . "</td>";
                echo "<td>" . $row['count'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";

        mysqli_free_result($result);
    }
    else {
        echo "<br>No records matching your query were found $sql_select ".mysqli_error($link)." ".mysqli_errno($link);
    }
}
else {
    echo "<br>ERROR: Could not able to execute $sql_select " . mysqli_error($link)." ".mysqli_errno($link);
}


//insert (only 1 car+ip), or update (2 or rest)
$count = 0;
$sql_select = "SELECT car, ip, today, count
                FROM cars
                WHERE car = '".$car."'
                AND ip = '".$ip."'";

if ($result = mysqli_query($link, $sql_select)) {
    $row_cnt = mysqli_num_rows($result);
    printf("<br>Result set has %d rows.\n", $row_cnt);
    //Result set has 0 rows.

    $sql_update="UPDATE cars
        SET count = ".(++$count)."
        WHERE ip = '".$ip."'
        AND car='".$car."'";
    echo "<br>sql_update: ".$sql_update;
    if(mysqli_query($link, $sql_update)) {
        echo "<br>Records were updated successfully $sql_update ".mysqli_error($link)." ".mysqli_errno($link);
        //Records were updated successfully UPDATE cars SET count = 1 WHERE ip = '104.00.01.02' AND car='volvo'
    }
    else {
        echo "<br>ERROR: Could not able to execute $sql_update ".mysqli_error($link)." ".mysqli_errno($link);
    }
    mysqli_free_result($result);
}
else {
    $sql_insert = "INSERT INTO cars (car, ip, today, count)
        VALUES ('".$car."', '".$ip."', NOW(), 1);";
    if(mysqli_query($link, $sql_insert)) {
        echo "<br>Records inserted successfully $sql_insert ".mysqli_error($link)." ".mysqli_errno($link);;
    }
    else {
        echo "<br>ERROR: Could not able to execute $sql_insert ".mysqli_error($link)." ".mysqli_errno($link);
    }
}

Can you help me?

michaelk · 09-29-2020, 11:17 AM

What is exactly not working? Your original database did not include a date field. Did you add one and if so what is its type?

Is your sql insert string actually on one line or did you just post it that way?

Code:

$sql_select = "SELECT car, ip, today, count
                FROM cars
                WHERE car = '".$car."'
                AND ip = '".$ip."'";

versus

Code:

$sql_select="select * from cars where car='$car' and ip='$ip'";

If you set count to zero then ++count will always be 1. Try:

Code:

$sql_select="update cars set count=count+1 where car='$car' and ip='$ip'";

Do you only want to insert/update values from an IP only once a day? If so you will need to add some checks to validate date and time.

Oldy · 09-29-2020, 11:57 AM

I forgot it with SQL, see here:

Code:

CREATE TABLE cars (
cars varchar(100) NOT NULL,
ip varchar(15) NOT NULL,
today datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(),
count mediumint(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I am posted with " and without sql.

Code:

$sql_update="UPDATE cars SET count = count+1 WHERE car='$car' AND ip = '$ip'";
if(mysqli_query($link, $sql_update)) {
    echo "<br>Records were updated successfully $sql_update ".mysqli_error($link)." ".mysqli_errno($link);
}
else {
    echo "<br>ERROR: Could not able to execute $sql_update ".mysqli_error($link)." ".mysqli_errno($link);
}

//Records were updated successfully UPDATE cars SET count = 0+1 WHERE ip = '104.00.01.02' AND car='volvo' 0
//and also with new car, not INSERT yet, but I suppose it will have ....inserted successfully..., but: https://cars.com/cars.php?cars=lada ...updated successfully..., and nothing car (lada+104.00.01.02) will be SELECTed.

michaelk · 09-29-2020, 01:23 PM

Code:

CREATE TABLE cars (
cars varchar(100) NOT NULL,
ip varchar(15) NOT NULL,
count mediumint(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Your table definition does not include a date field i.e today is not defined.

Oldy · 09-29-2020, 01:36 PM

today: https://www.linuxquestions.org/quest...5/#post6170875

michaelk · 09-29-2020, 03:59 PM

Nevermind bad advise.

boughtonp · 09-29-2020, 04:10 PM

https://www.w3schools.com/php/php_my...statements.asp

Oldy · 09-29-2020, 04:40 PM

@michaelk: Yes I know with date and time, ex. the time here is now 23:41.
sql: volvo - 104.00.01.02 - 2020-09-29 23:41 - 3

or

Code:

$sql_select = "SELECT car, ip, ".date_create('now')->format('Y-m-d H:i:s').", count
                FROM cars
                WHERE car = '".$car."'
                AND ip = '".$ip."'";
...

volvo - 104.00.01.02 - 2020-09-29 23:41 - 3

I have not tested it yet on today datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp()
maybe you can have only today datetime NOT NULL DEFAULT '0000-00-00 00:00:00'.

But still is only ...updated successfully..., not ...inserted successfully... on the first time.

@boughtonp: It is 99% the same on Object oriented style (only mysql), Procedural style (only mysql) and PDO with Prepared Statements (mysql and other sql). I use now procedural style for a more easy and newbie. But again, what exactly will you mean about it? Tiny different style, but it is only this ...updated successfully.... Exactly the same.

michaelk · 09-29-2020, 05:38 PM

I stand corrected, I tried using NOW() and it worked i.e. I did not get a syntax error.

Check the web server error logs which might show the line that isn't working. I don't run mysql so I might be missing something.