-1

When I try to execute the following:

DECLARE @sqlText nvarchar(1000); 

SET @sqlText = N'SELECT TOP ' + CAST((:intValue) AS VARCHAR(20)) +  't_stamp, PART_NUM, ' + :Column  + ' FROM dbo.press_7_recipes_log WHERE PART_NUM = ' + CAST(:text AS VARCHAR(20))
Exec (@sqlText)

I am getting the following

error:com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the varchar value '43302001-8' to data type int.

Any help would be greatly appreciated, not sure what else is required here.

:intValue is of type int4
:text is of type string
:Column is of type string (This is pulling a specified column from the database and why I think this needed to be a dynamic query)

Tried multiple attempts at googling the issue and changing the command with the same outcome. If I change the PART_NUM in the where to a column that is of type int the code works fine, any string related column does not.

Thom A
  • 88,727
  • 11
  • 45
  • 75
James
  • 1
  • 2
    Are you hoping for 43302001 MINUS 8 ? Otherwise '43302001-8' is not an INT nor will it ever be an INT – John Cappelletti Jan 13 '23 at 17:27
  • 2
    This above looks like injection as well; why aren't you using parametrisation? – Thom A Jan 13 '23 at 17:27
  • @JohnCappelletti No, the value that is in the error is of type string in the database, PART_NUM is a string in the database so I am trying to look for '43302001-8' (from a parameter passed to this query) and then display all data for that entry in specified columns. PART_NUM, and t_stamp are columns in the database already and the last column is selected from a drop-down menu, as to why it is also a parameter. – James Jan 13 '23 at 18:59
  • @Larnu Firstly thanks for editing the post to make it presentable, I will do the same in the future. Secondly, I thought I was using parametrization but perhaps I don't know the difference between injection. – James Jan 13 '23 at 19:06

1 Answers1

0

The problem is that after your preparation the query becomes: SELECT TOP 666 t_stamp, PART_NUM, ANOTHER_COLUMN FROM dbo.press_7_recipes_log WHERE PART_NUM = 43302001-8

And since 43302001-8 is an INTEGER=43301993, SQL Server converts PART_NUM column to INT, which doesn't work since it probably contains non-integers.

You need to change your dynamic query to this me thinks:

DECLARE @sqlText nvarchar(1000); 

SET @sqlText = N'SELECT TOP ' + CAST((:intValue) AS VARCHAR(20)) +  't_stamp, PART_NUM, ' + :Column  + ' FROM dbo.press_7_recipes_log WHERE PART_NUM = ''' + REPLACE(CAST(:text AS VARCHAR(20)), '''', '''''') + ''''
Exec (@sqlText)

This will change WHERE to: PART_NUM = '43302001-8'

But as others noticed, you have a lot of possibilities for SQL Injections here. So i'd probably get rid of this code and rewrite it to avoid dynamic SQL

siggemannen
  • 3,884
  • 2
  • 6
  • 24