CVE-2015-10036: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: A vulnerability was found in kylebebak dronfelipe. It has been declared as critical. Affected by this vulnerability is an unknown functionality. The manipulation leads to sql injection. The patch is named 87405b74fe651892d79d0dff62ed17a7eaef6a60. It is recommended to apply a patch to fix this issue. The associated identifier of this vulnerability is VDB-217951.
CWE description: The product constructs all or part of an SQL command using externally-influenced input from an upstream component, but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command when it is sent to a downstream component. Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data.
CVSS v3 base score: 9.8
Published: 2023-01-11T07:15Z
Repository: https://github.com/kylebebak/dronfelipe
Language: PHP
Files changed: location_history/controllers/__build_query.php, location_history/controllers/__parse_query.php, location_history/controllers/global.php, location_history/controllers/graph.php, location_history/controllers/main.php, location_history/controllers/trips.php, location_history/controllers/visits.php, location_history/css/style.css
Commit date: 2015-12-15 15:11:20 -0600
Commit message: this commit is important, it has prevented all sql injection attacks in location_history by either using prepared statements or using mysqli::real_escape_string

Diff: diff --git a/location_history/controllers/__build_query.php b/location_history/controllers/__build_query.php
index ba15f5b..9cde485 100644
--- a/location_history/controllers/__build_query.php
+++ b/location_history/controllers/__build_query.php
@@ -1,23 +1,22 @@
 <?php
 
 include_once '../models/Location_History.php';
 
 
-
 // range of dates
 if ($dateFilter[0]) {
-	$query .= " AND start_date >= STR_TO_DATE('" . htmlspecialchars($dateFilter[0]) . "', '%m/%d/%Y')";
+	$query .= " AND start_date >= STR_TO_DATE('" . $db->mysqli()->real_escape_string($dateFilter[0]) . "', '%m/%d/%Y')";
 }
 
 if ($dateFilter[1]) {
-	$query .= " AND start_date <= STR_TO_DATE('" . htmlspecialchars($dateFilter[1]) . "', '%m/%d/%Y')";
+	$query .= " AND start_date <= STR_TO_DATE('" . $db->mysqli()->real_escape_string($dateFilter[1]) . "', '%m/%d/%Y')";
 }
 
 
 // filtered-out days
 if (isset($dayFilter)) {
-	$query .= " AND DAYOFWEEK(start_date) NOT IN (" . htmlspecialchars($dayFilter) . ")";
+	$query .= " AND DAYOFWEEK(start_date) NOT IN (" . $dayFilter . ")";
 }
 
 
 ?>
diff --git a/location_history/controllers/__parse_query.php b/location_history/controllers/__parse_query.php
index 0c620d5..76798d6 100644
--- a/location_history/controllers/__parse_query.php
+++ b/location_history/controllers/__parse_query.php
@@ -4,22 +4,22 @@ include_once '../models/Location_History.php';
 
 
 // limit number of results
 $limit = null;
 if ($_POST['limit']) {
-	$limit = $_POST['limit'];
+	$limit = $db->mysqli()->real_escape_string($_POST['limit']);
 }
 
 
 // select only a certain range of dates
 $dayFilter = null;
 if (isset($_POST['dayFilter'])) {
-	$dayFilter = implode(',', $_POST['dayFilter']);
+	$dayFilter = $db->mysqli()->real_escape_string(implode(',', $_POST['dayFilter']));
 }
 
 
-// filter out certain days of the week
+// filter out certain days of the week. the elements of this array are escaped in __build_query.php
 $dateFilter = array('', '');
 if (isset($_POST['dateFilter'])) {
 	$dateFilter = $_POST['dateFilter'];
 }
 
diff --git a/location_history/controllers/global.php b/location_history/controllers/global.php
index a1985e1..c882893 100644
--- a/location_history/controllers/global.php
+++ b/location_history/controllers/global.php
@@ -13,11 +13,11 @@ $query = "SELECT
 
 
 // CONSTRUCT WHERE CLAUSE
 include '__build_query.php';
 
-$visits = $db->rawQuery($query, null, false);
+$visits = $db->rawQuery($query, null);
 
 
 
 // trips
 $query = "SELECT
@@ -27,11 +27,11 @@ $query = "SELECT
 
 
 // CONSTRUCT WHERE CLAUSE
 include '__build_query.php';
 
-$trips = $db->rawQuery($query, null, false);
+$trips = $db->rawQuery($query, null);
 
 
 echo json_encode(array("visits" => $visits, "trips" =>  $trips));
 
 
diff --git a/location_history/controllers/graph.php b/location_history/controllers/graph.php
index 21e29a7..b0cfc19 100644
--- a/location_history/controllers/graph.php
+++ b/location_history/controllers/graph.php
@@ -7,28 +7,25 @@ include_once '__parse_query.php';
 // this cannot return meaningful results with a location id
 if (!$_POST['location_id']) {
 	return;
 }
 
-$location_id = $_POST['location_id'];
-
-
 
 // visits for location
 $query = "SELECT
 	DATE(start_date) AS date, SUM(duration) AS duration
 	FROM grouped_point gp
-	WHERE location_id = " . htmlspecialchars($location_id);
+	WHERE location_id = ?";
 
 
 // CONSTRUCT WHERE CLAUSE
 include '__build_query.php';
 
 // group by comes after where clause
 $query .= " GROUP BY DATE(start_date)";
 
-$results = $db->rawQuery($query, null, false);
+$results = $db->rawQuery($query, Array($_POST['location_id']));
 
 
 // convert seconds to hours, and make sure that no day is longer than 24 hours, by rolling extra hours over to the next record
 foreach($results as $index => &$result) {
 	$duration = $result['duration'];
diff --git a/location_history/controllers/main.php b/location_history/controllers/main.php
index b04c4f9..f99bcc2 100644
--- a/location_history/controllers/main.php
+++ b/location_history/controllers/main.php
@@ -22,16 +22,16 @@ include_once '__build_query.php';
 $query .= " GROUP BY l.id ORDER BY duration DESC";
 
 
 // limit clause comes last
 if (isset($limit)) {
-	$query .= " LIMIT " . htmlspecialchars($limit);
+	$query .= " LIMIT " . $limit;
 }
 
 
 
-echo json_encode($db->rawQuery($query, null, false));
+echo json_encode($db->rawQuery($query, null));
 
 
 
 
 
diff --git a/location_history/controllers/trips.php b/location_history/controllers/trips.php
index be3e985..fceb027 100644
--- a/location_history/controllers/trips.php
+++ b/location_history/controllers/trips.php
@@ -7,11 +7,11 @@ include_once '../models/Circular.php';
 // this cannot return meaningful results without a location id
 if (!$_POST['location_id']) {
 	return;
 }
 
-$location_id = htmlspecialchars($_POST['location_id']);
+$location_id = $db->mysqli()->real_escape_string($_POST['location_id']);
 
 
 
 
 
@@ -26,11 +26,11 @@ FROM trip t, location l
 WHERE t.end_location_id = l.id
 AND t.start_location_id = " . $location_id;
 
 include '__build_query.php';
 $query .= " GROUP BY t.end_location_id HAVING count_lid > 1 ORDER BY count_lid DESC";
-$start_aggregate = $db->rawQuery($query, null, false);
+$start_aggregate = $db->rawQuery($query, null);
 
 
 
 
     /* all
@@ -48,11 +48,11 @@ WHERE t.end_location_id = l.id
 AND t.end_location_id = c.end_location_id
 AND t.start_location_id = " . $location_id;
 
 include '__build_query.php';
 $query .= " ORDER BY c.count_lid DESC, t.end_location_id, t.start_date ASC";
-$start_all = $db->rawQuery($query, null, false);
+$start_all = $db->rawQuery($query, null);
 
 
 
 
 /* trips ending at location
@@ -65,11 +65,11 @@ FROM trip t, location l
 WHERE t.start_location_id = l.id
 AND t.end_location_id = " . $location_id;
 
 include '__build_query.php';
 $query .= " GROUP BY t.start_location_id HAVING count_lid > 1 ORDER BY count_lid DESC";
-$end_aggregate = $db->rawQuery($query, null, false);
+$end_aggregate = $db->rawQuery($query, null);
 
 
 
 
     /* all
@@ -87,11 +87,11 @@ WHERE t.start_location_id = l.id
 AND t.start_location_id = c.start_location_id
 AND t.end_location_id = " . $location_id;
 
 include '__build_query.php';
 $query .= " ORDER BY c.count_lid DESC, t.start_location_id, t.end_date ASC";
-$end_all = $db->rawQuery($query, null, false);
+$end_all = $db->rawQuery($query, null);
 
 
 
 
 
@@ -107,11 +107,11 @@ WHERE t.start_location_id = " . $location_id . " OR t.end_location_id = " . $loc
 include '__build_query.php';
 
 $query .= " ORDER BY t.start_date ASC) SQ, location ls, location le
 WHERE ls.id = SQ.start_location_id AND le.id = SQ.end_location_id";
 
-$start_end_all = $db->rawQuery($query, null, false);
+$start_end_all = $db->rawQuery($query, null);
 
 
 
 
 
diff --git a/location_history/controllers/visits.php b/location_history/controllers/visits.php
index 140bbf1..377a287 100644
--- a/location_history/controllers/visits.php
+++ b/location_history/controllers/visits.php
@@ -7,26 +7,23 @@ include_once '__parse_query.php';
 // this cannot return meaningful results without a location id
 if (!$_POST['location_id']) {
 	return;
 }
 
-$location_id = $_POST['location_id'];
-
-
 // visits for location
 $query = "SELECT
 	DATE(gp.start_date) as start_date, TIME(gp.start_date) as start_time, TIME_TO_SEC(gp.start_date) as start_time_sec, TIME_TO_SEC(gp.end_date) as end_time_sec, duration
 	FROM grouped_point gp
-	WHERE location_id = " . htmlspecialchars($location_id);
+	WHERE location_id = ?";
 
 
 
 // CONSTRUCT WHERE CLAUSE
 include '__build_query.php';
 include_once '../models/Circular.php';
 
-$results = $db->rawQuery($query, null, false);
+$results = $db->rawQuery($query, Array($_POST['location_id']));
 
 
 $start_times = array();
 $end_times = array();
 
@@ -41,8 +38,6 @@ echo json_encode(array
 		("start" => round(circularMean($start_times, 86400)),
 		"end" => round(circularMean($end_times, 86400))),
 	"results" => $results));
 
 
-
-
 ?>
diff --git a/location_history/css/style.css b/location_history/css/style.css
index 13b85bc..8de35aa 100644
--- a/location_history/css/style.css
+++ b/location_history/css/style.css
@@ -206,10 +206,14 @@ li#datepicker-container {
 
 li#weekdays-container {
 	margin-top: 8px;
 }
 
+label.checkbox {
+	color: black !important;
+}
+
 li#search-container {
 	margin-top: 11px;
 	margin-right: 10px;
 	margin-left: -30px;
 }

Vulnerable code: 
	$query .= " AND start_date >= STR_TO_DATE('" . htmlspecialchars($dateFilter[0]) . "', '%m/%d/%Y')";
	$query .= " AND start_date <= STR_TO_DATE('" . htmlspecialchars($dateFilter[1]) . "', '%m/%d/%Y')";
	$query .= " AND DAYOFWEEK(start_date) NOT IN (" . htmlspecialchars($dayFilter) . ")";
	$limit = $_POST['limit'];
	$dayFilter = implode(',', $_POST['dayFilter']);
// filter out certain days of the week
$visits = $db->rawQuery($query, null, false);
$trips = $db->rawQuery($query, null, false);
$location_id = $_POST['location_id'];


	WHERE location_id = " . htmlspecialchars($location_id);
$results = $db->rawQuery($query, null, false);
	$query .= " LIMIT " . htmlspecialchars($limit);
echo json_encode($db->rawQuery($query, null, false));
$location_id = htmlspecialchars($_POST['location_id']);
$start_aggregate = $db->rawQuery($query, null, false);
$start_all = $db->rawQuery($query, null, false);
$end_aggregate = $db->rawQuery($query, null, false);
$end_all = $db->rawQuery($query, null, false);
$start_end_all = $db->rawQuery($query, null, false);
$location_id = $_POST['location_id'];


	WHERE location_id = " . htmlspecialchars($location_id);
$results = $db->rawQuery($query, null, false);


Fixed code: 	$query .= " AND start_date >= STR_TO_DATE('" . $db->mysqli()->real_escape_string($dateFilter[0]) . "', '%m/%d/%Y')";
	$query .= " AND start_date <= STR_TO_DATE('" . $db->mysqli()->real_escape_string($dateFilter[1]) . "', '%m/%d/%Y')";
	$query .= " AND DAYOFWEEK(start_date) NOT IN (" . $dayFilter . ")";
	$limit = $db->mysqli()->real_escape_string($_POST['limit']);
	$dayFilter = $db->mysqli()->real_escape_string(implode(',', $_POST['dayFilter']));
// filter out certain days of the week. the elements of this array are escaped in __build_query.php
$visits = $db->rawQuery($query, null);
$trips = $db->rawQuery($query, null);
	WHERE location_id = ?";
$results = $db->rawQuery($query, Array($_POST['location_id']));
	$query .= " LIMIT " . $limit;
echo json_encode($db->rawQuery($query, null));
$location_id = $db->mysqli()->real_escape_string($_POST['location_id']);
$start_aggregate = $db->rawQuery($query, null);
$start_all = $db->rawQuery($query, null);
$end_aggregate = $db->rawQuery($query, null);
$end_all = $db->rawQuery($query, null);
$start_end_all = $db->rawQuery($query, null);
	WHERE location_id = ?";
$results = $db->rawQuery($query, Array($_POST['location_id']));
label.checkbox {
	color: black !important;
}