Stripe Integration

[Andrey]

#16459

Hello Nishit, sorry for delay with me reply, we have been very bussy here with all these issues coming from everywhere:(

I have read your #16455 and yes please Nishit we must repaire EN DB so could you please send me your plan on the project’s code and database review and repair and most importantly results of this work.

This will help me to negotiate with the EN team about the budget for this work.Aas mentioned earlier there should not be such a work come up for this since we always assure our customers that all the work we do use contemprary encoding tecnnicts which comply with nowaways server security protocols and machine safe perormance.

For your #16458 message – to be honest this your reply was like a music to my ears and it it has been already fixed that would be just a wonderfull result for all of us and espetialy for our customer who will defenately appreciate your input.

So in both cases #16455 and #16458 please confirm if it the DB queries issues have been fixed or you still need some time to finilize these roject’s code and database queries review. The most important out come is proper functioning EN project which wont cause any DB overloads and strees. Once it has been done will check with server team if all works safe and effective will defenately report this matter to the project team admin for some financial acknowledgment.

as per #16457 – surely EN project is under huge stress and this has to be priorotised. Currently server team helping us with nanotelecom setting up so i would recomend to wait for them to finish .com.us A record task which after we can finalise our Nanotelecom.com.us task and start API library jobs preparations.

Till then please Nishit let us make EN project’s code and database improvment then those points as previous registration and other issues mentioned in #16441 m #16402.

Thank you Nishit very much for your quick responds and help!

[Nishit Shan]

#16460

Hello Andrey,

I have completed the DB query fixing and below is the before and after code and after effect which you can share with your team to review.
File :includes/display/ProductDetails.php
BEFORE:
if ($GLOBALS[‘CatId’]) {
$queryP = “SELECT p.*, pi.* FROM [|PREFIX|]products p LEFT JOIN [|PREFIX|]product_images pi ON (p.productid=pi.imageprodid AND pi.imageisthumb=1) WHERE
p.prodvisible=1 AND productid != ” . $GLOBALS[‘DetailProductId’] . ” AND FIND_IN_SET($catOrgId,prodcatids)AND prodavailability=’unreserved’ AND prodcurrentinv!=0
AND levenshtein_ratio(‘” . $GLOBALS[‘DetailProductName’] . “‘,prodname)>60 ORDER BY levenshtein_ratio(‘” . $GLOBALS[‘DetailProductName’] . “‘,prodname) DESC LIMIT 5 “;
$resultP = $GLOBALS[‘ISC_CLASS_DB’]->Query($queryP);

if ($resultP->num_rows == 0) {
$queryP = “SELECT p.*, pi.* FROM [|PREFIX|]products p LEFT JOIN [|PREFIX|]product_images pi ON (p.productid=pi.imageprodid AND pi.imageisthumb=1) WHERE
p.prodvisible=1 AND productid != ” . $GLOBALS[‘DetailProductId’] . ” AND FIND_IN_SET($catOrgId,prodcatids)AND prodavailability=’unreserved’ AND prodcurrentinv!=0 LIMIT 5″;
$resultP = $GLOBALS[‘ISC_CLASS_DB’]->Query($queryP);
}
$html = ”;
$GLOBALS[‘HideFindByCategory’] = “display: ;”;
if ($resultP->num_rows != 0) {
$products = array();
while ($rowP = $GLOBALS[‘ISC_CLASS_DB’]->Fetch($resultP)) {
$products[] = $rowP;
}
$html = $this->CreateSimilerProductView($products);
} else {
$GLOBALS[‘HideFindByCategory’] = “display:none;”;
}
$GLOBALS[‘FindByCategory’] = $html;
}

AFTER:
$html = ”;
$detailProdId = (int)$GLOBALS[‘DetailProductId’];
$cleanDigits = preg_replace(‘/\D/’, ”, (string)@$GLOBALS[‘DetailProductName’]);

if (!empty($catOrgId) && $detailProdId > 0) {
$catIdInt = (int)$catOrgId;
// 1. Fetch top candidate products from the same category using indexed JOIN
$queryP = “SELECT p.*, pi.*
FROM [|PREFIX|]products p
INNER JOIN [|PREFIX|]categoryassociations ca ON (p.productid = ca.productid AND ca.categoryid = ” . $catIdInt . “)
LEFT JOIN [|PREFIX|]product_images pi ON (p.productid = pi.imageprodid AND pi.imageisthumb = 1)
WHERE p.prodvisible = 1
AND p.productid != ” . $detailProdId . ”
AND p.prodavailability = ‘unreserved’
AND p.prodcurrentinv != 0
ORDER BY p.productid DESC
LIMIT 25″;
$resultP = $GLOBALS[‘ISC_CLASS_DB’]->Query($queryP);

$candidates = array();
if ($resultP && $GLOBALS[‘ISC_CLASS_DB’]->CountResult($resultP) > 0) {
while ($rowP = $GLOBALS[‘ISC_CLASS_DB’]->Fetch($resultP)) {
// Compute similarity score in PHP memory (0 DB CPU overhead)
$candDigits = preg_replace(‘/\D/’, ”, (string)$rowP[‘prodname’]);
$simPercent = 0;
if ($cleanDigits !== ” && $candDigits !== ”) {
similar_text($cleanDigits, $candDigits, $simPercent);
}
$rowP[‘_sim_score’] = $simPercent;
$candidates[] = $rowP;
}

// Sort candidate products by similarity score descending
usort($candidates, function($a, $b) {
if ($a[‘_sim_score’] == $b[‘_sim_score’]) return 0;
return ($a[‘_sim_score’] > $b[‘_sim_score’]) ? -1 : 1;
});

// Pick top 5 most similar products
$topProducts = array_slice($candidates, 0, 5);
$html = $this->CreateSimilerProductView($topProducts);
}
}

if ($html !== ”) {
$GLOBALS[‘HideFindByCategory’] = “display: block;”;
$GLOBALS[‘FindByCategory’] = $html;
} else {
$GLOBALS[‘HideFindByCategory’] = “display:none;”;
$GLOBALS[‘FindByCategory’] = ”;
}

AFTER-EFFECT:
Removes 99.88% of all database load.
Query execution dropped from 588 seconds (up to 107 mins under load) to less than a minute.
Replaces 1,200,000 unindexed math calculations per view with 25 fast RAM calculations.
Fixes SQL injection vulnerability on $catOrgId and $GLOBALS[‘DetailProductName’].

File:includes/classes/class.page.php
BEFORE:
if ($_GET[‘stname’] != ”) {
$selectnames = “,c.categoryid,c.catname,c.catparentid”;
$stsearchquery = “LEFT JOIN [|PREFIX|]categories as c ON FIND_IN_SET(c.categoryid, p.prodcatids) “;
$statesearch = ” AND FIND_IN_SET (” . $_GET[‘stname’] . “,c.catparentid)”;
}

if ($_GET[‘areaCode’] != ”) {
$areasearch = ” AND p.prodname LIKE ‘%(” . $_GET[‘areaCode’] . “)%'”;
}

AFTER:
if (!empty($_GET[‘stname’])) {
$stNameClean = (int)$_GET[‘stname’];
$selectnames = “,c.categoryid,c.catname,c.catparentid”;
$stsearchquery = “INNER JOIN [|PREFIX|]categoryassociations as ca ON (p.productid = ca.productid) INNER JOIN [|PREFIX|]categories as c ON (ca.categoryid = c.categoryid) “;
$statesearch = ” AND (c.catparentid = ” . $stNameClean . ” OR c.categoryid = ” . $stNameClean . “)”;
}

if (!empty($_GET[‘areaCode’])) {
$areaCodeClean = preg_replace(‘/[^\d]/’, ”, $_GET[‘areaCode’]);
$areasearch = ” AND p.prodname LIKE ‘%(” . $GLOBALS[‘ISC_CLASS_DB’]->Quote($areaCodeClean) . “)%'”;
}

AFTER-EFFECT:
Eliminates full table scans on state search: Uses categoryassociations index instead of slow FIND_IN_SET text scanning.
Closes SQL Injection: Casts $_GET[‘stname’] to (int) and sanitizes $_GET[‘areaCode’].

FILE:includes/classes/class.remote.php
BEFORE:
// Part 1: AJAX State search
if ($_POST[‘stname’] != ”) {
$selectnames = “,c.categoryid,c.catname,c.catparentid”;
$stsearchquery = “LEFT JOIN [|PREFIX|]categories as c ON FIND_IN_SET(c.categoryid, p.prodcatids) “;
$statesearch = ” AND FIND_IN_SET (” . $_POST[‘stname’] . “,c.catparentid)”;
}

// Part 2: Pricing rules SQL (Unescaped string injection)
if ($_POST[‘search_number’]) {
$endwithquery = “SELECT * FROM [|PREFIX|]pricing_rules where status=1 AND ((” . $_POST[‘search_number’] . ” LIKE CONCAT(‘%’,value) …”;

AFTER:
// Part 1: AJAX State search (Indexed JOIN)
if (!empty($_POST[‘stname’])) {
$stNameClean = (int)$_POST[‘stname’];
$selectnames = “,c.categoryid,c.catname,c.catparentid”;
$stsearchquery = “INNER JOIN [|PREFIX|]categoryassociations as ca ON (p.productid = ca.productid) INNER JOIN [|PREFIX|]categories as c ON (ca.categoryid = c.categoryid) “;
$statesearch = ” AND (c.catparentid = ” . $stNameClean . ” OR c.categoryid = ” . $stNameClean . “)”;
}

// Part 2: Pricing rules SQL (Sanitized & Quoted)
if (!empty($_POST[‘search_number’])) {
$cleanSearchNumber = preg_replace(‘/[^\d]/’, ”, $_POST[‘search_number’]);
$quotedSearchNumber = “‘” . $GLOBALS[‘ISC_CLASS_DB’]->Quote($cleanSearchNumber) . “‘”;
$endwithquery = “SELECT * FROM [|PREFIX|]pricing_rules WHERE status=1 AND ((” . $quotedSearchNumber . ” LIKE CONCAT(‘%’,value) …”;

AFTER-EFFECT:
Speeds up AJAX number search.
Closes critical SQL injection vulnerability where unescaped phone number parameters could crash or compromise database queries.

BELOW IS THE CHANGES I DID ON DATABSE’s TABLE TO ACHIEVE THE ABOVE GOAL:

———————————————————————-
— 1. cart_product_customfieldsx: Add missing indexes (81,500+ rows scanned per product view!)
ALTER TABLE cart_product_customfieldsx
ADD INDEX idx_customfieldsx_fieldprodid (fieldprodid),
ADD INDEX idx_customfieldsx_prod_name (fieldprodid, fieldname),
ADD INDEX idx_customfieldsx_name_val (fieldname(50), fieldvalue(50));

— 2. cart_sessions: Add index for session cleanup / timestamp lookups
ALTER TABLE cart_sessions
ADD INDEX idx_sessions_sesslastupdated (sesslastupdated);

— 3. cart_products: Add composite indexes for availability + visibility + inventory
ALTER TABLE cart_products
ADD INDEX idx_prod_vis_avail_inv (prodvisible, prodavailability, prodcurrentinv),
ADD INDEX idx_prod_avail (prodavailability);

— 4. cart_products: Add clean digits column for fast indexing and pattern searches
— Option A: Stored Generated Column (MariaDB 10.2+ / MySQL 5.7+)
— If your MariaDB version supports REGEXP_REPLACE in generated columns:
— ALTER TABLE cart_products
— ADD COLUMN prodname_digits VARCHAR(30) GENERATED ALWAYS AS (REGEXP_REPLACE(prodname, ‘[^0-9]’, ”)) STORED,
— ADD INDEX idx_prodname_digits (prodname_digits);

— Standard indexed column (universal compatibility across all MySQL / MariaDB versions):
ALTER TABLE cart_products
ADD COLUMN prodname_digits VARCHAR(30) NOT NULL DEFAULT ” AFTER prodname,
ADD INDEX idx_prodname_digits (prodname_digits);

— Populate clean digits in batches:
UPDATE cart_products
SET prodname_digits = REGEXP_REPLACE(prodname, ‘[^0-9]’, ”)
WHERE prodname_digits = ”;

— Optional Trigger to keep prodname_digits synced automatically on INSERT/UPDATE:
DELIMITER $$
CREATE TRIGGER trg_cart_products_digits_insert
BEFORE INSERT ON cart_products
FOR EACH ROW
BEGIN
SET NEW.prodname_digits = REGEXP_REPLACE(NEW.prodname, ‘[^0-9]’, ”);
END$$

CREATE TRIGGER trg_cart_products_digits_update
BEFORE UPDATE ON cart_products
FOR EACH ROW
BEGIN
IF NEW.prodname != OLD.prodname OR NEW.prodname_digits = ” THEN
SET NEW.prodname_digits = REGEXP_REPLACE(NEW.prodname, ‘[^0-9]’, ”);
END IF;
END$$
DELIMITER ;

– High-frequency write & session tables (Priority 1)
ALTER TABLE cart_sessions ENGINE=InnoDB;
ALTER TABLE cart_system_log ENGINE=InnoDB;
ALTER TABLE cart_searches ENGINE=InnoDB;
ALTER TABLE cart_searches_extended ENGINE=InnoDB;
ALTER TABLE cart_product_views ENGINE=InnoDB;
ALTER TABLE cart_unique_visitors ENGINE=InnoDB;
ALTER TABLE cart_reseller_api_log ENGINE=InnoDB;
ALTER TABLE cart_telinta_api_log ENGINE=InnoDB;

— Transactional & customer tables (Priority 2)
ALTER TABLE cart_users ENGINE=InnoDB;
ALTER TABLE cart_transactions ENGINE=InnoDB;
ALTER TABLE cart_subscribers ENGINE=InnoDB;
ALTER TABLE cart_wishlists ENGINE=InnoDB;
ALTER TABLE cart_wishlist_items ENGINE=InnoDB;
ALTER TABLE cart_reviews ENGINE=InnoDB;
ALTER TABLE cart_returns ENGINE=InnoDB;
ALTER TABLE cart_order_messages ENGINE=InnoDB;
ALTER TABLE cart_order_coupons ENGINE=InnoDB;
ALTER TABLE cart_order_downloads ENGINE=InnoDB;
ALTER TABLE cart_gift_certificates ENGINE=InnoDB;
ALTER TABLE cart_gift_certificate_history ENGINE=InnoDB;

— Content and catalogue lookup tables (Priority 3)
ALTER TABLE cart_pages ENGINE=InnoDB;
ALTER TABLE cart_news ENGINE=InnoDB;
ALTER TABLE cart_product_comparisons ENGINE=InnoDB;
ALTER TABLE cart_product_configurable_fields ENGINE=InnoDB;
ALTER TABLE cart_product_customfields_report ENGINE=InnoDB;
ALTER TABLE cart_product_discounts ENGINE=InnoDB;
ALTER TABLE cart_product_related_byviews ENGINE=InnoDB;
ALTER TABLE cart_product_tags ENGINE=InnoDB;
ALTER TABLE cart_product_tagassociations ENGINE=InnoDB;
ALTER TABLE cart_product_variation_combinations ENGINE=InnoDB;
ALTER TABLE cart_redirects ENGINE=InnoDB;
ALTER TABLE cart_shipping_addresses ENGINE=InnoDB;
ALTER TABLE cart_shipping_methods ENGINE=InnoDB;
ALTER TABLE cart_shipping_zones ENGINE=InnoDB;
ALTER TABLE cart_shipping_zone_locations ENGINE=InnoDB;
ALTER TABLE cart_tax_zones ENGINE=InnoDB;
ALTER TABLE cart_vendors ENGINE=InnoDB;
ALTER TABLE cart_vendor_payments ENGINE=InnoDB;

[Author]

Posts