Code not working after updates

Hi, I hope it is ok to ask this here.
This is a problem on a site I maintain, It worked correctly until updates a while ago.
It is a company selling 2 products per litre. Both products are in the same table but each has its own row. When they update the per litre price a new row is added to the database. This still works, (it also mentions location but they only deliver to one county at the moment)

This saves the price

<?
include('config.php');
if(in_array($_POST['location'], $locations) && in_array($_POST['fueltype'], $fueltypes))
{
	$location = $_POST['location'];
	$fueltype = $_POST['fueltype'];
	$time = time();
	$prices = json_decode($_POST['prices']);
	$prices_save = array();
	foreach($prices as $p)
	{
		$prices_save[] = array('range' => intval($p[0]), 'price' => number_format($p[1], 10, '.', ''));
	}
	print_r($prices_save);
	query("INSERT INTO `zzz_oilprices` (`timestamp`, `type`, `location`, `prices`) VALUES ('".$time."', '".$fueltype."', '".$location."', '".serialize($prices_save)."')",'u');
}
else
{
	echo'error';
}
?>

But the line of code that used to check the table for the latest entry now only gets the price from the first row in the table rather than the latest row for that product.

function query($query, $type)
{

    $rs = mysql_query($query) or die(mysql_error());
    
    if ($type == 'u') { return; }    
    
    if ($type == 'n'){ $num = mysql_num_rows($rs);  if ($num == 0){ return 0; }else{ return $num; } }
    

    if ($type == "1") {
        $data=mysql_fetch_array($rs);
        return $data;
    }
    else { return $rs; }

}

function Prices()
{


$products = array(0=>array('name'=>'Kerosene', 'locations'=>array()), 1=>array('name'=>'Gas', 'locations'=>array()));


$sql = "SELECT * FROM (SELECT * FROM `xxx_oilprices` ORDER BY `id` DESC) as tmp WHERE `type` = 'kero' GROUP BY `location` ORDER BY `id` DESC";
$k = query($sql, '');										
while($a=mysql_fetch_array($k))
{
	$products[0]['locations'][$a['location']][]=array('range' => 'Select Quantity', 'price' => 0);
	$aa = unserialize($a['prices']);
	foreach($aa as $aaa)
	{
		$products[0]['locations'][$a['location']][]=array('range' => $aaa['range'], 'price' => $aaa['price']);
	}
}

$sql = "SELECT * FROM (SELECT * FROM `xxx_oilprices` ORDER BY `timestamp` DESC) as tmp WHERE `type` = 'gas' GROUP BY `location` ORDER BY `timestamp` DESC";
$g = query($sql, '');										
while($b=mysql_fetch_array($g))
{ 
	$products[1]['locations'][$b['location']][]=array('range' => 'Select Quantity', 'price' => 0);
	$bb = unserialize($b['prices']);
	foreach($bb as $bbb)
	{
		$products[1]['locations'][$b['location']][]=array('range' => $bbb['range'], 'price' => $bbb['price']);						
	}
}		
	return $products;
}	
	
	
if($_POST['action']=='dropdown' && is_numeric($_POST['update_fueltype']) && $_POST['county']!='')
{
	$products = Prices();	
	if(is_array($products[$_POST['update_fueltype']]['locations'][strtolower($_POST['county'])]))
	{
		foreach($products[$_POST['update_fueltype']]['locations'][strtolower($_POST['county'])] as $l)
		{
			echo'<option value="'.$l['range'].'">'.$l['range'].'</option>';
		}
	}
	else
	{
		echo'<option value="0">Unavailable</option>';
	}
}

if(is_numeric($_POST['update_quantity']) && is_numeric($_POST['update_fueltype']) && $_POST['update_county']!='')
{
	$products = Prices();
	echo CalculateCost(intval($_POST['update_quantity']), intval($_POST['update_fueltype']), $_POST['update_county'], $products);
}

function CalculateCost($quantity, $fueltype, $county)
{
	$p = Prices();
	if(!$p){ $p=$products; }
	if(is_numeric($quantity) && is_numeric($fueltype))
	{
		foreach($p[$fueltype]['locations'][strtolower($county)] as $p)
		{
			if($p['range'] == $quantity)
			{
				return number_format($p['price']*$quantity, 2, '.', '');
			}
		}
	}
	else
	{
		return 'An error occured while calculating your quote';
	}
}

function FuelName($fueltype)
{
	$p = Prices();
	return $p[$fueltype]['name'];
}

This might be the problem line

$sql = "SELECT * FROM (SELECT * FROM `zzz_oilprices` ORDER BY `timestamp` DESC) as tmp WHERE `type` = 'gas' GROUP BY `location` ORDER BY `timestamp` DESC";

This was updates to the hosting not wordpress
Any help appreciated.

Mod edit: added tags around code to improve formatting; replace 2 instances of database prefix with “zzz”.

I don’t have an answer to your question, but, do want to point out that this code is problematic for security for a variety of reasons.

  1. $p[1] is used raw from the $_POST variable without being validated. Ensure you’re either typecasting or otherwise validating all values; never assume.

  2. Directly querying the database isn’t necessarily problematic, however, your queries are raw and not run through the prepare() method. This is a serious hole in the application that could get the site hacked. Using the built-in methods for accessing the database is the best course of action.

  3. After retrieving data, it is being printed directly to the screen without any escaping. Again, this makes it much more likely the site can be hacked.

  4. The use of in_array() here is problematic for the possibility of false positives and unexpected results. I’ve (previously) explained why in this Tweet; see the comments.

  5. Your database prefix was exposed in two places in your code above.

Note: I moderated your post to add code formatting tags and to replace your database prefix with “zzz”.

3 Likes

I inherited the code when I took over the site. The prices and customer orders are password protected and accessed separately from the wordpress install.
The look up code is in a php file in the theme. I have another site doing something similar but using a custom plugin and woo. This has its problems as well. I really need to get this working. If it has security problem then it will be back to the drawing board. If I need to pay someone to re-do the functional part of the code, then that is what I will need to do. Any takers?

1 Like

Completely understand. You might try a quick post in the jobs forum?

https://forums.classicpress.net/c/jobs-forum

2 Likes