Bug in function populatePIREPFinance

[Rabso]

Hi, 

 

So while checking errors, I got following:

=====
Time: 10.05.20 19:18:06
Backtrace: DB::write_debug > DB::get_row > OperationsData::getAircraftInfo > FinanceData::getLoadCount > PIREPData::populatePIREPFinance > PIREPData::fileReport > ACARSData::FilePIREP > smartCARS::filepirep
Query: SELECT * FROM phpvms_aircraft WHERE `id`=A330-243
Error: (1054) - Unknown column 'A330' in 'where clause'
=====

 

I searched in codes and found this bug (PIREPData.class.php), line 950:

$pirep->load = FinanceData::getLoadCount($pirep->aircraft, $sched->flighttype);

Now $pirep->aircraft should be corrected to $pirep->aircraftid

 

Why?, $pirep array is coming from line 931, PIREPData::getReportDetails($pirep)

Checking function getReportDetails, line 307

        $sql = 'SELECT p.*, s.*, s.id AS scheduleid, p.route, p.route_details, p.rawdata,
					u.pilotid, u.firstname, u.lastname, u.email, u.rank, u.code AS pcode,
					dep.name as depname, dep.lat AS deplat, dep.lng AS deplng,
					arr.name as arrname, arr.lat AS arrlat, arr.lng AS arrlng,
				    p.code, p.flightnum, p.depicao, p.arricao,  p.price AS price,
				    a.id as aircraftid, a.name as aircraft, a.registration, p.flighttime,
				    p.distance, 
                    UNIX_TIMESTAMP(p.submitdate) as submitdate, 
                    UNIX_TIMESTAMP(p.modifieddate) as modifieddate,
                    p.accepted, p.log
				FROM '.TABLE_PREFIX.'pilots u, '.TABLE_PREFIX.'pireps p
					LEFT JOIN ' . TABLE_PREFIX . 'airports AS dep ON dep.icao = p.depicao
					LEFT JOIN ' . TABLE_PREFIX . 'airports AS arr ON arr.icao = p.arricao
					LEFT JOIN ' . TABLE_PREFIX . 'aircraft a ON a.id = p.aircraft
					LEFT JOIN ' . TABLE_PREFIX . 'schedules s ON s.code = p.code AND s.flightnum = p.flightnum
				WHERE p.pilotid=u.pilotid AND p.pirepid=' . $pirepid;

 

Problem is in 'a.name as aircraft',

if we look at output of 'aircraft', we will see 3 times;

- first one is containing aircraft id from table pireps

- second one is containing aircraft id from table schedules

- and last one is containing aircraft name from table aircraft.

So when code called $pirep->aircraft, it's reading last selected from 3 which is aircraft name.

 

Checking this raised a question, why 'getReportDetails' is calling many columns from pireps table several times? at first p* to call all column, then p.route then p.route_details and so on.

 

regards

Edited October 6, 2020 by Rabso

[Nabeel]

What database versions are you using? All of those columns/queries are being joined so multiple queries don't need to be made when it can all be returned in one

[Rabso]

I am using MySQL 5.7 but even when it was on 5.5 had same issue.

I tested the code through phpmyadmin and run the SQL, I can see 3 columns as aircraft but 3rd one contains aircraft name not id