cancel
Showing results for 
Search instead for 
Did you mean: 

Query Template restricted to 32 Params

Former Member
0 Kudos

Hi All,

I have more than 50 Params on Screen to be inserted into database through MII

Solution 1: I can send this as one param by selecting all the variables in one variable (through dynamic SQL Query)

but this is not good practice as it is is not secured due to SQL injection

Solution2 : I can have two tables and distribute the variables from the screen in these two tables (as Query templates restrict me to have 32 Params)

Can anyone suggest me a better solution keeping in mind security and Best Practices

Thanks in advance

Regards

Namita

Accepted Solutions (1)

Accepted Solutions (1)

sidnooradarsh
Contributor
0 Kudos

Hi Namitha,

If you still don't want to use dynamic sql in MSSQL server then

please refer this link where in an example is suggested for splitting an appended string.

Its similar to what I have suggested in Oracle in my earlier post.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23402223.html

Hope this helps as well!!

Regards,

Adarsh S P

Answers (4)

Answers (4)

sidnooradarsh
Contributor
0 Kudos

Hi Namitha,

1) If you are using MSSQL server then you can still go for Dynamic SQL but instead of using your Dynamic SQL statement directly in Query Template Write a Stored procedure and inside procedure write the Dynamic SQL statement passing this as string parameter. And call this stored procedure from Query template.

Since Stored Procedure is stored at server and hence not visible or in-accessible to anyone or end user

So chances of SQL injection is minimal or no chance.

2) But If you are using Oracle then you can do something like this:-

Assuming that you are not sure of how many rows you need to insert. It comes on fly from front-end.

Say for example, you need to insert two rows, each row having 50 or more fields(columns).

In JavaScript write a small routine in which you append each field separated by comma( , ) or any character you feel convenient and then prepare the second row in same way and then append it with first row separated by semicolon( ; ) or any character you feel convenient.

follow same approach for any number of rows using loops.

So you will get just one Parameter string carrying all the rows and fields.

Pass this to a stored procedure thru query template.

and perform this logic

char_position := instr(STR,',');

field_value :=to_number(substr(STR,1,char_position-1));

STR := substr(STR,char_position+length(','));

Use the above code for un-stringing or to get the rows appended.

then use the above same code within each row and loop untill you finish un-stringing all the rows.

where STR is your appended string passed from front-end.

Hope this helps!!

Regards,

Adarsh S P

Former Member
0 Kudos

Hi Namita,

we're facing the same problem. I've the following things in mind:

1. do first an insert statement with the first 32 parameters, remember the key of the data row and then perform an update statement on this row to set the remaining parameters. You may also set a status flag after successful insertion if other Jobs or systems query data from this table.

2. We've tried to store KPIs as XMLTYPE in the Oracle Database, but this gets quite complicated, since you have to extract the values using Oracles EXTRACTVALUE() in combination with the XPath to the corresponding node in the XML Structure. Furthermore, you get problems when you pass an XML that contains more than 4000 characters, since string literals in an (Oracle) SQL statement are limited by this length.

3. group your values into logical independent subgroups and use seperate tables.

Kind Regards,

Matthias

0 Kudos

Check out the link Manisha provided first. The basic concept is that you can dynamically paste an SQL Script to the Query property of the SQL Query action block. You can bypass the Parameters completely and build your entire query dynamically. It is a bit more difficult to maintain, but gives more flexibility.

You can dynamically build your script in pieces or all at once. A simple script without using parameters might look something like this:

"Select * from Orders where OrderNum between " & singlequote & Transaction.LowOrderNumber & singlequote & " and " & singlequote & Transaction.HighOrderNumber & singlequote & " and OrderCreateDate between "

& singlequote & Transaction.StartDate & singlequote & " and " & singlequote & Transaction.EndDate & singlequote & " and Material like " & singlequote & Transaction.MaterialStub & "%" & singlequote

Evaluate and it should end up as (assuming I got all the syntax correct):

Select * from Orders where OrderNum between '000007000180' and '000007000285' and OrderCreateDate between '08/01/2008 00:00:00' and '08/05/2008 12:35:28' and Material like '0000000001800%'

Good luck,

Mike

Edited by: Michael Appleby on Aug 6, 2008 1:19 PM

Former Member
0 Kudos

Hi Namitha,

I guess there is no security issue here. because preparing a single query statement using these variables , setting query to query template is same as setting param values to pre existing query in query template.

If there is any security concern here, please let me know because I used that method in my application at various places.

Regards,

Veeresh.

Former Member
0 Kudos

Hi Namita,

You can go through this thread:

https://forums.sdn.sap.com/click.jspa?searchID=14894870&messageID=4837093

Hope this may help you.

Thanks,

Manisha