要はSQLインジェクションというのはハッキングということです。
意図しないデータを入手することができてしまいます。
それらを防ぐ仕組みがあります。
プリペアドステートメントという仕組みです。
目次(タップで飛べるよ！)
今回扱うSQLはこちら（対策前）
$sql = <<< SQL
SELECT
user_id,
SEC_TO_TIME(
SUM(
TIME_TO_SEC(work_time) - TIME_TO_SEC('8:00:00')
)
) AS total_overtime
FROM
tb_user_work_time
WHERE
work_time > CAST('8:00:00' AS TIME)
GROUP BY
user_id
HAVING
CASE {'$data'}
WHEN '20h以下' THEN total_overtime < CAST('20:00:00' AS TIME)
ELSE total_overtime >= CAST('２0:00:00' AS TIME)
END
SQL;
$results = DB::select($sql);残業時間の合計が20時間未満、20時間以上で表示するuser_idを取り出します。
HAVING
CASE {'$data'}
WHEN '20h以下' THEN total_overtime < CAST('20:00:00' AS TIME)
ELSE total_overtime >= CAST('２0:00:00' AS TIME)
END
SQL;
{‘$data’}には検索条件が入ってきます。20h以下ならtotal_overtime < CAST(’20:00:00′ AS TIME)が動きます。
しかし、この{‘$data’}が悪意あるものによって、別のSQLが入れられてしまうと動いてしまいます。
対策
$sql = <<< SQL
SELECT
user_id,
SEC_TO_TIME(
SUM(
TIME_TO_SEC(work_time) - TIME_TO_SEC('8:00:00')
)
) AS total_overtime
FROM
tb_user_work_time
WHERE
work_time > CAST('8:00:00' AS TIME)
GROUP BY
user_id
HAVING
CASE ?
WHEN '20h以下' THEN total_overtime <= CAST('20:00:00' AS TIME)
ELSE total_overtime >= CAST('00:00:00' AS TIME)
END
SQL;
$results = DB::select($sql, [$data]);このように書きます。変更点は以下です。
HAVING
CASE ?
WHEN '20h以下' THEN total_overtime <= CAST('20:00:00' AS TIME)
ELSE total_overtime >= CAST('00:00:00' AS TIME)
END
SQL;
$results = DB::select($sql, [$data]);
CASEのあとが？になってます。
これはわからないわけではないです。
砕いて表現すると、後から定義するから準備して待っててねという意味になります。
これをプリペアドステートメント（準備宣言）といいます。
そのままですね。
$results = DB::select($sql, [$data]);こちらの [$data]で?の中に引数が渡されます。
複数ある時は、？が出てくる順に合わせて、順に定義してあげます。
複数ある場合の参考例
<?phpnamespace App\Services;use App\Services\Service;
use Illuminate\Support\Facades\DB;class GetDepartParentProspectService extends Service
{
const MY_SECTION_PROSPECT_PARENT_INDEX = 0; // 集計部署見込み取得クエリのINDEX /**
* 親部署の売上を返却
*
* @param int $sectionParentId
* @param int $year
* @param string $period
* @return object(stdClass)
*/
public function execute($sectionParentId, $year, $period)
{
$CONST_PLAN_STATUS_BEFORE_ESTIMATE = 1; // 見積もり発行前
$CONST_PLAN_STATUS_LOW_ACCURACY = 2; // 受注前低確度
$CONST_PLAN_STATUS_HIGH_ACCURACY = 3; // 受注前高確度(内示メール添付必須)
$CONST_PLAN_STATUS_ORDER_COMPLETE = 4; // 受注確定
$CONST_PLAN_STATUS_MAINTENANCE = 5; // 保守 $yearJanuary = "{$year}-01";
$yearFebruary = "{$year}-02";
$yearMarch = "{$year}-03";
$yearApril = "{$year}-04";
$yearMay = "{$year}-05";
$yearJune = "{$year}-06";
$yearJuly = "{$year}-07";
$yearAugust = "{$year}-08";
$yearSeptember = "{$year}-09";
$yearOctober = "{$year}-10";
$yearNovember = "{$year}-11";
$yearDecember = "{$year}-12"; $sql = <<< SQL
SELECT
SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS January,
SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS February,
SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS March,
SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS April,
SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS May,
SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS June,
SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS July,
SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS August,
SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS September,
SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS October,
SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS November,
SUM(CASE WHEN DATE_FORMAT(d.end_time,'%Y-%m') = ? THEN d.plan_total_price ELSE 0 END) AS December
FROM
(
SELECT
tb_plan.plan_total_price,
tb_plan.section_id,
tb_plan.end_time
FROM
tb_plan
JOIN
tb_plan_source
ON tb_plan.plan_id = tb_plan_source.plan_id
WHERE
tb_plan.plan_status_id = ?
OR
tb_plan.plan_status_id = ?
OR
tb_plan.plan_status_id = ?
OR
tb_plan.plan_status_id = ?
OR
tb_plan.plan_status_id = ?
) AS d
WHERE
d.section_id IN
(
SELECT DISTINCT
tb_section.section_id
FROM
tb_section
JOIN
tb_section_parent
ON tb_section.section_parent_id = ?
WHERE
tb_section.deleted = 0
AND
tb_section.year = ?
AND
tb_section.period = ?
)
SQL; $result = DB::select($sql, [
$yearJanuary,
$yearFebruary,
$yearMarch,
$yearApril,
$yearMay,
$yearJune,
$yearJuly,
$yearAugust,
$yearSeptember,
$yearOctober,
$yearNovember,
$yearDecember,
$CONST_PLAN_STATUS_BEFORE_ESTIMATE,
$CONST_PLAN_STATUS_LOW_ACCURACY,
$CONST_PLAN_STATUS_HIGH_ACCURACY,
$CONST_PLAN_STATUS_ORDER_COMPLETE,
$CONST_PLAN_STATUS_MAINTENANCE,
$sectionParentId,
$year,
$period,
]); return $result[self::MY_SECTION_PROSPECT_PARENT_INDEX];
}
}
コメント