php - MySQLi 更新带引号等

Question

我正在尝试使用 PHP 更新 Joomla 模块，但是参数字段充满了引号和单引号。

这是原始的 MySQL 查询。我怎样才能在 PHP 中完成这项工作，并在查询中包含所有引号和单引号？

UPDATE `xxyyzz_modules` 
SET `params` = '{"moduleclass_sfx":"","loadJQuery":"1","tableType":"datatable","theme":"ui-lightness","source":"sql","title":"","className":"arttable_table","tablecode":"","sqlQuery":"SELECT * FROM `xxyyzz_dtregister_paymentdetailsuser` WHERE datetime between ''2014\\/02\\/16'' and ''2014\\/03\\/02''","csscode":"","connectionString":"","csvFile":"","csvDelimiter":",","convertLinks":"2","linkConversionPattern":"TITLE|URL","linksNofollow":"0","linksNewWindow":"0","showFirstLink":"0","searchText":"Search:","searchSize":"15","headerStyle":"","cellStyle":"","excel":"","chartType":"LineChart","chartLeftHeader":"","xAxis":"","yAxis":"","chartWidth":"1100","chartHeight":"300","hideTable":"1","module_tag":"div","bootstrap_size":"0","header_tag":"h3","header_class":"","style":"0"}' 
WHERE `id` =140;

Answer 1

您需要转义用于分隔 PHP 字符串的任何类型的引号。

$sql = 'UPDATE `xxyyzz_modules`
        SET `params` = \'{"moduleclass_sfx":"","loadJQuery":"1","tableType":"datatable","theme":"ui-lightness","source":"sql","title":"","className":"arttable_table","tablecode":"","sqlQuery":"SELECT * FROM `xxyyzz_dtregister_paymentdetailsuser` WHERE datetime between \'\'2014\\/02\\/16\'\' and \'\'2014\\/03\\/02\'\'","csscode":"","connectionString":"","csvFile":"","csvDelimiter":",","convertLinks":"2","linkConversionPattern":"TITLE|URL","linksNofollow":"0","linksNewWindow":"0","showFirstLink":"0","searchText":"Search:","searchSize":"15","headerStyle":"","cellStyle":"","excel":"","chartType":"LineChart","chartLeftHeader":"","xAxis":"","yAxis":"","chartWidth":"1100","chartHeight":"300","hideTable":"1","module_tag":"div","bootstrap_size":"0","header_tag":"h3","header_class":"","style":"0"}\'
        WHERE `id` =140;';

或

$sql = "UPDATE `xxyyzz_modules`
        SET `params` = '{\"moduleclass_sfx\":\"\",\"loadJQuery\":\"1\",\"tableType\":\"datatable\",\"theme\":\"ui-lightness\",\"source\":\"sql\",\"title\":\"\",\"className\":\"arttable_table\",\"tablecode\":\"\",\"sqlQuery\":\"SELECT * FROM `xxyyzz_dtregister_paymentdetailsuser` WHERE datetime between ''2014\\\\/02\\\\/16'' and ''2014\\\\/03\\\\/02''\",\"csscode\":\"\",\"connectionString\":\"\",\"csvFile\":\"\",\"csvDelimiter\":\",\",\"convertLinks\":\"2\",\"linkConversionPattern\":\"TITLE|URL\",\"linksNofollow\":\"0\",\"linksNewWindow\":\"0\",\"showFirstLink\":\"0\",\"searchText\":\"Search:\",\"searchSize\":\"15\",\"headerStyle\":\"\",\"cellStyle\":\"\",\"excel\":\"\",\"chartType\":\"LineChart\",\"chartLeftHeader\":\"\",\"xAxis\":\"\",\"yAxis\":\"\",\"chartWidth\":\"1100\",\"chartHeight\":\"300\",\"hideTable\":\"1\",\"module_tag\":\"div\",\"bootstrap_size\":\"0\",\"header_tag\":\"h3\",\"header_class\":\"\",\"style\":\"0\"}'
        WHERE `id` =140";

或者使用此处文档:

$sql = <<'EOS'
       UPDATE `xxyyzz_modules`
       SET `params` = '{"moduleclass_sfx":"","loadJQuery":"1","tableType":"datatable","theme":"ui-lightness","source":"sql","title":"","className":"arttable_table","tablecode":"","sqlQuery":"SELECT * FROM `xxyyzz_dtregister_paymentdetailsuser` WHERE datetime between ''2014\\/02\\/16'' and ''2014\\/03\\/02''","csscode":"","connectionString":"","csvFile":"","csvDelimiter":",","convertLinks":"2","linkConversionPattern":"TITLE|URL","linksNofollow":"0","linksNewWindow":"0","showFirstLink":"0","searchText":"Search:","searchSize":"15","headerStyle":"","cellStyle":"","excel":"","chartType":"LineChart","chartLeftHeader":"","xAxis":"","yAxis":"","chartWidth":"1100","chartHeight":"300","hideTable":"1","module_tag":"div","bootstrap_size":"0","header_tag":"h3","header_class":"","style":"0"}'
       WHERE `id` =140;
EOS;