无法在SQL语句中获取空格

Question

我的WHERE语句中有两个变量。我似乎不能用空格来分隔它们，所以我最终得到了一个语法错误。谢谢你的帮助。

(我使用的是codeigniter)

顺便说一下，我已经尝试设置了一个$space变量，并在设置了两个变量之后，在sql中将空格放在和之前。

错误

错误号: 1064

您的SQL语法中有一个错误；请查看与您的MySQL服务器版本对应的手册，了解在第2行的“source_adusers.ad_account=”Wolfs，Marc“GROUP BY rollout_systems.eam_user LIMIT”附近使用的正确语法。

SELECT *，COUNT( rollout_systems.eam_user ) as system FROM rollout_systems LEFT JOIN source_adusers ON rollout_systems.EAM_User = source_adusers.ad_account WHERE rollout_systems.scope_ID =3 3AND source_adusers.ad_account="Wolfs，Marc“GROUP BY rollout_systems.eam_user LIMIT 0,50

行号: 330

PHP

 if ($this->session->userdata('scopeId') != NULL) {
        $where1 = 'WHERE rollout_systems.scope_ID = '. $this->session->userdata('scopeId') . '';
    } else {
        redirect('/headquarters/home');;
    }

    if ($search) {
        $where2 = ' AND rollout_systems.sys_name ="'.$search.'"';
    } else {
        $where2 = '';
    }
 $query = $this->db->query('SELECT * FROM rollout_systems  LEFT JOIN   source_adusers
        ON rollout_systems.eam_user = source_adusers.ad_account '. $where1 .''. $where2 .' GROUP BY rollout_systems.sys_name LIMIT '.$limit.',50');

Answer 1

如果您将空格和和保留在$query中，而不是将它们构建到where变量中，会发生什么呢？然后，您的$where 2只需要在不影响查询的情况下工作--即0=0。

 if ($this->session->userdata('scopeId') != NULL) {
        $where1 = 'WHERE rollout_systems.scope_ID = '. $this->session->userdata('scopeId') . '';
    } else {
        redirect('/headquarters/home');;
    }

    if ($search) {
        $where2 = 'rollout_systems.sys_name ="'.$search.'"';
    } else {
        $where2 = '0=0';
    }
 $query = $this->db->query('SELECT * FROM rollout_systems  LEFT JOIN   source_adusers
        ON rollout_systems.eam_user = source_adusers.ad_account '. $where1 .' and '. $where2 .' GROUP BY rollout_systems.sys_name LIMIT '.$limit.',50');

Answer 2

只需在两个var之间添加一个空格- '. $where1 .' '. $where2 .'

正如其他人所指出的，如果您期望的是整数值，那么您确实应该使用mysql_real_escape_string()或intval()来转义您的用户输入。如果您使用的是PDO或mysqli，请使用预准备语句。

如果$this->db是一个PDO实例，你可以使用-

$params = array();

if ($this->session->userdata('scopeId') != NULL) {
    $where = 'WHERE rollout_systems.scope_ID = ?';
    $params[] = $this->session->userdata('scopeId');
} else {
    redirect('/headquarters/home');;
}

if ($search) {
    $where .= ' AND rollout_systems.sys_name = ?';
    $params[] = $search;
}

$sql = "SELECT * FROM rollout_systems
    LEFT JOIN source_adusers ON rollout_systems.eam_user = source_adusers.ad_account
    $where
    GROUP BY rollout_systems.sys_name
    LIMIT ?, 50";
$params[] = $limit;

$query = $this->db->prepare($sql);
$query->execute($params);

Answer 3

$where = array();

if ($this->session->userdata('scopeId') != NULL) {
    // better to escape your parameter here unless you trust it totally
    // judging by its name, it's user-provided data, so I wouldn't trust it
    $where[] = 'rollout_systems.scope_ID = '. $this->session->userdata('scopeId');
} else {
    redirect('/headquarters/home');
    // you may need to exit here after redirection, depends on your implementation
}

if ($search) {
    // once again don't forget to escape $search in real application
    $where[] = "rollout_systems.sys_name = '" . $search . "'";
}

$query = $this->db->query("
    SELECT
      *
    FROM
      `rollout_systems`
      LEFT JOIN
      `source_adusers`
      ON rollout_systems.eam_user = source_adusers.ad_account
    WHERE
      " . implode (' AND ', $where) . "
    GROUP BY
      rollout_systems.sys_name
    LIMIT " . $limit /* escape it! */ . ",50"
);