Bug causing mysql errors - cause detected

[ktmtalk]

I was seeing several mysql errors... 4 from one guy, and 1 from another.

Here is the error message for each one:

The query returned with an errorcode of:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USD',buyit='no',rating='Excellent',zipcode='56318',adoptions=0,isauction=0,durat' at line 1
If you need assistence or feel this is a 'bug'; please report it to our support forums at: Photopost Pro Bug Reports - PhotoPost Community
To turn off these emails, set $debug=0 in your config-inc.php file.

Here are examples of the sql statements:

UPDATE cp_products SET enddate=1258595766,price='3600',shipping='\',extra1=''....

UPDATE cp_products SET enddate=1258600536,price='2300.00',shipping='\',extra1=''....

UPDATE cp_products SET enddate=1258600738,price='4200.00',shipping='\',extra1=''....

UPDATE cp_products SET price='4200',shipping='\',reserve='0.00',title=.....

Look at the shipping values.... "\"
That \ "escapes" the next character (the ending quote) and causes the mysql error.

Ok, I showed the sql statements, and the errors, and the reason. Wanna know how it actually happened??? Well, of course you do!

Even though it's supposed to be numbers only, the seller put .... At Buyer's Expense in the shipping field.

I'm guessing that the code saw the single quote and escaped it it with a "\" ... then it looks like it removed everything EXCEPT the \ ....and the end result was..... shipping="\" and that was what was trying to be saved in the sql statement.

So.... the qwik solution is..... ?????


.

[Chuck S]

I would need the full errors. Your first is not showing an error of the query

I really need the full context of these errors.

I mean shipping does not have a / in it anywhere so thats why I need to know more info.

[ktmtalk]

Originally Posted by Chuck S

I would need the full errors. Your first is not showing an error of the query

I really need the full context of these errors.

I mean shipping does not have a / in it anywhere so thats why I need to know more info.

You should be able to test it with your own system.... select "place an ad" and put Buyer's Expense in the shipping and it should bomb out immediately

Entering Buyer's Expense in shipping...
In uploadproduct.php,

$wasuploaded = "no";
$testname = $_FILES['theimage']['name'][0];
$realsize = $_FILES['theimage']['size'][0];

$price = str_replace("$", "", $price);
$price = ereg_replace( "[^0-9\.]", "", $price );

echo "shipping1=$shipping<br>"; // retruns Buyer's Expense
$shipping = str_replace("$", "", $shipping);
echo "shipping2=$shipping<br>"; // returns Buyers' Expense
$shipping = ereg_replace( "[^0-9\.]", "", $shipping );
echo "shipping3=$shipping<br>"; // returns /

$reserve = str_replace("$", "", $reserve);
$reserve = ereg_replace( "[^0-9\.]", "", $reserve );
$category = (int)$category;
$quantity = (int)$quantity;

It's acting like magic quotes are on, but they are not.

But since you asked for it.... here is the full error message:

We're sorry, but the system experienced an unrecoverable error. Please try again later.

Script:

Query: UPDATE cp_products SET enddate=1258665922,price='100',shipping='\',extra1='',extra2='',extra3='',extra4='',extra5='',extra6='',currency='USD',buyit='no',rating='Excellent',zipcode='',adoptions=0,isauction=0,duration=15,reserve='',quantity='1' WHERE id=172

Result: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USD',buyit='no',rating='Excellent',zipcode='',adoptions=0,isauct

Database handle: Resource id #5

.

[Chuck S]

well you cant put that in a field for an integer

I can explicitly set that string to INT

[ktmtalk]

Originally Posted by Chuck S

well you cant put that in a field for an integer

I can explicitly set that string to INT

or we could just put:
$shipping = stripslashes($shipping);

and be done with it

[Chuck S]

Well you can do that I am going to go the other way and make sure it can only be an INT. Your not going to be able to store anything but an interger in that field. It is a floating point mysql field.

[ktmtalk]

Originally Posted by Chuck S

Well you can do that I am going to go the other way and make sure it can only be an INT. Your not going to be able to store anything but an interger in that field. It is a floating point mysql field.

ok, let us know when it's ready for download

[Chuck S]

Just make the typecast lines in uploadproduct this

Content visible to verified customers only.

[ktmtalk]

better solution than my stripslashes...

probably wanna change "reserve" to INT too and then again the same thing in editproduct too

[Chuck S]

yep indeed

[ktmtalk]

Originally Posted by Chuck S

yep indeed

ain't debugging fun

I am a professional coder in C# and I'm constantly amazed at some of my code: "crap, how long has that bug been there and... why hasn't someone said something before now??" in some of my projects.

[Chuck S]

yeah thats how it goes if no one reports it you aint gonna find it.

[ktmtalk]

Originally Posted by Chuck S

Just make the typecast lines in uploadproduct this

Content visible to verified customers only.

if you do it this way, it causes even more problems.... the users will not see the "numbers only" and inevitably, will enter a $ and a comma in their price. (ie $1,800).

Since it is now typecast as an INT, the conversion of the $ sets the value to ZERO, which in turn, sets the price to "Best Offer".

Yeah, I know... tell the seller to follow instructions but after having to change a dozen of these myself, I think it needs to be coded to handle issues like this - they are going to happen.

.

[Chuck S]

okay but we already run a stripslashes on the line thats what the typecast feature is.

You can certainly add extra stripslashes lines like you say in there but you know its still not going to record a string as I stated before. A float field is still an INT field in a way

[Chuck S]

See love the morning and coffee.

The code is doing what it is suppose to do. See we code it as a string and and it passes through the typecast function which adds the slash because going into mysql you need that. Now we issue an ereg_replace line that removes everything except the actual number. So I dont know why you think setting it to an INT does not work the end result is we pass an integer to the database. You input $199.50

It passes through the code it is returned as 19950. That inturn is used in the query and you end up with 199.50 stored in the database since its a 2point floating field.

The problem is when you use a non interger in that line what is left after the regex is \. You cant enter a string value into a float field.

You can use a striipslash sure and the variable comes back empty thats fine. I choose this in uploadproduct.php because it lets me backwards remember why I just posted this. You will see there are no stripslash or addslash lines thrown throughout our code as there is a function which properly does this.

Content visible to verified customers only.

[ktmtalk]

Originally Posted by Chuck S

So I dont know why you think setting it to an INT does not work

because it doesn't work.

[ktmtalk]

Originally Posted by Chuck S

See love the morning and coffee.

The code is doing what it is suppose to do. See we code it as a string and and it passes through the typecast function which adds the slash because going into mysql you need that. Now we issue an ereg_replace line that removes everything except the actual number. So I dont know why you think setting it to an INT does not work the end result is we pass an integer to the database. You input $199.50

It passes through the code it is returned as 19950. That inturn is used in the query and you end up with 199.50 stored in the database since its a 2point floating field.

The problem is when you use a non interger in that line what is left after the regex is \. You cant enter a string value into a float field.

You can use a striipslash sure and the variable comes back empty thats fine. I choose this in uploadproduct.php because it lets me backwards remember why I just posted this. You will see there are no stripslash or addslash lines thrown throughout our code as there is a function which properly does this.

Content visible to verified customers only.

Thats not in the code that I downloaded:

$shipping = str_replace("$", "", $shipping);
$shipping = ereg_replace( "[^0-9\.]", "", $shipping );

$reserve = str_replace("$", "", $reserve);
$reserve = ereg_replace( "[^0-9\.]", "", $reserve );
$category = (int)$category;
$quantity = (int)$quantity;

if ( $testname == "" || $testname == "none" || $realsize < 1 )
{
$forwardid = process_image( $testname, $filepath, $category, 1 );

I don't see the:
$price = str_replace("\", "", $price);
$shipping = str_replace("\", "", $shipping);

[Chuck S]

As stated those lines you would add after changing the typecast lines back to string

You can download the build and upload editproduct and uploadproduct php files

[ktmtalk]

Originally Posted by Chuck S

As stated those lines you would add after changing the typecast lines back to string

You can download the build and upload editproduct and uploadproduct php files

it was not stated... your original post just said to change the typecast... no mention of the two additional str_replace lines.

And even if you do add the two lines, you still get a "Best Offer" when someone enters something like "$1,111".


Never mind about this stuff... you really get defensive on this stuff. I thought I was helping by showing you things that needed attention.... like showing "Delete Ad?" when you really meant "Delete Comment"... or showing "Delete Image" when you really meant "Delete Ad", and... never mind.

.

[Chuck S]

Bug causing mysql errors - cause detected

I said I choose to add those lines and I made them bold so you know they are new at any rate feel free to download the code again.