Skip to Content
avatar image
Former Member

Query Template restricted to 32 Params

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

5 Answers

  • Best Answer
    avatar image
    Former Member
    Aug 06, 2008 at 03:59 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 06, 2008 at 10:43 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 06, 2008 at 10:44 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 06, 2008 at 10:45 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Aug 06, 2008 at 03:46 PM

    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

    Add comment
    10|10000 characters needed characters exceeded