none
SELECT文中のコラム名を変数で指定する場合、どのようにすればよいのでしょうか RRS feed

  • 質問

  •  

    ユーザ定義関数の中で、

    (SELECT  @colName  FROM TableName  WHERE colID=@colid)

    を実行し、その結果をリターン値に代入するというものですが、

     

    結果として返ってくるのが、@colNameの内容がそのまま帰ってきます。

     

    @colName,@colid1はともに関数の引数で、

    @colNameは、nvarchar(20)

    @colidは、int

    です。

     

    ちなみにSELECT文中の@colNameのところをじかに列名を指定すると期待する値が返るので、

    変数で指定する場合になにかひと工夫いるのでしょうか。

     

    また、@colNameのところをCOL_NAME()関数でやってもみましたが、結果は同じでした。

     

     

     

    2008年8月14日 4:00

回答

  • 既にtrapemiyaさんが回答されていますが、動的SQLで対応可能です。

    強いて言えば、execを使うのではなく、sp_executesqlを使ったほうが安全です。

    (SQLインジェクション攻撃に対して。マイクロソフトもsp_executesqlの使用を推奨しています)

     

    > ちょっと思ったのですが、もしかしてセキュリティ上SELECT文中に文字列の変数を指定することは

    > いけないことなのでしょうか。

     

     文字列の変数を指定すること自体は問題ありません。

     問題があるとすれば、画面等からの入力値をそのままクエリーの引数として使用する場合に問題となります。

     対策としては、文字列を直接WHEREの条件とするのではなく、パラメータとして引数とすることで対策できます。

    (これがsp_executesqlを推奨する理由なのですが)

     

    2008年8月14日 6:52
  •  CatTail さんからの引用

    強いて言えば、execを使うのではなく、sp_executesqlを使ったほうが安全です。

    (SQLインジェクション攻撃に対して。マイクロソフトもsp_executesqlの使用を推奨しています)

     

    私が配慮が足りないコードを載せてしまいました。

    列名の部分はパラメータ化できませんので、sp_executesqlを使ったところで適切なエスケープを行うことができず、自分でエスケープしなければなりません。どのみち自分でエスケープするのであれば、@colidも自分でエスケープしてしまえばいいんじゃないかというのが頭にありました。

    いずれにしても配慮が足りませんでした。フォローありがとうございます。

     

    というわけで、@colNameは自分で適当にエスケープした後にsql文を組み立て、それをsp_executesqlに渡し、@colidの方はsp_executesqlのパラメータとしてしまえば自動的にエスケープされます。

    2008年8月14日 7:50

すべての返信

  •  

    (SELECT  @colName  FROM TableName  WHERE colID=@colid)

    この部分を

    (SELECT  @colName = 列名 FROM TableName  WHERE colID=@colid)

     

    にすれば、期待した値が返ってこないですか?

     

    2008年8月14日 4:47
  • もしかして、テーブルの列名をリターン値として返したいのでしょうか?

    それであれば、

     
    SELECT @colName = COL_NAME(OBJECT_ID('TableName'), @colid)
     
    で列名を返せると思います。
     
    2008年8月14日 5:11
  • CatTailさん早速ありがとうございます。

     

    まぎらわしい質問ですみません。

    求めているのは、列名ではなくあくまでフィールド値です。

    つまりはSELECT文の列名のところを関数の引数で指定したいのです。

     

    ちょっと思ったのですが、もしかしてセキュリティ上SELECT文中に文字列の変数を指定することは

    いけないことなのでしょうか。

     

     

     

    2008年8月14日 5:37
  • 以下のようにしてみて下さい。

     

    exec ('select ' + @colName + ' from TableName where colID=' + @colid')

    2008年8月14日 6:28
  • 既にtrapemiyaさんが回答されていますが、動的SQLで対応可能です。

    強いて言えば、execを使うのではなく、sp_executesqlを使ったほうが安全です。

    (SQLインジェクション攻撃に対して。マイクロソフトもsp_executesqlの使用を推奨しています)

     

    > ちょっと思ったのですが、もしかしてセキュリティ上SELECT文中に文字列の変数を指定することは

    > いけないことなのでしょうか。

     

     文字列の変数を指定すること自体は問題ありません。

     問題があるとすれば、画面等からの入力値をそのままクエリーの引数として使用する場合に問題となります。

     対策としては、文字列を直接WHEREの条件とするのではなく、パラメータとして引数とすることで対策できます。

    (これがsp_executesqlを推奨する理由なのですが)

     

    2008年8月14日 6:52
  •  CatTail さんからの引用

    強いて言えば、execを使うのではなく、sp_executesqlを使ったほうが安全です。

    (SQLインジェクション攻撃に対して。マイクロソフトもsp_executesqlの使用を推奨しています)

     

    私が配慮が足りないコードを載せてしまいました。

    列名の部分はパラメータ化できませんので、sp_executesqlを使ったところで適切なエスケープを行うことができず、自分でエスケープしなければなりません。どのみち自分でエスケープするのであれば、@colidも自分でエスケープしてしまえばいいんじゃないかというのが頭にありました。

    いずれにしても配慮が足りませんでした。フォローありがとうございます。

     

    というわけで、@colNameは自分で適当にエスケープした後にsql文を組み立て、それをsp_executesqlに渡し、@colidの方はsp_executesqlのパラメータとしてしまえば自動的にエスケープされます。

    2008年8月14日 7:50
  • CatTailさん、trapemiyaさん、ありがとうございます。

     

    ご指摘の方法を試してみます。

     

     

    2008年8月14日 9:37