SQLインジェクション攻撃の防ぎ方がわかりません。

Question

#前提知識
SQLについて勉強していく中でSQLインジェクション攻撃というものを知りました。
複数の解説サイト等を読んでいくと自分がこれまで書いていたコードが「危険性のあるコード」として紹介されているものと近いのではないかと不安になりました。
自分の知識不足は反省するとして、これから書いていくコードをSQLインジェクション攻撃の対策が取れたコードにしなくてはいけないと思いました。
ただ、本当に自分が知識不足過ぎるだけなのですがいくつかの解説サイトを読んでもどう直せばSQLインジェクション攻撃の対策が取れたセキュア？なコードになるのかがいまいち理解できませんでした。

---

#聞きたいこと
0. 今の自分の理解に間違っている部分があれば指摘してほしいです。

1. （今の自分の理解があっているとするなら）こうすれば対策が取れたコードになるんじゃないか、というコードは書いてみたのですが、これではたして対策できているのか、なぜこれで対策になるのかがわかりません。対策できていますか。また、対策できていないとすればどこを直せばいいですか。

2. これまでは'SQL文を実行するコードに続くの部分でSQLを実行する実態部分をCallしていました。（引数がSQL文で、返り値が実行結果）直した方法のうち、例2の方法だとSQL文を作るためには接続を先に作成しなくてはいけません。つまり、セキュア？なSQL文を作成するためにもSQL文を作成する部分とSQL実行部分を分けてはいけないということでしょうか。

#利用環境
VB.NET
SQL Server

---

#勉強したことまとめ
##SQLインジェクション攻撃とは
例えばログインIDとパスワードを入力してログインするフォームがあるとする。
ユーザはログインIDとパスワードをテキストボックスに入力してログインする。
このとき、テキストボックスにSQL文を直接入力されてしまうと、こちらが意図していないSQL文が実行されてしまう可能性がある。
SQL文がこうなっているとして、

SELECT
    COUNT(UserID) AS cnt
FROM m_User
WHERE UserID = 'ユーザIDテキストボックスの値' AND pass= 'パスワードテキストボックスの値'

パスワードのテキストボックスに

a' OR '1' = '1

と入力されてしまうと、SQL文の結果が

SELECT
    COUNT(UserID) AS cnt
FROM m_User
WHERE UserID = 'ユーザIDテキストボックスの値' AND pass= 'a' OR '1' = '1'

となってしまい、だれでも入れるログインフォームになってしまう。
OR句が入っているので、ORの右側の条件'1' = '1'に該当するとOKになってしまう。
（つまり100%該当してしまう）
##防ぐためにはどうすればいいか
いくつか紹介されていた（例えばテキストボックスの値を利用する場合エスケープを毎回挟むなど）が、これについて調べたいと思った。
###SQL 文の組み立ては全てプレースホルダで実装する。

プレースホルダによる組み立てとは、パラメータ部分を?などの記号で示しておき、後に、そこへ実際の値を機械的な処理で割り当てる方法である。

##SQLインジェクション攻撃を知るまでに書いていたコード
ログインフォームを例とする。
###例1

Private Sub bt_login_Click(sender As Object, e As EventArgs) Handles bt_login.Click
    Dim sqlstr As String = "SELECT COUNT(UserID) AS cnt FROM m_User WHERE UserID = '" & tx_userid.Text & "' AND pass= '" & tx_pass.Text & "'"
    'SQL文を実行するコードに続く
    Dim dt As DataTable = SqlExecution(sqlstr)
End Sub

###例2

Private Sub bt_login_Click(sender As Object, e As EventArgs) Handles bt_login.Click
    Dim userid As String = tx_userid.Text
    Dim pass As String = tx_pass.Text
    Dim sqlstr As String = "SELECT COUNT(UserID) AS cnt FROM m_User WHERE UserID = '" & userid & "' AND pass= '" & pass & "'"
    'SQL文を実行するコードに続く
    Dim dt As DataTable = SqlExecution(sqlstr)
End Sub

##知ったうえで対策を考えて書いたコード
###例1
説明を読んでこういうことか？と思いながら作ったもの。
今までと何が変わったのかよくわからない

    Dim userid As String = tx_userid.Text
    Dim pass As String = tx_pass.Text
    Dim sqlstr As String = "SELECT COUNT(UserID) AS cnt FROM m_User WHERE UserID = '@ID' AND pass= '@PASSWORD'"
    sqlstr = sqlstr.Replace("@ID", userid)
    sqlstr = sqlstr.Replace("@PASSWORD", pass)
    'SQL文を実行するコードに続く
    Dim dt As DataTable = SqlExecution(sqlstr)

###例2
VB.netで解説されているサイトを探して書いたもの
何をやっているのかはよくわかってないし、SQL文を作成する部分とSQL文を実行する部分を分けることができないのかな、と思ってしまう。

Private Sub bt_login_Click(sender As Object, e As EventArgs) Handles bt_login.Click
    Dim userid As String = tx_userid.Text
    Dim pass As String = tx_pass.Text

    Dim connectionString As String = "" '接続文字列は今回伏せる
    Dim con As SqlConnection = New SqlConnection(connectionString)

    Dim sqlstr As String = "SELECT COUNT(UserID) AS cnt FROM m_User WHERE UserID = @ID AND pass= @PASSWORD"

    Dim command As SqlCommand = New SqlCommand(sqlstr, con)
    Dim pID As SqlParameter = New SqlParameter("@ID", userid)
    command.Parameters.Add(pID)
    Dim pPassword As SqlParameter = New SqlParameter("@PASSWORD", pass)
    command.Parameters.Add(pPassword)
    'SQL文を実行するコードに続く
End Sub

---

#まとめ
0. SQLインジェクション攻撃について知った。
0. 自分のコードは対策ができていないのではと思った。
0. 対策方法を考えたが、対策できているのかよくわからなくなった。

知識不足ゆえに質問も的を射ていないかもしれませんが、よろしくお願いします。

---

#試した上での追記
例1、例2、修正例1、修正例2全て試しました
結果として
例1、例2、修正例1はダメ。修正例2はOKでした。
修正例1は例1、例2と同じ結果になりました。
（ユーザテーブルの全件のカウントが返ってくる）
実行されたSQLを出力すると以下のようでした。

SELECT COUNT(UserID) AS cnt FROM m_User WHERE UserID = '0001' AND pass= 'a' OR '1'='1'

それに対して修正例2は0件（該当なし）が返ってきました
実行されたSQLを出力すると以下のようでした。

SELECT COUNT(UserID) AS cnt FROM m_User WHERE UserID = @ID AND pass= @PASSWORD

Comments

[hentaiman]

> 対策方法を考えたが、対策できているのかよくわからなくなった。
そういう時は、自分でSQLインジェクションを模した入力を与えて実行してみましょう。

> （つまり100%該当してしまう）
と理解しているようなので、試してみる事は出来るはず

また、いずれの場合も組み立てられたSQLをログに出力して確認する事でSQLインジェクション対策の効果のほどを実感できるでしょう

[退会済みユーザー]

対象は SQL Server だけで、ADO.NET + SqlClient で SqlConnection, SqlCommand などを利用しているという条件でいいですか？

[hinano]

お返事遅くなってすみません。
hentaimanさん
>そういう時は、自分でSQLインジェクションを模した入力を与えて実行してみましょう。
その通りですね。試した結果を追記しました。

[hinano]

お返事遅くなってすみません。
SurferOnWwwさん
>対象は SQL Server だけで、ADO.NET + SqlClient で SqlConnection, SqlCommand などを利用しているという条件でいいですか？
今後他の方法も勉強するかと思いますが、今回は理解のためが主の目的なので、1種類で大丈夫です。

[hentaiman]

結果だけではなく、SQLを確認（サーバー側のログでも見れると思うが）してみましょう。
修正例1はただの文字列の置換ですからプレースホルダーとは別物ですね。それを踏まえて発行されたSQLをログで確認してみてください。

[退会済みユーザー]

質問者さん、無言になってしまいましたが、すでに多々回答が付いていますし、回答のコメントに対しても返事していますので、回答に対するフィードバックを返すようにしてください。とにかく無言で放置は NG です。マナー的にも。

[hinano]

お返事遅くなってすみません。
hentaimanさん
>結果だけではなく、SQLを確認（サーバー側のログでも見れると思うが）してみましょう。
修正例1はただの文字列の置換ですからプレースホルダーとは別物ですね。それを踏まえて発行されたSQLをログで確認してみてください。
SQLの結果も踏まえて追記しました。

[hinano]

お返事遅くなってすみません。
SurferOnWwwさん
>質問者さん、無言になってしまいましたが、すでに多々回答が付いていますし、回答のコメントに対しても返事していますので、回答に対するフィードバックを返すようにしてください。とにかく無言で放置は NG です。マナー的にも。
そうですね。ごめんなさい。
自分でも調べていくうちに回答が遅くなってしまいました。

[hentaiman]

>SQLの結果も踏まえて追記しました。
追記しろって言ってるわけじゃなくて自分で確認したら100%違いが分かると思ってのアドバイスです。

[hoshi-takanori]

SQLインジェクション対策も重要ですが、パスワードを平文ではなくハッシュ化して保存することもお忘れなく。
https://www.atmarkit.co.jp/ait/articles/1902/08/news022.html

[退会済みユーザー]

SQLに限らずセキュリティの基本はコードとデータの分離です。今いじっているのはコードなのか、それともデータなのかを常に意識するようにしましょう。データは信頼できないと思いましょう。文字列で結合したSQLはコードとデータを混ぜています。プレースホルダを使うことでコードとデータの分離を実現しています。

Answer 1

まず
Type-Safe SQL パラメーターの使用

この例では、 @au_id パラメーターは実行可能コードとしてではなくリテラル値として扱われます。 この値は型および長さについてチェックされます。 @au_id の値が指定された型および長さの制約に従っていない場合は、例外がスローされます。

とあるとおり、Parameters コレクションを利用することでパラメーターで指定した値を「SQLコマンドとしても解釈可能な文字列」ではなく「リテラル値」として扱う事ができます。

で、質問文の

VB.netで解説されているサイトを探して書いたもの

というのは動的な SQL を使用した Parameters コレクションの使用に書かれている利用法です。

簡単に言えば、SQLという言語を単なる文字列としてプログラム側で組み立ててRDMBS側に送ると何でもできてしまう、というのがSQLインジェクションなので、そうならないように「SQLコマンド」と「パラメータ」をプログラム上でちゃんと分離して管理し、「パラメータ」として不適切な値が入らないようにする、という手法をとっているわけです。

したがって

つまり、セキュア？なSQL文を作成するためにもSQL文を作成する部分とSQL実行部分を分けてはいけないということでしょうか。

Parametersコレクションを利用するべきなのか、という意味で質問されているのであれば、YESです。

Answer 2

質問のコメントに対する返事がないですが、コメントに書いた通り「対象は SQL Server だけで、ADO.NET + SqlClient で SqlConnection, SqlCommand などを利用しているという条件」と理解してレスします。

1. 今の自分の理解に間違っている部分があれば指摘してほしいです。
2. （今の自分の理解があっているとするなら）こうすれば対策が取れたコードになるんじゃないか、というコードは書いてみたのですが、これではたして対策できているのか、

対策以前に、質問に書かれた「知ったうえで対策を考えて書いたコード」の「例 1」は動かないと思いますが、それはちょっと置いといて（＝「例 1」は議論の対象外とし、「例 2」だけ考えてという意味）、SQL 文のパラメータ化の観点だけから対策前後のコードを見ると、

SQLインジェクション攻撃を知るまでに書いていたコード

⇒ ユーザー入力を使って SQL の文字列を組み立てて SQL Server に発行。

知ったうえで対策を考えて書いたコード

⇒ SQL 文をパラメータ化し、ADO.NET + SqlClient の SqlCommand 使ってパラメータ値を代入し、SQL Server に発行。

・・・となっていて、パラメータ化による SQL インジェクション防止対策は考えられていると思います。

ただ、「高いスキルを持つ決然たる攻撃者は、パラメータ化されたデータであっても操作できる」そうで（下に紹介した Microsoft のドキュメントに書いてあったこと）、以下の記事の「すべての入力の検証」セクションに書いてあるように "型、長さ、形式、および範囲をテストすることによってユーザー入力を必ず検証してください" も対処する必要があると思います。

SQL インジェクション
https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2008/ms161953(v=sql.100)

なぜこれで対策になるのかがわかりません。

パラメータの入力はリテラルとして扱われるから SQL インジェクション攻撃を防ぐことができると言われています。そこがポイントですが、そのリテラルというのが何か、どういう仕組みで防止できるかはちょっと話が長くなるので、自分のブログの記事で恐縮ですが、以下の記事の説明を見てください。

パラメータ化クエリ
http://surferonwww.info/BlogEngine/post/2012/02/02/Parameterized-query.aspx

上の記事からリンクを張った「安全なSQLの呼び出し方 - IPA 独立行政法人 情報処理推進機構」も一読することをお勧めします。（特に、プレースホルダには静的と動的があって、それによって対策が少々異なることに注目。SQL Server は「静的」です）

安全なSQLの呼び出し方 - IPA 独立行政法人 情報処理推進機構
https://www.ipa.go.jp/security/vuln/documents/website_security_sql.pdf

1. 例2の方法だとSQL文を作るためには接続を先に作成しなくてはいけません。つまり、セキュア？なSQL文を作成するためにもSQL文を作成する部分とSQL実行部分を分けてはいけないということでしょうか。

上に書いたように、質問に書かれた「知ったうえで対策を考えて書いたコード」の「例 1」は動かないと思いますけど、それはちょっと置いといて、上の 3 の文章の「分けてはいけない」かどうかだけに限れば、パラメータ化クエリの利用による SQL インジェクション防止という点では、何の違いもないと思います。

Comments

[hinano]

SQL Serverは、渡されたSQL文の要素をどう解釈するか読んでいて、
危険性があるコードのようになんの加工もせずに渡してしまうと、書き方しだいで解釈を変えられてしまう。
対策したコードだと、SQL Serverに「ここはリテラルだよ」と教えることができて
ユーザに意図的に解釈を変えられなくなる
という認識であっていますでしょうか？

[退会済みユーザー]

> SQL Serverは、渡されたSQL文の要素をどう解釈するか読んでいて、危険性があるコードのようになんの加工もせずに渡してしまうと、書き方しだいで解釈を変えられてしまう。

「要素をどう解釈するか読んで」ということはありません。「解釈を変えられてしまう」ということもありません。SQL Server は渡された SQL 文をそのまま実行するだけです。

紹介した記事「SQL インジェクション」を読んでないのでは？　最初の方だけでいいので読んでください。

質問者さんの書いたコードでも ID とかパスワードはテキストボックスなどからユーザーが入力するのだと思いますが、悪意を持ったユーザーが、上の記事に書いてあるようにテキストボックスに、

Redmond'; drop table OrdersTable--

・・・のように入力したとすると、「SQLインジェクション攻撃を知るまでに書いていたコード」ではユーザー入力を使って SQL 文を組み立てて SQL Server に発行するので、SQL Server は言われた通り OrdersTable テーブルをドロップしてしまうということになります。それが SQL インジェクションによる問題です。

> 対策したコードだと、SQL Serverに「ここはリテラルだよ」と教えることができてユーザに意図的に解釈を変えられなくなるという認識であっていますでしょうか？

紹介した記事「SQL インジェクション」の例で言うと、

WHERE ShipCity = @ShipCity

というようにパラメータ化して ADO.NET の SqlCommand からパラメータ値を渡せば、ユーザーがテキストボックスに入力した

Redmond'; drop table OrdersTable--

という文字列は実行可能なコードとしてではなくリテラル値として扱われ、注入した値はリテラルの外にはみ出すことはないので（WHERE 句の条件が true または false になるだけ）、SQL インジェクション攻撃を防ぐことができるということです。

[退会済みユーザー]

質問欄に「試した上での追記」を追記されたようですので、以下の質問に対してはそれを踏まえて回答した方がよかったですね。

> 対策したコードだと、SQL Serverに「ここはリテラルだよ」と教えることができてユーザに意図的に解釈を変えられなくなるという認識であっていますでしょうか？

表現の仕方の問題だけかもしれませんが、ちょっと違うように感じます。

質問欄の「修正例2」は「SQL 文をパラメータ化し、ADO.NET + SqlClient の SqlCommand 使ってパラメータ値を代入」という SQL インジェクション対策が正しく取られています。

なので、質問欄の「試した上での追記」に書かれたように「修正例2はOKでした」という結果になったのです。

パスワードのテキストボックスへのユーザー入力を

a' OR '1' = '1

として試したようですが、その文字列は実行可能なコードとしてではなくリテラル値として扱われ、そのようなパスワードはないので、WHERE 句の条件が false になり、結果ログイン失敗に終わったということです。

Answer 3

SQL文自体を編集されてしまう事を防ぐのがSQLインジェクション対策です。
端的に言うと、パラメータを値として定義する事です。

プレースホルダによってパースすることは、パラメータが数値の定義ならエラーになり、文字列なら、文字列として括られることによって対策になります。

質問の例は、共にパラメータ化しているので問題無いと思います。

Answer 4

SQLのことは詳しくなくて恐縮ですが、考え方として以下と思っております。
・文字を文字として扱う（コードとしてパラメタを読み込まない）＝サニタイジング
・可能であれば、値をチェックして用途から外れる文字列やサイズをそもそも扱わない
・エラー発生時に、DBのエラー情報を返さない＝エラー内容からの推測を防止する
・アクセス元が制限できるなら、制限（または定期的なログ確認）
・診断を自身のサーバにやってみる（オープンソースのものもあったはず）

Answer 5

余談です。最強の SQL インジェクション対策を語ります！

SQL インジェクションとは、ざっくりと言ってしまうと、「外部入力により、SQL に構造上の変化を起こさせ、管理者の意図以外の動作をさせる」ことです。

そのため、最も強固な対策は、外部入力を一切入れない、固定 SQL(造語ｗ)を使用することです。

例えば、

if ($table === 'test01') {
 $SQL = 'SELECT * FROM test01';
} else if ($table === 'test02') {
 $SQL = 'SELECT * FROM test02';
} else {
 $SQL = "";
}

のような書き方だと、外部からの入力は、SQL に一切含まれません。
とりうる値をすべて上記のように記述していけば、SQL に外部からの入力が挿し込まれることはありません。

ただ、これでは、条件分岐が多岐に分かれすぎるので、使いにくいです。

そこで、DB の持つ PreparedStatement の機能が注目されることになりました。
PreparedStatement とは、本来、SQL の変化する箇所以外を事前にコンパイルしておき、効率的に SQL を実行するための仕組みです。
しかし、その際に、構文自体を事前コンパイルし、パラメータとなるリテラルまたは変数値を後から突っ込めるようにする動作が、セキュリティ的に非常に優れたものとして評価されるようになりました。
これは、SQL の構造とデータを分離することに他ならず、後から投入されるパラメータは、リテラルまたは変数値としてしか判断されないため、構造に対しての変化を起こすことのできない仕組みです。

現在では、普遍的な SQL インジェクション対策として語られるようになりました。

すでに、多数の回答が付いていましたが、前者に関して少し記述しておきたかったので、投稿します。
前者は主に識別子をパラメータとする場合に使用します。

参考まで＾＾

Answer 6

COUNT(UserID) AS cnt
というSQL文の書き方からそもそも違う。基本的な脆弱性というのは、誤った手抜き方法をしているサイトに対して、誤認をさせるものだから、SQLを省略することなくしっかり書くことが大切。省略をすれば一般的には応答が高速化するが、だめな省略でも一見できてしまい、脆弱になるのがSQLインジェクションのよくあるかたち。頑張ってください。

Comments

[javahack]

COUNT(UserID) AS cnt の何がだめなのかわからないのですが、正しい書き方を知っているのであれば教えていただけないでしょうか。

[kokorohamoe]

すみません。Countが含まれるSQLの全文を通して。の意味で正しい間違えているはいいにくいです。私達が商用で、インジェクション対策として書くコードとは違っている。という意味です。発言を見ていると、わかっていておっしゃられていると思っていますので、お互いが共通知識を持っていることを確認するために抜粋のみで、お答えしています。　あるいみ、こんなところで、不特定多数に有償でうっているノウハウをべらべらとかかれてしまったら、そっちほうが、知識のインジェクション攻撃でダンピングですよね。なので、SQL　CountのこのSQLのような書き方をそもそもしない。ということです。　練習という意味ではいちど＊して　最大１件表示としぼり。すべてのパラメーターをちぇっくしてみてください。それで回答を十分見つけられると思います。

[kokorohamoe]

select * from の意味です。回答を１件のみにしろという構文はわすれてしまいました。ぐぐってください。その２つで表示されるものをみくらべて、何回かテストすれば、どこを忘れているかはすぐ見つかると思います。

[javahack]

COUNT()を含む構文に問題があるとは知りませんでした。
つまり、
SELECT COUNT(*) FROM テーブル名 WHERE 検索条件
では脆弱性の可能性があり、
SELECT TOP1 * FROM テーブル名 WHERE 検索条件 [SQL Server]
SELECT * FROM テーブル名 WHERE 検索条件 LIMIT 1 [MySQL]
と書くべきということでしょうか。

[kokorohamoe]

べきはないと思います。ただ、この場合はインジェクション攻撃を前提として、かつANDの基本的な構文で、など条件がついていますので、おかきになられたLimit 1などの構文のほうがよく使われるということです。インジェクション攻撃であればLimitは本来不要ですが、セキュリティーを考えると、保安のためにつけておく。というのと近いと思います。
そもそも、インジェクションであれば、スクリプトに署名をするなどの他の方法もあり、署名付きSQLであれば、おっしゃられているようにCountを使ったほうが良いと思います。ただ、お話を見る限り、署名付きのSQLではないと判断しましたので*などでエラーチェックなどを厳密に行ったほうがよいかとおもいコメントするに至りました。

[kokorohamoe]

SQLインジェクションは、某声優さんなどでもいまはやりらしいですが、もう１つプルートフォースアタック。というフォースの加護をというスターフォースみたいな攻撃もあります。総当たり攻撃ともいうのですが、器械を使って何度も何度も何百万回もくりかえす攻撃があります。Limit 1などインジェクションには直接関係があるとは言い難い文言を追加していくのもそのためです。SQLがこのCountを使ったものだった場合、集計処理が何百万回も実行されることになります。そのため、SQLインジェクションに直接関係ないから、といえどもSQLインジェクション対策でこういうコードを書かないようにしています。他の攻撃の踏み台にされず、かつ、SQLインジェクションを防ぐコードというふうにかんがえていただけると、正解はもうすぐだとおもいます。

[kokorohamoe]

考え方　Select * ：追加削除をしても、このレコードでなければかわらない。から同時に行える
Select count　集計中には、Insertなどを同時には行えない。など、いろいろあります。
バックエンドでたくさんの処理が同時に走ることもあり、そのなかの１件だけがSQLインジェクションというふうに考えてみると　正解に近づけると思います。（考え方のヒント）

[javahack]

COUNNT()にSQLインジェクション攻撃に対する脆弱性があると書かれているかと思っての確認でしたが、
・COUNNT()自体に脆弱性があるわけではない
・SQLインジェクション以外の攻撃を考慮し、COUNNT()のような集計関数を使用しなくても良い場面では、使用を避ける
ということと理解しました。

[kokorohamoe]

脆弱性といわれると、定義がある単語でしょうからちょっと違いますし、SQLインジェクションへの対処方法も１つではないでしょうから、表現の問題でなんともいえませんが、SQLインジェクションとCountの組み合わせはあまりつかわれないというか、SQLインジェクション『も』含め、SQLインジェクションにも関係はしますが、他の脆弱性のこともあるので、結果論としてCountは使われませんし、結果論としてCountを使わず他のアルゴリズムを使うことで、結果論脆弱性対策にもつながる1手になるため、元質問であるHinanoさんを参考に回答すると　select *を使う、limit など数を絞る関数をつかつなどの対処をするため、問題そのものは先に進み、次の脆弱性へと考慮が進むかと思います。日本語がへたですみません。かいとうをここまでとします。　説明が下手ですみません。誤解がないようになるべくせつめいいたしました。

[kokorohamoe]

通常はサニティーチェックとよばれる入力の正当性の検証や、使ってはいけない文字の確認など、セキュリティーチェックをすることなど　他の技法と組み合わせることが多いと付記しておきます。
それでは、ありがとうございました。

[Zuishin]

チンパンジーに低評価しておきます。もうこのユーザーの回答は読まずに低評価しても間違いないと思います。