WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

パラメータ化クエリと sp_executesql 変換

by WebSurfer 11. December 2019 16:05

SqlParameter クラスを利用してクエリをパラメータ化すると sp_executesql に変換されて SQL Server で実行されるのですが、変換によって予期せぬ問題が出ることがあるという話を書きます。

sp_executesql に変換

元の話は MSDN Forum のスレッド「パラメータを利用したLikeで欲しい結果が得られない 」です。

具体的にどういうことかは以下の通りです。

SQL Server のデータベースに以下の画像の通り tbl_Item テーブルがあり、Item_CD 列で LIKE 句による検索をかけるとします。型の nvarchar(6) を覚えておいてください。

tbl_Item テーブル

この tbl_Item テーブルをベースに、Visual Studio のデーターソース構成ウィザードで型付 DataSet + TableAdapter を作成します。Item_CD 列で検索できる TableAdapter のメソッドを追加します。その SELECT クエリは以下の画像の通りです。

TableAdapter のメソッド

上の画像の通り型付 DataSet + TableAdapter が作成できたら「データのプレビュー」画面を開きバラメータ ICD に %A00001% を代入して[プレビュー]ボタンをクリックします。3 レコードヒットすると期待しますが、結果は一件もヒットしません。

データのプレビュー

ICD に代入した %A00001% を %00001% に代えた場合は 3 件のレコードがヒットします。上に紹介した MSDN Forum のスレッドの最初の質問に書いてあった通りです。

データのプレビュー

何故 %A00001% では一件もヒットしないかですが、上に紹介した MSDN Forum のスレッドで質問者さんがアップしてくれたプロファイラの画像を見ると分かるので、借用して下に貼っておきます。

プロファイラの画像

理由は Item_CD 列の型 nvarchar(6) の 6 で制限を受けるからです。具体的には以下の 2 つです。

(1) SqlParameter の Size

上の画像の通り SELECT クエリをベースに Visual Studio のデータソース構成ウィザードで FillByICD, GetDataByICD メソッドを生成していますが、その際 @ICD には SqlParameter が以下のように初期化されパラメータとして設定されます。

this._commandCollection[1].Parameters.Add(
  new SqlParameter("@ICD", SqlDbType.NVarChar, 6,
  ParameterDirection.Input, 0, 0, "Item_CD", 
  DataRowVersion.Current, false, null, "", "", ""));

SqlParameter コンストラクタの第 3 引数がデータベースの Item_CD 列の型 nvarchar(6) の 6 となり、結果 SQL Server に送信するデータの最大量が 6 文字に制限されます。

上のプロファイラの画像で @CID=N'%A0000' と 6 文字になっているところに注目してください。この時は C# のコードでは "%A00001%" という文字列を代入したとのことですが、SQL Server では先頭から 6 文字だけしか受け取れていません。

(2) sp_executesql の N'@ICD nvarchar(6)'

SqlParameter を使った ADO.NET のパラメータ化クエリは、上のプロファイラ画像のとおり sp_executesql に変換されます。その中の N'@ICD nvarchar(6)' の 6 でも制限を受けます。

一番上の画像を見てください。SSMS で sp_executesql を実行したもので、そこでは @CID=N'%A00001%' としていますが、先頭から 6 文字しか SQL Server には渡せないようで、一件もヒットしていません。(@ICD には %A0000 が代入されると思われる)

ちなみに、N'@ICD nvarchar(6)', の 6 を 7 に変えると同じく @CID=N'%A00001%' で 3 件ヒットします。(@ICD には %A00001 が代入されると思われる)

何が N'@ICD nvarchar(6)', の 6 を決めているのかという疑問は残っていますが、この 6 が制限の一つであるのは結果から間違いなさそうです。

回避策は WHERE 句を WHERE Item_CD LIKE N'%' + @ICD + N'%' のように変更して、@ICD には A00001 と入力するのがよさそうです。

と言うか、普通は最初から WHERE Item_CD LIKE N'%' + @ICD + N'%' とすると思います。だから、今回の問題のような話は初耳だったのかもしれません。

LIKE 句を使った時の問題は、自分が知る限りですが先の記事「固定長パラメータの LIKE 比較」に書いた話がありました。

加えて、LIKE 句を使うと今回のような問題も起こり得るということを知ることができて、MSDN Forum のおかげで一つ利口になったような気がします。

Tags: , ,

ADO.NET

コンマ区切りスクリプトと検証の整合

by WebSurfer 28. November 2019 15:41

数字を 3 桁でコンマ区切りする JavaScript と ASP.NET MVC5 のクライアント側での検証の話です。

コンマ区切りスクリプトと検証の整合

元の話は Teratail のスレッド「3桁コンマ区切り数字をコンマ無しでFrom送信したい」です。

コンマ区切り用 JavaScript のコードの動作は、初期画面では数字を 3 桁でコンマ区切りし、ユーザーが編集するときはコンマを除去し、編集完了後は再び 3 桁でコンマ区切りするというものです。コードは Teratail のスレッドの私 SurferOnWww の回答にありますので見てください。

そのコンマ区切り用 JavaScript の動作と、以下のようにモデルにアノテーション属性を付与するとデフォルトで有効になる控えめな JavaScript による検証が、かなり微妙ながら基本的なところでは整合を取って動きましたので、備忘録として書いておくことにしました。(実は、バッティングして動かないと思い込んでました(汗))

public class PurchaseRecord
{
  // ・・・中略・・・

  [Display(Name = "価格")]
  [Required(ErrorMessage = "{0} は必須")]
  [RegularExpression(@"^\d{1,6}$", 
    ErrorMessage = "数字 1 ~ 6 文字")]
  [Range(100, 10000, 
    ErrorMessage = "{0}は{1}~{2}の間で入力してください。")]
  [DisplayFormat(DataFormatString = "{0:N0}", 
      ApplyFormatInEditMode = true)]
  public decimal Price { get; set; }
}

どのような動きになるかと言うと以下の通りです:

  1. テキストボックスの初期表示は 1,234
  2. ユーザーが編集動作に入る時 focus イベントが発生しスクリプトで 1234 に書き換わる
  3. ユーザーが例えば 3210 というように編集
  4. 次の作業に移るためフォーカスを外す
  5. change イベントが発生し 3210 に対し検証がかかる
  6. blur イベントが発生しスクリプトで 3,210 に書き換える
  7. ユーザーが送信ボタンをクリック
  8. submit イベ���トが発生しスクリプトで 3,210 を 3210 に書き換える
  9. サーバーで 3210 を受信、サーバー側での検証は OK となる。

・・・という順序になってうまくいきます。

以上、基本的な動きは OK ではあるものの、かなり微妙なところで動いていますので、実際に運用に使う場合は十分な検証が必要だと思います。思いつくのは:

(1) change ⇒ blur の順序でイベントが発生しなければならないが、全てのブラウザでそうかは不明。(メジャーなブラウザは大丈夫のようですが、昔の Forefox は反対だったという話があります)

(2) 上のステップ 3 でユーザーが数字だけ入力してくれると期待するのは無理がある。(上のサンプルでは RegularExpression 属性を追加してチェックするようにしてますが、それで十分か?)

(3) ユーザーがブラウザの JavaScript を無効にした場合はサーバー側だけで検証することになる。

・・・などです。

特に、不特定多数のユーザーが不特定多種のブラウザでアクセスしてくるインターネットに公開するような場合は別の方法(サーバー側だけで検証するとか、String 型にするとか)を考えた方が良いかもしれません。

今のところ気が付いた問題点は以下の通りです:

問題 1: ステップ 3 でユーザーが 3,210 とカンマを入れて入力すると、ステップ 5 の時点ではカンマ入りなので正規表現での検証で引っかかるという問題があります。

問題 2: コンマ区切り用 JavaScript のコードには全角 ⇒ 半角変換の機能が実装されていますが、タイミングの問題で検証に引っかかってしまいます。どういうことかと言うと、全角数字を入力するとステップ 6 の時点で半角に変換しますが、検証がかかるステップ 5 の時点ではまだ全角なので正規表現による検証で NG となります。

その後、ステップ 6 の時点で半角に変換されるので、見かけは正しく半角なのにエラーが出て混乱を招くと思います。なので、全角 ⇒ 半角変換のコードは削除した方がよさそうです。

最後にオマケを二つ書いておきます。

その 1: 上のステップ 2 で編集操作に入った時、キャレットが末尾にあるのが自然と思いますが、そうしたい場合は以下のように 2 行追加してください。

elm.addEventListener('focus',
  function () {
    this.value = delFigure(this.value);

    // キャレットを文字列の末尾に持ってくる
    // ため以下の 2 行を追加
    var len = this.value.length;
    this.setSelectionRange(len, len);
  }, false);

その 2: クライアント側での検証を無効にすると「価格」として有効でない文字列、例えば 123x とかでもサーバーに送信されてしまいます。その場合、モデルバインディングできないのでアノテーション属性に設定した検証がかかる以前にエラーとなります。

そのエラーメッセージが気に入らないので自分で設定したいという場合は Controller にコードを追加して書き換えることができます。詳しくは別の記事「int 型プロパティの検証、エラーメッセージ」を見てください。

Tags: , , ,

MVC

ASP.NET / IIS の要求サイズの制約

by WebSurfer 20. November 2019 13:55

ASP.NET と IIS には要求のサイズを制限する組み込みのセキュリティ機能があります。httpRuntime 要素の maxRequestLength 属性と requestLimits 要素の maxAllowedContentLength 属性が良く知られています。

それ以外に serverRuntime 要素の uploadReadAheadSize 属性というのもあって、それに気が付かないでハマるということがありましたので、備忘録としてまとめて書いておきます。元の話は Teratail のスレッド「asp.net ajax upload でエラーになる」です。

(1) httpRuntime 要素の maxRequestLength 属性

ASP.NET の制限でデフォルトで 4MB になっています。詳しくは Microsoft のドキュメント httpRuntime 要素 (ASP.NET 設定スキーマ) を見てください。肝心な部分のみ以下に抜粋しておきます。

"入力ストリームのバッファリングしきい値の限界値を KB 単位で指定します。 この限界値は、たとえば大きいファイルをサーバーにポストするユーザーなどにより引き起こされる、サービス拒否攻撃を防止するために使用できます。既定値は 4096 です。 しきい値を超えると、ConfigurationErrorsException 例外がスローされます。"

(2) requestLimits 要素の maxAllowedContentLength 属性

IIS7 から導入された要求のフィルタリング <requestFiltering>の機能の中の requestLimits 要素の maxAllowedContentLength 属性がそれで、デフォルト値が 30,000,000 バイトになっています。下の IIS Manager での設定画面を見てください。

maxAllowedContentLength 属性

設定変更は上の画像のように IIS Manager で行うのがお勧めです。applicationHost.config ファイルをメモ帳などで開いて編集することでも可能ですが。

(3) serverRuntime 要素の uploadReadAheadSize 属性

以下の IIS Manager の画像の通りデフォルトで 49,152 バイトになっており、この制約にかかると HTTP 413 Request Entity Too Large というエラーになります。上に紹介した Teratail のスレッドの話がこの問題です。

uploadReadAheadSize 属性

Microsoft のドキュメント Solution for “Request Entity Too Large” errorLarge file upload failure for Web application calling WCF service – 413 Request entity too large によると、SSL 通信を利用しているとき、または WCF に要求を出す場合は uploadReadAheadSize 属性の制約の影響を受けるとのことです。

ただ、SSL だけでそういう問題が出るとすると FAQ レベルの話&周知の事例なのに、ググって調べた限りそうでもなさそうなのが不思議でした。

さらに調べてみると、IIS randomly returns 413 Request Entity Too Large when uploading large files and using TLS という記事が見つかり、それによると client certificate も絡んだ問題と書いてあります。

上の記事の回答で黄色のバックグラウンドとなっている部分は IIS Express を使った時のエラーメッセージのようです。その中に:

Most likely causes: The Web server cannot service the request because it is trying to negotiate a client certificate but the request entity is too large.

If using client certificates, try: Increasing system.webServer/serverRuntime@uploadReadAheadSize

・・・とあります。

実際、上に紹介した Teratail の記事の問題のサイトでは client certificate を使っているようです。

ただし、自分の環境の Visual Studio Community 2015 で IIS Express で SSL 通信を利用する設定にし、sslFlags を SslNegotiateCert に設定して試してみましたが、uploadReadAheadSize はデフォルトの 49,152 バイトのままでも問題を再現できませんでしたが・・・

(4) JSON 文字列を送信する場合の制約

JSON 文字列のデシリアライズに使用されている(と思われる)JavaScriptSerializer クラスの MaxJsonLength プロパティによって、Web サービスの場合はデフォルトで 102,400 文字に、ASP.NET MVC のアクションメソッドの場合は 2,097,152 文字に制限されています。

前者(Web サービス)の方は jsonSerialization 要素の maxJsonLength 属性の設定によって変更可能です。後者(ASP.NET MVC のアクションメソッド)の場合は 2,097,152 文字から変更できません。

2013 年時点の情報なので今は変更されているかもしれませんが、詳しくは別の記事「MVC は maxJsonLength を無視」を見てください。

Tags: , ,

ASP.NET

About this blog

2010年5月にこのブログを立ち上げました。その後 ブログ2 を追加し、ここは ASP.NET 関係のトピックス、ブログ2はそれ以外のトピックスに分けました。

Calendar

<<  December 2019  >>
MoTuWeThFrSaSu
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345

View posts in large calendar