SQLインジェクションに対応してみる

この記事は約13分で読めます。

要は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('20: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('20: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];
    }
}

コメント

タイトルとURLをコピーしました