CVE-2022-4871: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: A vulnerability classified as problematic was found in ummmmm nflpick-em.com up to 2.2.x. This vulnerability affects the function _Load_Users of the file html/includes/runtime/admin/JSON/LoadUsers.php. The manipulation of the argument sort leads to sql injection. The attack can be initiated remotely. The patch is identified as dd77a35942f527ea0beef5e0ec62b92e8b93211e. It is recommended to apply a patch to fix this issue. VDB-217270 is the identifier assigned to this vulnerability. NOTE: JSON entrypoint is only accessible via an admin account
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: 7.2
Published: 2023-01-03T12:15Z
Repository: https://github.com/ummmmm/nflpick-em.com
Language: PHP
Files changed: html/includes/runtime/admin/JSON/LoadUsers.php
Commit date: 2022-08-21 11:29:10 -0700
Commit message: Fixed SQL injection issue in _Load_Users

That was probably the only place in the software that had SQL injection since
we took the sort value and put it directly into the query.  The sort value
is now verified as a valid column.

This wasn't really an issue however since the JSON entrypoint was only accessible
via an admin account anyway.

Diff: diff --git a/html/includes/runtime/admin/JSON/LoadUsers.php b/html/includes/runtime/admin/JSON/LoadUsers.php
index ad30e70..1445ba9 100644
--- a/html/includes/runtime/admin/JSON/LoadUsers.php
+++ b/html/includes/runtime/admin/JSON/LoadUsers.php
@@ -4,32 +4,51 @@ class JSON_LoadUsers extends JSONAdmin
 {
 	public function execute()
 	{
 		$sort		= Functions::Post( 'sort' );
 		$direction	= Functions::Post( 'direction' );
-		
+
 		if ( !$this->_Load_Users( $sort, $direction, $users ) )
 		{
-			return $this->setDBError();
+			return false;
 		}
-		
+
 		foreach( $users as &$loaded_user )
 		{
 			$loaded_user[ 'last_on' ] 			= Functions::FormatDate( $loaded_user[ 'last_on' ] );
-			$loaded_user[ 'current_place' ] 	= Functions::Place( $loaded_user[ 'current_place' ] ); 
+			$loaded_user[ 'current_place' ] 	= Functions::Place( $loaded_user[ 'current_place' ] );
 		}
 
-		return $this->setData( $users );		
+		return $this->setData( $users );
 	}
 
 	// Helper functions
 
 	private function _Load_Users( $sort, $direction, &$users )
 	{
 		$db_weeks	= new Weeks( $this->_db );
 		$current	= $db_weeks->Current();
 		$direction 	= ( $direction === 'asc' ) ? 'ASC' : 'DESC';
+
+		switch ( $sort )
+		{
+			case 'name'				:
+			case 'current_place'	:
+			case 'last_on'			:
+			case 'paid'				:
+			case 'failed_logins'	:
+			case 'active_sessions'	:
+			case 'remaining'		:
+			{
+				break;
+			}
+			default					:
+			{
+				return $this->setError( array( '#Error#', 'Invalid sort' ) );
+			}
+		}
+
 		$sql 		= "SELECT
 							u.*,
 							CONCAT( u.fname, ' ', u.lname ) AS name,
 							( SELECT COUNT( * ) FROM failed_logins WHERE email = u.email ) AS failed_logins,
 							( SELECT COUNT( * ) FROM sessions WHERE userid = u.id ) AS active_sessions,
@@ -43,8 +62,14 @@ class JSON_LoadUsers extends JSONAdmin
 							p.id IS NULL
 						GROUP BY
 							u.id
 						ORDER BY
 							{$sort} {$direction}";
-		return $this->_db->select( $sql, $users, $current );
+
+		if ( !$this->_db->select( $sql, $users, $current ) )
+		{
+			return $this->setDBError();
+		}
+
+		return true;
 	}
 }

Vulnerable code: 		
			return $this->setDBError();
		
			$loaded_user[ 'current_place' ] 	= Functions::Place( $loaded_user[ 'current_place' ] ); 
		return $this->setData( $users );		
		return $this->_db->select( $sql, $users, $current );
Fixed code: 
			return false;

			$loaded_user[ 'current_place' ] 	= Functions::Place( $loaded_user[ 'current_place' ] );
		return $this->setData( $users );

		switch ( $sort )
		{
			case 'name'				:
			case 'current_place'	:
			case 'last_on'			:
			case 'paid'				:
			case 'failed_logins'	:
			case 'active_sessions'	:
			case 'remaining'		:
			{
				break;
			}
			default					:
			{
				return $this->setError( array( '#Error#', 'Invalid sort' ) );
			}
		}


		if ( !$this->_db->select( $sql, $users, $current ) )
		{
			return $this->setDBError();
		}

		return true;