3

I have the following SQL insert statement inside the loop which iterate through a cursor:

SELECT @q_sql = 'INSERT INTO SYS_PARAMETER(parameter_uno, parameter_key, description, parameter_value, comments, created_date, created_user_id, created_user_name, last_modified_user, last_modified_date, module_uno, data_type)
                 VALUES ('+@sysparam_uno + ','''+@q_parameter_key+''','''','''+b.pairvalue+''','''',
         getdate(),''setup'',''setup'',''setup'',getDate(),'''+@q_module_uno+''','''')'
         from UTIL_pairkeys a
         INNER JOIN UTIL_pairvalues b on a.pairkeyuno = b.pairkeyuno
         and b.languno = 1
         where a.pairkey=@q_parameter_key

         EXEC sp_executesql @q_sql

Due to value coming to b.pairvalue parameter having a single quote, insert statement fails on SQL Server 2005, but work well on SQL Server 2008R2 and later versions. Any knows reason for this? I know that insert statement fails once parameter value has single quote in between varchar columns. But this something strange here.

Sample insert statement as follows;

INSERT INTO SYS_PARAMETER(parameter_uno,parameter_key,description,parameter_value,comments,created_date,created_user_id,created_user_name,last_modified_user,last_modified_date,module_uno,data_type)
values (269,'application.fs.company','','St John's Foods','',getdate(),'setup','setup','setup',getDate(),'1','')
nwGCham
  • 313
  • 1
  • 3
  • 16
  • Show us `select @q_sql` – Alex K. Aug 14 '15 at 12:22
  • Can you post one of the generated insert statements that is failing? – Dan Guzman Aug 14 '15 at 12:22
  • 4
    [SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection – marc_s Aug 14 '15 at 12:25
  • print @q_sql updated at the end of question. Thanks. – nwGCham Aug 14 '15 at 12:30
  • 3
    Your `'St John's Foods'` breaks the SQL string (this will happen on all versions of SQL Server btw). Parametrize it as @marc_s suggests by using [*input parameters* with sp_executesql](http://stackoverflow.com/questions/16308207/a-more-elegant-way-of-escaping-dynamic-sql) – Alex K. Aug 14 '15 at 12:36
  • do you have same data on both servers ? because if you only have the St John's food in sql2005 instance, it's pretty obvious what's the problem. – Horia Aug 14 '15 at 12:48
  • @SQLDiver your answer is correct. You should not have deleted it – t-clausen.dk Aug 14 '15 at 19:34

3 Answers3

1

If it is a problem of single quote only than you can replace it by two single quotes like this:

replace( b.pairvalue ,'''','''''')
Sachin
  • 2,152
  • 1
  • 21
  • 43
1

I order to escape ', you need to replace it with '':

SELECT @q_sql = '
INSERT INTO SYS_PARAMETER(parameter_uno,parameter_key,description,parameter_value,comments,created_date,created_user_id,created_user_name,last_modified_user,last_modified_date,module_uno,data_type)
values ('+@sysparam_uno + ','''+@q_parameter_key+''','''','''+REPLACE(b.pairvalue, '''', '''''')+''','''',getdate(),''setup'',''setup'',''setup'',getDate(),'''+@q_module_uno+''','''')'
         from UTIL_pairkeys a
         INNER JOIN UTIL_pairvalues b on a.pairkeyuno = b.pairkeyuno
         and b.languno = 1
         where a.pairkey=@q_parameter_key

EXEC sp_executesql @q_sql

However, it's best if you could use parametrisation instead:

DECLARE @pair_value VARCHAR(100)

SELECT @pair_value = b.pair_value     
from UTIL_pairkeys a
        INNER JOIN UTIL_pairvalues b on a.pairkeyuno = b.pairkeyuno
        and b.languno = 1
        where a.pairkey=@q_parameter_k

SELECT @q_sql = '
INSERT INTO SYS_PARAMETER(parameter_uno,parameter_key,description,parameter_value,comments,created_date,created_user_id,created_user_name,last_modified_user,last_modified_date,module_uno,data_type)
VALUES( @parameter_uno_param,
        @parameter_key_param,
        '''',
        @parameter_value_param,
        '''',

        getdate(),
        ''setup'',
        ''setup'',

        getdate(),
        ''setup'',
        @module_uno_param,
        '''')'

EXEC sp_executesql 
                @q_sql,
                N'  @parameter_uno_param    VARCHAR(100),
                    @parameter_key_param    VARCHAR(100),
                    @parameter_value_param  VARCHAR(100),
                    @module_uno_param       VARCHAR(100)
                    ',
                @sysparam_uno,
                @q_parameter_key,
                @pair_value,
                @q_module_uno

This assumes your select will only find one pair_value. If more, you need to consider looping through those.

Horia
  • 1,612
  • 11
  • 18
  • 1
    why down vote ? at least say what's wrong with this solution. thank you. – Horia Aug 14 '15 at 12:44
  • 1
    the problem is the keyword VALUES which was introduced in sqlserver 2008,.Question is why doesn't this work in sqlserver 2005(while working in 2008). Besides the ' has already been escaped – t-clausen.dk Aug 14 '15 at 12:45
  • 1
    @t-clausen.dk, i`m quite convinced the problem is he has St John's on 2005 instance only, because otherwise there's no way for it not to work on 2005, but work on 2008, hence my solution will fix that problem. – Horia Aug 14 '15 at 12:50
  • Yes you are right, but that is just a poorly constructed example. The real query uses variables. – t-clausen.dk Aug 14 '15 at 12:52
  • Please take a chance to actually read my answer, i am using variables, but from the example posted above, I cannot guess the cursor and other statements - so that part is missing. Also, please review you're comment above, VALUES keyword was NOT introduced in 2008, it was definitively way earlier than that. – Horia Aug 14 '15 at 12:57
  • @Juan DEFAULT VALUES is available. However not Table Value Constructor which is completely different. – t-clausen.dk Aug 14 '15 at 13:03
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/86998/discussion-between-horia-and-t-clausen-dk). – Horia Aug 14 '15 at 13:09
  • Table value constructor is irrelevant here. Because each time it iterate through the cursor, it generate new insert statement. I have mentioned one of the statement at the end of question. Also VALUES was part of INSERT statement from the beginning. @Horia : I have clearly mentioned that this insert statement generate when iterate through cursor. I am expecting reason why this is failing 2005 and works well 2008R2 or later version.... – nwGCham Aug 14 '15 at 13:48
  • @javaGuy Table value constructor is NOT irrelevant here.. As soon as you are using the VALUES(x,y,x), you have invalid syntax in sqlserver 2005. Besides you should probably not have used a cursor to call this procedure. Use a table TYPE instead. Not going to comment further on this question. – t-clausen.dk Aug 14 '15 at 18:32
0

Above code segment call by java program and since SQL Server 2005 thrown an exception to the calling java program it cause to break the java program itself. However, for SQL Server 2008, it look like database engine it self handle the exception without throwing exception to the calling application. To prove that I added try catch block to the error prone code segment and ran the java program against the SQL Server 2005. So, calling application didn't break. Did same against the SQL Server 2008 R2 and result was same.

nwGCham
  • 313
  • 1
  • 3
  • 16