multipule queries

[Icewolf]

Hi

I need some advice on what I have coded is correct. I created a shoping cart. I have the screen where the user selects the item, from there it goes to their shopping cart. From here is where I am struggling. They don't use money to make purchases the items. There is a reward system that they use to purchase the item. The first thing I need to do is validate that they have enough points to buy the item. Here is the first part of the code.

<?php
include 'connect.php';
include 'timeout.php';
include 'header_signin.php';

	//check for sign in status
	if(!$_SESSION['signed_in'])
	{
		echo 'You must be signed in to view cart.';
	}
	else
{
$sql ="SELECT point_earn, prod_price from rewards, shp_order_items, shp_products
	   where user_id = member_id
	   and product_id = prod_id
	   and user_id = '" . mysql_real_escape_string($_SESSION['user_id']) . "'
	   and ordr_item_id = '{$_GET['id']}'";
 $result = mysql_query($sql);
 
	if(!$result)

	while($row = mysql_fetch_assoc($result))
{
               
			$pe = $row['point_earn'];
			$pp = $row['prod_price'];
			 
			
}

if ($pe >= $pp)

from here I need a couple of things to happen. I need to move these items from one table to the next to show the items have been purchased. From there I need to subtract the price from the rewards they already have. Then finally update that row so it will no longer show in their cart. I know the first part and the last part work. But the update the rewards is not working. Here is the rest of the code.

$sqlint = "INSERT INTO `shp_orders`(`ordr_item_id`, `order_date`, `order_status`) VALUES ('{$_GET['id']}',Now(),'Processing')";
   
   $results = mysql_query($sqlint);
   
   $sqlup = sprintf ("UPDATE `rewards` SET `point_earn` = `point_earn` - $pp 
   WHERE member_id = '" . mysql_real_escape_string($_SESSION['user_id']) . "'");
   
   $resultup = mysql_query($sqlup);
   
   $sqloup = "UPDATE `shp_order_items` SET `item_ordered`= -1 WHERE ordr_item_id = '{$_GET['id']}'";
   
   $resultoup = mysql_query($sqloup);
   
   echo 'Item has been ordered';
   //header('Refresh: 3;url=getitemsshp.php');
   echo '<br>' . $sqlup . '<br>' . mysql_error();
}

else
{
   echo 'There are not enough bank points to purchase item.';
    header('Refresh: 3;url=getitemsshp.php');
}
}

include 'footer.php';
?>

When I run the debugger code here is what I am getting. What I am not sure of is if I have the multipule queries are correct.

Item has been ordered
UPDATE `rewards` SET `point_earn` = `point_earn` - WHERE member_id = '3'

[MDCode]

Your echoed query shows that the value of $pp is empty so it seems your first query is failing. Also, I do hope you realize $_GET variables are vulnerable to SQL Injectjon as well.

 

Edit: The page should actually not be continuing as you have no open bracket after the if false check

Edited November 17, 2013 by SocialCloud

[Icewolf]

Thanks Social I do have the open bracket in there after the if statement I must not of copied it. How else can you do the values without doing the $_GET?

[JIXO]

Try the PHP PDO extension, its more secure than the old mysql extension. Sql injections are nasty and you can not protect your self 100% all time, no matter how much work and filtering done there is always a Snowden out there LOL.

 

Take a look at the documentation , they even mentioned quires are immuned against sql injection.

[Icewolf]

Thanks for the suggestion Jixo. The PHP PDO creates the connection to the database at the time it runs the sql and then terminates the connection afterwards correct?

[JIXO]

A PDO connection is closed by destroying it's object.

The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.

So

$this->db = null;

Will close the connection.

 

Look at this bug report to have more understanding about how PDO closes the connection here.