テスト中に画面でエラーが発生してしまった。
調査の結果以下のような原因だった。
- 検索条件に'(シングルクオーテーション)が含まれていてストアドプロシージャでエラーになっているようだ。
使用しているストアドでは動的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で仕様を実装できるかを確認したほうが良い