PHP API script inserting into MySQL database twice

Question

After running the following script from the browser I am finding it is inserting the data into my MySQL database twice. I am only making one call in my code and everything seems to be in order. I was thinking this might have something to do with calling the query inside an if statement, but I haven't been able to find anything relating to this.

I am running the script using the following query:

http://businesstools.vmem.int/performance/api/start.php?device=7300&config=thin

When I run a SELECT statement on this table it returns the following. Asterisks are placed next to queries I ran, the non-marked rows are coming in as secondary inserts.

mysql> SELECT * FROM test;
+-----+-----+-----+---------------------+
| tid | cid | did | runtime             |
+-----+-----+-----+---------------------+
|  *1 |   1 |   5 | 2015-12-22 15:54:56 |
|   2 |   1 |   5 | 2015-12-22 15:55:29 |
|  *3 |   1 |   5 | 2015-12-22 15:57:52 |
|   4 |   1 |   5 | 2015-12-22 15:57:57 |
|  *5 |   0 |   5 | 2015-12-22 15:57:59 |
|   6 |   0 |   5 | 2015-12-22 16:06:28 |
|  *7 |   0 |   5 | 2015-12-22 16:06:31 |
|  *8 |   1 |   5 | 2015-12-22 16:06:35 |
|  *9 |   1 |   5 | 2015-12-22 16:06:38 |
| *10 |   1 |   5 | 2015-12-22 16:06:41 |
| *11 |   1 |   6 | 2015-12-22 16:06:49 |
| *12 |   1 |   5 | 2015-12-22 16:10:21 |
+-----+-----+-----+---------------------+
12 rows in set (0.00 sec)

PHP

mysql_connect("localhost", xxxx, xxxx);
mysql_select_db(performanceData);
mysql_set_charset("utf8");

//Assert provided device configuration exists
$deviceSwitch = false;
$deviceNum;
$configSwitch = false;
$configNum;
$errorSwitch = false;

$returnArray = array("testID"=>-1, "error"=>"NULL");
$errorString = "ERROR";

$deviceInfo = mysql_query("SELECT d.did AS 'did', d.name AS 'name', c.cid AS 'cid', c.lunType AS 'lunType' FROM device d JOIN config c ON d.did = c.did;");

while ($row = mysql_fetch_assoc($deviceInfo))
{
    if (strcmp($_GET["device"], $row["name"]) == 0)
    {
        $deviceSwitch = true;
        $deviceNum = $row["did"];
        if (strcmp($_GET["config"], $row["lunType"]) == 0)
        {
            $configSwitch = true;
            $configNum = $row["cid"];
        }
    }
}

if ($deviceSwitch && $configSwitch)
{
    if (mysql_query("INSERT INTO test (cid, did) VALUES (".$configNum.", ".$deviceNum.");"))
    {
        $returnArray["testID"] = mysql_insert_id();
    }
    else
    {
        $errorString .= " - Failed to insert into database, please contact sysadmin";
        $errorSwitch = true;
    }
}
else
{
    $errorSwitch = true;
    $errorString .= " - Improper device or config formatting";
}

if ($errorSwitch)
    $returnArray["error"] = $errorString;

echo json_encode($returnArray);

?>

MySQL Table Setup

CREATE TABLE test(
    tid int NOT NULL AUTO_INCREMENT,
    cid int NOT NULL,
    did int NOT NULL,
    runtime TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (tid),
    FOREIGN KEY (cid) REFERENCES config (cid)
        ON DELETE CASCADE,
    FOREIGN KEY (did) REFERENCES device (did)
        ON DELETE CASCADE
);

EDIT: After further testing I've found this is only an issue until the ID being auto incremented by my database reaches a value of 7, then it operates properly.

EDIT 2: This appears to be caused by browser pre-fetching caused by me using the same URL enough times for the browser to log it in "most visited pages". When a new tab was opened it prefetched the URL adding unwanted rows to the DB. Exposing a critical weakness of a direct-to-database GET web API.

EDIT 3: I decided to force CLI interaction to get around this problem. This removes the issue, but disallows URL/GET based access to the script.

brainless · Accepted Answer · 2015-12-24T11:55:46.043

CLI can be used to avoid such scenarios where you do not want the web to invoke an action on your application. You can use Symphony Console component which is available on packagist and can be easily integrated using composer.

If you do not wish to enforce CLI strictly, you can detect the prefetch requests from header values and ignore those requests.

Chrome/Safari send an X-Purpose: preview and Mozilla sends X-moz: prefetch HTTP header when pre-fetching a web page.

PHP API script inserting into MySQL database twice

1 Answers1