...

View Full Version : unable to get results



jasonpc1
06-19-2011, 01:16 PM
can anyone tell me where i am going wrong with my code, i am wanting to display all the items that are stored in sessions but grab the related info from mysql.



<?
$cartCount = 0; $cartCost = 0;
foreach($_SESSION['cart'] as $id => $value) {
$cartCount += $value;
$getCost = mysql_query("SELECT * FROM `products` WHERE `Pid` = '" . $id . "' LIMIT 1");
$itemCost = mysql_fetch_assoc($getCost);
$cartCost += ($itemCost['Price'] * $value);
echo("Product ID: " . $itemCost['Pid'] . " : Product Name: " . $itemCost['Product Title'] . " : Price: " . $itemCost['Price'] . " : ");
}
?>

Old Pedant
06-19-2011, 09:33 PM
DEBUG DEBUG DEBUG.

Replace this:

$getCost = mysql_query("SELECT * FROM `products` WHERE `Pid` = '" . $id . "' LIMIT 1")

with this:


$sql = "SELECT * FROM `products` WHERE `Pid` = '" . $id . "' LIMIT 1";
echo "DEBUG SQL: " . $sql . "<br/>";
$getCost = mysql_query( $sql );

Do the SQL statements that generates look reasonable? Or are the PID values it looks for bogus?

jasonpc1
06-19-2011, 10:04 PM
This code works and produces the price of each item to work out the cost based on the number of items that are stored in the session data

but when i change the query to grab all the database info for the row it kicks up a fuss.



<?
$cartCount = 0; $cartCost = 0;
foreach($_SESSION['cart'] as $id => $value) {
$cartCount += $value;
$getCost = mysql_query("SELECT `Price` FROM `products` WHERE `Pid` = '" . $id . "' LIMIT 1");
$itemCost = mysql_fetch_assoc($getCost);
$cartCost += ($itemCost['Price'] * $value);
//echo("Product ID: " . $itemCost['Pid'] . " : Product Name: " . $itemCost['Product Title'] . " : Price: " . $itemCost['Price'] . " : ");
}
?>
<br class="clearfloat" /><br />
Total items in cart: <?=$cartCount;?><br />Grand Total: <?=sprintf("%02.2f", $cartCost);?>p

Old Pedant
06-19-2011, 10:26 PM
Well, try specifically asking for only the fields you need. Maybe you have an unrelated field that is causing the problem?


$sql = "SELECT Pid, Price, `Product Title` FROM products WHERE Pid = '" . $id . "' LIMIT 1";


Incidentally, *IF* Pid is a NUMERIC field, then the apostrophes in there (surrounding the $id value) are technically incorrect. MySQL lets you use them, but it allows a lot of sloppiness that other DBs disallow.

Old Pedant
06-19-2011, 10:28 PM
Oh, and did you use a DB tool (not PHP) to test one or more of the $sql values you see from the debugging? To make sure that they indeed return all the data you expect?

bullant
06-20-2011, 05:11 AM
Incidentally, *IF* Pid is a NUMERIC field, then the apostrophes in there (surrounding the $id value) are technically incorrect. MySQL lets you use them, but it allows a lot of sloppiness that other DBs disallow.

I don't see it as sloppiness at all but as a last "back up" defence against possible attacks.

Although all user inputs to an sql query should be validated and sanitised first there is no harm at all wrapping numbers in quotes afterwards as well notwithstanding MySQL might be one of few or the only DB that allows it.

This is from the MySQL manual (http://dev.mysql.com/doc/refman/5.5/en/security-guidelines.html)


A common mistake is to protect only string data values. Remember to check numeric data as well. If an application generates a query such as SELECT * FROM table WHERE ID=234 when a user enters the value 234, the user can enter the value 234 OR 1=1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1=1. As a result, the server retrieves every row in the table. This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotation marks around the numeric constants: SELECT * FROM table WHERE ID='234'. If the user enters extra information, it all becomes part of the string. In a numeric context, MySQL automatically converts this string to a number and strips any trailing nonnumeric characters from itI routinely wrap numbers in quotes when using MySQL since it does no harm.

Old Pedant
06-20-2011, 08:15 PM
If you will never use any database except MySQL, go for it.

I use MySQL, Oracle (well, seldom, but still...), SQL Server, Objectivity/DB, and a handful of others.

And if I got in the habit of putting numbers in apostrophes, I'd keep getting syntax errors from those other DBs.

I don't know what functions PHP has to make it easy/possible to ensure that a number *is* a number. In Java and C# and C++ and VBScript, there are functions to convert a string to a number and/or ensure that a string *is* just a number. And I routinely use those to validate strings-as-numbers.

So, for example, in ASP code I would do:


SQL = "SELECT * FROM table WHERE id = " & CLNG(userinput)

And if the user input is *NOT* a number (or if it has junk after the number) that either tosses an error (if not a number) or strips the junk after the number. And so I never have to worry about SQL injection.

If PHP doesn't have some simple equivalent like this, then yeah, I can see why you'd use the apostrophes.

MattF
06-20-2011, 08:42 PM
If PHP doesn't have some simple equivalent like this, then yeah, I can see why you'd use the apostrophes.

It has plenty of functionality in that regard, intval() being one example. Personally, I'm off the same mindset as yourself. Design correctly and avoid the hacks, hence no apostrophes for integer fields.

bullant
06-21-2011, 12:16 AM
If you will never use any database except MySQL, go for it.


Over the years I have used other databases including Oracle as well.

Nowadays if a client asks me to provide the hosting then I will be using MySQL if a database is required and I will consequently wrap numbers in quotes since they do no harm at all and can provide some "back up" defence against certain types of attack as described earlier.

If the client says they have their own hosting then I ask them which database they will be using if a database is required. If they say MySQL then I will wrap numbers in quotes. If it is another database then I won't.

It's not a time intensive task for me to remove quotes in the future in the unlikely event they will ever need to be.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum