After running a script in a browser, I found that it twice inserts data into my MySQL database. I only make one call in my code, and everything seems to be in order. I thought this could be due to calling the request inside the if statement, but I could not find anything related to this.
I run the script using the following query:
http://businesstools.vmem.int/performance/api/start.php?device=7300&config=thin
When I run the SELECT in this table, it returns the following. Asterisks are placed next to the requests I requested, unmarked lines are included 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 found that this is only a problem until the identifier automatically incremented by my database reaches 7, then it works correctly.
EDIT 2: This appears to be caused by a prefetch of the browser that I called up using the same URL enough time for the browser to register it on the “most visited pages”. When a new tab was opened, he pre-typed the URL, adding unwanted rows to the database. Granting critical weaknesses GET web APIs with direct database access.
EDIT 3: I decided to force the interaction with the CLI to get around this. This fixes the problem, but prevents URL / GET based access to the script.