第一次來這里，所以我會盡量做到徹底。我是一個相當(dāng)新的程序員，并開始在一家新公司工作，我一直試圖防止在舊項目遺留的特定 PHP 代碼中進(jìn)行一些 SQL 注入，但在我重寫它時需要。這個項目在 Laravel 作為后端，AngularJS 作為前端。我嘗試了幾種方法來防止 SQL 注入，但都得到了相同的錯誤。錯誤：SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'll.created_at BETWEEN '?' AND '?' UNION SELECT 1 as type, ll.id, ll.name, ll.ema' at line 1這是有問題的原始代碼： if (!empty($request['inst'])) { $institution = $request['inst']; $inst = ' ll.inst_id = ' . $request['inst'] . ' AND '; $instCount = ' customers.inst_id = :inst AND '; $instEvent = ' location_id = :inst AND '; } else { $inst = ' '; $instCount = ' '; $instEvent = ' '; $institution = ""; } $startTime = $request['start']; $endTime = $request['end'];以下是我在仔細(xì)閱讀互聯(lián)網(wǎng)和 SO 后使用的一些技術(shù)。占位符（？而不是所有變量，并在最后創(chuàng)建一個包含所有變量 9 次的數(shù)組）命名綁定示例：DB::select($sql, ["inst" => $institution, "start" => $startTime, "end" => $endTime,])命名綁定 命名綁定，PDO::ATTR_EMULATE_PREPARES => true,因此我可以多次使用 :inst、:start、:end使用 @ 符號的 SQL 變量綁定技術(shù)結(jié)果：沒有保護(hù) - 工作正常，我收到正確的結(jié)果占位符 - 我收到上述錯誤，但 SQL 在占位符中有正確的數(shù)據(jù)。我可以獲取生成的 SQL 并通過 phpmyadmin 服務(wù)器 SQL 查詢運(yùn)行它，并按預(yù)期接收數(shù)據(jù)。命名綁定 - 根本不起作用。我收到一條錯誤消息SQLSTATE[HY093]: Invalid parameter number: parameter was not defined使用 - 命名的綁定可以PDO::ATTR_EMULATE_PREPARES => true,工作，但是它會影響我從服務(wù)器中提取的所有數(shù)據(jù)，將諸如數(shù)字之類的東西轉(zhuǎn)換為字符串或更糟。這會對到處打字造成嚴(yán)重破壞SQL 變量綁定 - 也不起作用，收到上述錯誤我不確定是否可以將這個巨大的 SQL 查詢轉(zhuǎn)換為 Laravel Eloquent 或 Query Builder，盡管我已經(jīng)嘗試過了。有人有想法么？任何幫助將不勝感激，我已經(jīng)為此苦苦掙扎了幾個星期。
1 回答
冉冉說
TA貢獻(xiàn)1877條經(jīng)驗 獲得超1個贊
$institution = $request['inst']??'';
$start = $request['start'];
$end = $request['end'];
$joins = [
1 => ['date_field' => 'meeting_set_first', 'is_active' => false],
2 => ['date_field' => 'occurred_date', 'is_active' => true],
3 => ['date_field' => 'not_show_date', 'is_active' => true],
4 => ['date_field' => 'cancel_meeting_date', 'is_active' => true],
5 => ['date_field' => 'sale_date', 'is_active' => true],
6 => ['date_field' => 'cancel_sale_date', 'is_active' => true],
7 => ['date_field' => 'extended_date', 'is_active' => false],
8 => ['date_field' => 'meeting_set_date', 'is_active' => true],
];
$sql = 'SELECT 0 as type ,ll.id as lead_name, ll.name, ll.email, ll.updated_at, u.employee_name, ls.meeting_set_date, ls.meeting_set_first FROM customers ll LEFT JOIN leads_statistics ls ON ll.id = ls.lead_id LEFT JOIN users u ON ll.userUpdate = u.employee_id WHERE '.($institution?" ll.inst_id = :inst0 AND ":'').' ll.created_at BETWEEN :start0 AND :end0 UNION ';
if ($institution) {
$placeHolders[':inst0'] = $institution;
}
$placeHolders[':start0'] = $start;
$placeHolders[':end0'] = $end;
$queryJoin = [];
foreach($joins as $key => $join) {
$queryJoin[] = 'SELECT '.$key.' as type, ll.id, ll.name, ll.email, ll.updated_at, u.employee_name, ls.meeting_set_date, ls.meeting_set_first FROM leads_statistics ls LEFT JOIN customers ll ON ls.lead_id = ll.id LEFT JOIN users u ON ll.userUpdate = u.employee_id WHERE '.($institution?" ll.inst_id = :inst$key AND ":'').($join['is_active']?' ls.is_active = 1 AND ':'').' ls.'.$join['date_field'] ." BETWEEN :start$key AND :end$key";
if ($institution) {
$placeHolders[':inst'.$key] = $institution;
}
$placeHolders[':start'.$key] = $start;
$placeHolders[':end'.$key] = $end;
}
$sql .= implode(' UNION ', $queryJoin);
$leads = DB::select($sql, $placeHolders);
- 1 回答
- 0 關(guān)注
- 142 瀏覽
添加回答
舉報
0/150
提交
取消