テスト中に画面でエラーが発生してしまった。

調査の結果以下のような原因だった。

検索条件に'(シングルクオーテーション)が含まれていてストアドプロシージャでエラーになっているようだ。

使用しているストアドでは動的sql※1(呼び方がわからないので以下このように呼びます。）を使用していた。

解決方法

以下の解決法が選択肢として上がりました。

ストアドプロシージャに渡すパラメータはすべてエスケープ処理を行う（SQLServerの場合はシングルクオーテーションをもう一つつける)
sp_executesqlの使用を検討する

検討の結果後者のsp_executesqlを使用することにしました。

実際にどのようなエラーだったか、
テストソースで解説します。

呼び出し元Java


 public static void callProcedure() throws SQLException {

		CallableStatement  cs = null;
		try (Connection con= DriverManager.getConnection(sqlcall.calltest())){
			cs = con.prepareCall("{? = call dbo.sp_TESTA(?)}");
			cs.registerOutParameter(1, java.sql.Types.INTEGER);
			cs.setString(2, "ff'ffff");
			cs.execute();
			ResultSet rs = cs.getResultSet();

			while(rs.next()) {
				System.out.println(rs.getString(1));
			}
			System.out.println("calltest"+ cs.getInt(1));

		}catch(SQLException e){
			e.printStackTrace();

		}
	}

testTableのデータ

Test1
ff'ffff

動的SQL※1



CREATE PROCEDURE [dbo].[sp_TESTA] 
	@TESTA nvarchar(40)
AS
BEGIN try
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;

	DECLARE @query nvarchar(MAX) = '';
	DECLARE @parameters nvarchar(max) = '';

	

	SET @query = N' SELECT Test1 '
	SET @query = @query + N' FROM dbo.testTable '
	SET @query = @query + N' where Test1 = '''+ @TESTA+''''

	execute ( @query);

	return 0;

END try
begin catch

	SELECT   
        ERROR_NUMBER() AS ErrorNumber  
       ,ERROR_MESSAGE() AS ErrorMessage; 
	return  -1

end catch
GO

'ffff' 付近に不適切な構文があります。 calltest-1
文字内にある'(シングルクオーテーション)が文字の区切りとして認識され、後ろ4文字が文字として認識されませんでした。

これを以下のようにsp_executesqlを使用するように修正しました。

sp_executesql


CREATE PROCEDURE [dbo].[sp_TESTA] 
	-- Add the parameters for the stored procedure here
	@TESTA nvarchar(40)
AS
BEGIN try
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @query nvarchar(MAX) = '';
	DECLARE @parameters nvarchar(max) = '';

	
	SET @query = N' SELECT Test1 '
	SET @query = @query + N' FROM dbo.testTable '
	SET @query = @query + N' where Test1 = @Test1 '
	
	set @parameters = N'@Test1 nvarchar(40) '

	
	execute sp_executesql @query,@parameters, @Test1 = @TESTA
		
	return 0;

END try
begin catch

	SELECT   
        ERROR_MESSAGE() AS ErrorMessage; 
	return  -1

end catch
GO

ff'ffff calltest0

無事文字として認識され正常に検索条件として反映されました。

まとめ

画面で渡された値を使用する場合にはsp_executesqlをなるべく使うこと。インジェクション対策にもつながるので、sp_executesqlで仕様を実装できるかを確認したほうが良い

参考

Microsoft SQLドキュメント sp_executesql (Transact-sql)