how to assign the string variable in select query

Question

ho to add the string variable in SQL Select query. "tw.local.subBarCode" is a string varaible and "tw.local.caseID" it is also string varaible.

Example: In Variable have some value

CASE_ID=15232 and SUBBARCODE=ODDV-1803-015232-HL01.

tw.local.sql="select CS.ORIGINALCHECK_ID AS checkId, "+
"CS.CHECKTYPE_ID AS checkTypeId, "+
"CS.CHECKTYPE_ID AS componentID, "+
"CT.NAME AS componentName "+
"from CHECK_SUMMARY CS INNER JOIN CHECKTYPE CT "+
"ON CT.ID=CS.CHECKTYPE_ID "+
"where CS.ORIGINALCHECK_ID="+tw.local.subBarCode+" and CS.CASE_ID="+tw.local.caseID+"";

Below like this when I debugging it is taking variables in my select query.

select CS.ORIGINALCHECK_ID AS checkId, 
CS.CHECKTYPE_ID AS checkTypeId, 
CS.CHECKTYPE_ID AS componentID, 
CT.NAME AS componentName from 
CHECK_SUMMARY CS INNER JOIN CHECKTYPE CT 
ON CT.ID=CS.CHECKTYPE_ID 
where CS.ORIGINALCHECK_ID=&apos;ODDV-1803-015232-HL01&apos;&apos; 
and CS.CASE_ID=15232

Please anyone helps me on this issue.

I need a query like below when I debug.

 select CS.ORIGINALCHECK_ID AS checkId, 
CS.CHECKTYPE_ID AS checkTypeId, 
CS.CHECKTYPE_ID AS componentID, 
CT.NAME AS componentName from 
CHECK_SUMMARY CS INNER JOIN CHECKTYPE CT 
ON CT.ID=CS.CHECKTYPE_ID 
where CS.ORIGINALCHECK_ID=&apos;ODDV-1803-015232-HL01&apos; 
and CS.CASE_ID=15232

Don't use concatenation, since it's highly unsafe (SQL Injection attacks). Use Parameterized SQL, that used question mark (?) for each parameter and then apply the parameter values. — The Impaler, Apr 02 '18 at 14:41

score 1 · Accepted Answer · answered Apr 03 '18 at 16:53

Based on your code snippet and the fact that you have tagged "BPM" it appears that you are asking this question about executing SQL in the product IBM BPM. While you don't call out the version of the product I will assume it is 7.5 or later.

Assuming all of this is correct you should be executing the query using the service "SQL Execute Statement" from the System Data Toolkit. You are attempting to create the full SQL statement instead of leveraging the parameterized query capability (which would do what @The Impaler is recommending)

You need to modify your SQL so that it can use the parameterized option. The code should be something like -

tw.local.sql="select CS.ORIGINALCHECK_ID AS checkId, "+
"CS.CHECKTYPE_ID AS checkTypeId, "+
"CS.CHECKTYPE_ID AS componentID, "+
"CT.NAME AS componentName "+
"from CHECK_SUMMARY CS INNER JOIN CHECKTYPE CT "+
"ON CT.ID=CS.CHECKTYPE_ID "+
"where CS.ORIGINALCHECK_ID=? and CS.CASE_ID=?";

You would then set the parameter value with something like the following -

tw.local.parameters = new tw.object.listOf.SQLParameter();
tw.local.parameters[0] = new tw.object.SQLParameter();
tw.local.parameters[0].value = tw.local.subBarCode;
tw.local.parameters[0].type = 'VARCHAR'; //Note: Guessing here.
tw.local.parameters[1] = new tw.object.SQLParameter();
tw.local.parameters[1].value = tw.local.caseID;
tw.local.parameters[1].type = 'INTEGER';

You would then hand those both into the SQL Execute Statement service and everything should just work. You are protected from SQL injection, and won't magically break if one of your variables has say a ' in the string.

Sometimes you can get away without specifying the types, depending on the default behavior of the underlying JDBC Driver. The SQLParameter Business Object documents the possible inputs for the type.

I'll also note that the tw.local.sql variable should really be set using a "scriptlet" instead of writing JS code. It is much more readable that way. — Drux, Apr 03 '18 at 16:58

score 0 · Answer 2 · answered Apr 02 '18 at 12:44

0

Have you tried to decode whatever is coming within the parameters?

Please take a look at this post: HTML Entity Decode

answered Apr 02 '18 at 12:44

Luiz Duarte

131
2
3

how to assign the string variable in select query

2 Answers2