Home > Back-end >  Node.JS promise-mysql2 - Don't replace ? with a parameter
Node.JS promise-mysql2 - Don't replace ? with a parameter

Time:12-06

I am having an issue where I need to have "?" in MySQL query, and this "?" shouldn't be replaced with a parameter, how can I do that? The first ? below needs not be replaced with parameter. Thanks!

           const [rows2, fields2, err2] = await conn.query(
             "WITH Starting_Domains AS (select DISTINCT(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(Link, '/', 3), '://', -1), '/', 1), '?', 1)) AS domain from links where is_starting_link = 1 AND CrawlID = ?)," 
             "STAGE2_Visitable_Links AS (SELECT ID, Link, CrawlID, VisitTryCount, SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(Link, '/', 3), '://', -1), '/', 1), '?', 1) AS domain, (SELECT domain IN (SELECT domain FROM Starting_Domains WHERE CrawlID = ?) ) AS IS_DOMAIN_SAME FROM Links l WHERE CrawlID=? AND (VisitTryCount < 3 OR VisitTryCount IS NULL) AND (TIMESTAMPDIFF(SECOND, LastVisitTry, NOW()) > 600 OR LastVisitTry IS NULL) AND VisitSuccess IS NULL)," 
             "STAGE1_Visitable_Links AS (SELECT ID, Link, CrawlID, VisitTryCount, SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(Link, '/', 3), '://', -1), '/', 1), '?', 1) AS domain, (SELECT domain IN (SELECT domain FROM Starting_Domains WHERE CrawlID = ?) ) AS IS_DOMAIN_SAME FROM Links l WHERE CrawlID=? AND (C1_TryCount < 3 OR C1_TryCount IS NULL) AND (TIMESTAMPDIFF(SECOND, C1_LastTry, NOW()) > 600 OR C1_LastTry IS NULL) AND C1_Success IS NULL)," 
             "STAGE1_Completed_Links AS (SELECT ID, Link, CrawlID, VisitTryCount, SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(Link, '/', 3), '://', -1), '/', 1), '?', 1) AS domain, (SELECT domain IN (SELECT domain FROM Starting_Domains WHERE CrawlID = ?) ) AS IS_DOMAIN_SAME FROM Links l WHERE CrawlID=? AND C1_Success = 1 OR C1_Success IS NULL AND C1_TryCount >=3 AND CrawlID=?)," 
             "STAGE2_Completed_Links AS (SELECT ID, Link, CrawlID, VisitTryCount, SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(Link, '/', 3), '://', -1), '/', 1), '?', 1) AS domain, (SELECT domain IN (SELECT domain FROM Starting_Domains WHERE CrawlID = ?) ) AS IS_DOMAIN_SAME FROM Links l WHERE CrawlID=? AND VisitSuccess = 1 OR VisitSuccess IS NULL AND VisitTryCount >=3 AND CrawlID=?)" 
             "SELECT"  
             "(SELECT COUNT(*) FROM STAGE2_Visitable_Links WHERE IS_DOMAIN_SAME = 1) AS STAGE2_LINKS_LEFT_IMPORTANT_DOMAIN, (SELECT COUNT(*) FROM STAGE2_Visitable_Links WHERE IS_DOMAIN_SAME = 0) AS STAGE2_LINKS_LEFT_UNIMPORTANT_DOMAIN," 
             "(SELECT COUNT(*) FROM STAGE1_Visitable_Links WHERE IS_DOMAIN_SAME = 1) AS STAGE1_LINKS_LEFT_IMPORTANT_DOMAIN, (SELECT COUNT(*) FROM STAGE1_Visitable_Links WHERE IS_DOMAIN_SAME = 0) AS STAGE1_LINKS_LEFT_UNIMPORTANT_DOMAIN," 
             "(SELECT COUNT(*) FROM STAGE1_Completed_Links WHERE IS_DOMAIN_SAME = 1) AS STAGE1_LINKS_COMPLETED_IMPORTANT_DOMAIN, (SELECT COUNT(*) FROM STAGE1_Completed_Links WHERE IS_DOMAIN_SAME = 0) AS STAGE1_LINKS_COMPLETED_UNIMPORTANT_DOMAIN," 
             "(SELECT COUNT(*) FROM STAGE2_Completed_Links WHERE IS_DOMAIN_SAME = 1) AS STAGE2_LINKS_COMPLETED_IMPORTANT_DOMAIN, (SELECT COUNT(*) FROM STAGE2_Completed_Links WHERE IS_DOMAIN_SAME = 0) AS STAGE2_LINKS_COMPLETED_UNIMPORTANT_DOMAIN"
             , [CrawlID, CrawlID, CrawlID, CrawlID, CrawlID, CrawlID, CrawlID, CrawlID, CrawlID, CrawlID, CrawlID]);

CodePudding user response:

You can escape ? by prepending it with a backslash. Because this is a node string, that backslash also needs to be escaped.

So use \\?

  • Related