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...