If this is your first visit, check our documentation. To post, register or login.
Forums / Zen Cart Code Suggestions / Quotes removed from integer fields in queries using quoted integer values

Quotes removed from integer fields in queries using quoted integer values

Started by mc12345678 on 1 Oct 2015, 4:24 PM · Views: 77

Results 1 to 2 of 2
1 Oct 2015, 4:24 PM
#1
mc12345678 avatar

mc12345678

Totally Zenned

Join Date:
Jul 2012
Posts:
16,908
Plugin Contributions:
2

Quotes removed from integer fields in queries using quoted integer values

Been publicly working on an attribute related plugin. I'm aware that there has been discussion (somewhere in the forum) of a potential need for an overhaul on the attribute system, so in a way not sure if the changes suggested below ought to be applied to the attributes portion of the system. That said, in review of code to possibly assist another poster, I just saw core code that is not specifically attribute dependent and affects a majority if not all product prices displayed.

So, background, because the use of attributes can be heavily database associated, in generating new queries I've been trying to make sure that all the i's are dotted and t's are crossed... One thing in my internet research that supposedly places a drain on the MySQL server is for it to convert from one type of data to another when making a comparison.

The drain on the server seems to makes sense, give it a string to compare against an integer declared table field, and one or both need to be converted or at least related to each other. do this many times over in a query (perhaps there is also some server side setup or incorrect setup that goes into this "issue", but...) and it would seem that this exacerbates the issue. There are other places though in the code, unrelated to attributes, where such a comparison is performed where first php converts the value to an integer, then that integer is concatenated with the string of the query such that the integer is captured between single quotes to be offered to the MySQL server as a string to be compared against an integer field.

While there has been (haven't looked back as to when) an additional database function bindVars (typically accessed by $db->bindVars()) that when used "properly" will insert/replace the "anchor" with the appropriate punctuation depending on the declared data type, performing that replacement throughout the code seems like it would be more time consuming and potentially error prone at the moment than perhaps the below suggestion. Further, not all queries within the ZC code are written to directly support the use of bindVars such as when the query is hard coded into a $db->Execute() statement.

An example: ZC 1.5.4, includes/functions/functions_prices.php, the function zen_get_products_actual_price

The query to perform the product check is:

$product_check = $db->Execute("select products_tax_class_id, products_price, products_priced_by_attribute, product_is_free, product_is_call from " . TABLE_PRODUCTS . " where products_id = '" . (int)$products_id . "'" . " limit 1");

Notice that after the equals on the products_id that there is a single quote surrounding the integer cast of the $products_id. This sequence results in a SQL statement in this portion of the code like:
where products_id = '5' limit 1;

products_id is an integer datatype and 5 is an integer that is being put within the single quote pair to treat it as a string.

it would seem that this particular query should be:

 $product_check = $db->Execute("select products_tax_class_id, products_price, products_priced_by_attribute, product_is_free, product_is_call from " . TABLE_PRODUCTS . " where products_id = " . (int)$products_id . " limit 1");

The next "issue" is that this inclusion of single quote around an integer occurs throughout much of the store code. So yes, I understand/get the cost versus reward of replacing that type of statement in so much of the code.

This is also partially why I am trying to identify if this is something that if performed would be considered for inclusion. While it seems like perhaps a regex find and replace might be possible for the majority of the code (assuming standards set and followed throughout), it 1) doesn't seem like it would be quick to go through/find all of the files, 2) seems like it would affect so many files that it may be considered more of a trouble for the upgrade process than it's worth... and 3) I have seen some post over the years that they may actually go and do this type of replacement or make other database access "enhancements" as part of installing the program and any additional plugins.

Lastly, and I also see this as a rebuttal topic, any well installed and managed site/server ought to be able to handle this nuance easily and without effect on operation. It does seem to me though, that as well written as ZC is, that this is something that could provide new contributors with better examples on how to provide more optimal code.

So not a bug as doesn't break anything as written, but would consider it a suggestion for improved performance especially for those that do not seek better resources to support the accepted version of ZC...

ZC Installation/Maintenance Support <- Site
Contribution for contributions welcome...

1 Oct 2015, 5:42 PM
#2
drbyte avatar

drbyte

Sensei

Join Date:
Jan 2004
Posts:
63,512
Plugin Contributions:
176

Re: Quotes removed from integer fields in queries using quoted integer values

You make a valid point, and it's one I've looked at often.

In all the tests I've done it seems consistent that MySQL and PHP have no trouble with the mixed treatment of integers as strings. I see no problem, even performance-wise, with leaving these as-is (ie: not doing a mass overhaul just for the sake of removing these non-problematic quotes).

That said, when I'm working on changes in sections of code that do have these unnecessary quotes I will typically clean up the query by removing them, and sometimes investigating additional optimizations for the same query.

The most troublesome part of just cleaning up these quotes is that it makes upgrades harder ... especially for files that are frequently changed by plugins too.

So, my general rule-of-thumb is to minimize pain for upgraders where possible, except when intentionally embracing bigger changes that are already going to require careful attention to upgrade or integrate.

.
Zen Cart - putting the dream of business ownership within reach of anyone!
Donate to: DrByte directly or to the Zen Cart team as a whole

Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.