mysql - 如何将变量放入 mysql 查询中？

Question

我有两个这样的变量

$date1 = $_POST['f_date1'];
$date2 = $_POST['f_date2'];

这是将其放入其中的正确方法吗？

$sql = "SELECT location, COUNT(*) as Referrals,
        SUM(CASE WHEN leadstatus = 'Hired' THEN 1 ELSE 0 END) as Hired,
        SUM(CASE WHEN leadstatus = 'Failed' THEN 1 ELSE 0 END) as Failed
        FROM vtiger_leadscf
        LEFT JOIN vtiger_leaddetails ON vtiger_leadscf.leadid = vtiger_leaddetails.leadid
        WHERE location > '' AND (date_table BETWEEN '$date1' AND '$date2')
        GROUP BY location 
        ORDER BY Referrals DESC";

Answer 1

执行此操作的方式取决于您使用的 mysql 接口(interface)。

如果您正在使用(旧的且已弃用的)mysql_* 接口(interface)(您不应该使用)，那么至少在使用您需要的变量之前使用mysql_real_escape_string()转义它们。

例如:

$date1 = mysql_real_escape_string($_POST['f_date1']);
$date2 = mysql_real_escape_string($_POST['f_date2']);

之后，是的，您的查询构造就很好了(对于此方法，您不应该使用它)。

理想情况下，您需要使用 PDO或mysqli ，两者都支持准备好的语句。这个示例将是 PDO，只是因为。

$pdo = new PDO('mysql:host=localhost;dbname=whatever', 'username', 'password');

$stmt = $pdo->prepare("SELECT location, COUNT(*) as Referrals,
        SUM(CASE WHEN leadstatus = 'Hired' THEN 1 ELSE 0 END) as Hired,
        SUM(CASE WHEN leadstatus = 'Failed' THEN 1 ELSE 0 END) as Failed
        FROM vtiger_leadscf
        LEFT JOIN vtiger_leaddetails ON vtiger_leadscf.leadid = vtiger_leaddetails.leadid
        WHERE location > '' AND (date_table BETWEEN :startDate AND :endDate)
        GROUP BY location 
        ORDER BY Referrals DESC");

$stmt->execute(array(
  'startDate' => $date1,
  'endDate' => $date2
));

注意查询中使用 :startDate 和 :endDate。这些是占位符，由传递给 $stmt->execute 的关联数组填充。首选准备好的语句，因为它们可以防止当您简单地将未经过滤的值连接到查询中时可能发生的麻烦(查找:sql 注入(inject))。

mysqli_ 接口(interface)与已弃用的 mysql_ 接口(interface)更相似，但它也支持准备好的语句。

mysqli方法:

$mysqli = new mysqli('localhost', 'username', 'password', 'db');

$stmt = $mysqli->prepare("SELECT location, COUNT(*) as Referrals,
            SUM(CASE WHEN leadstatus = 'Hired' THEN 1 ELSE 0 END) as Hired,
            SUM(CASE WHEN leadstatus = 'Failed' THEN 1 ELSE 0 END) as Failed
            FROM vtiger_leadscf
            LEFT JOIN vtiger_leaddetails ON vtiger_leadscf.leadid = vtiger_leaddetails.leadid
            WHERE location > '' AND (date_table BETWEEN ? AND ?)
            GROUP BY location 
            ORDER BY Referrals DESC");

$stmt->bind_param("ss", $date1, $date2);

$stmt->execute();

请注意，关键区别在于使用 ? 作为占位符(pdo 也支持此功能，我只是更喜欢命名占位符)以及变量的绑定(bind)方式。 “ss” 指定所绑定(bind)值的“类型”。

我个人更喜欢 PDO，纯粹是因为我更喜欢它使用数组参数进行 execute 调用。