IBM i Global

 View Only

  • 1.  qsys2.http_post and sql plan cache

    Posted Mon December 04, 2023 03:53 PM

    When using qsys2.http_post I noticed that the SQL plan cache will show the statement along with the parameter values. Is it possible to prevent sensitive data from being included in the SQL plan cache when using qsys2.http_post?



    ------------------------------
    David Hammond
    ------------------------------


  • 2.  RE: qsys2.http_post and sql plan cache

    IBM Champion
    Posted Tue December 05, 2023 05:06 AM
    Edited by Satid Singkorapoom Tue December 05, 2023 05:15 AM

    Dear David

    Did you pass the URL text string directly in HTTP_POST?   If so, please try assigning the URL string to a variable first and use the variable in HTTP_POST instead as Plan Cache will NOT keep the actual value of any variables (parameters) but keep them as symbol "?" (Parameter Marker) instead.  Look at sample here :  https://blog.faq400.com/en/db2-for-i/qsys2-http-functions-en/    

    Sample:

    -- URL ... with my APi-KEY

    set faq400.GV_URL='https://translation.googleapis.com/language/translate/v2?key=' concat faq400.GV_GoogleAPIKey;

    -- Body ... with my words

    set faq400.GV_BODY= (
    select   json_object('q' value JSON_ARRAYAGG( nameITA),
                       'source' value 'it',
                       'target' value 'en',
                       'format' value 'text') as "BodyMsg"
                       from qtemp.FruitNames ) ;

    -- Let's create a new Header, JSON with a special option "sslTolerate"="true" if we don't need to check SSL Certificates

    set faq400.GV_HEADERJSON= 
    json_object('header' value 'content-type,application/json',
                'header' value 'content-length, ' concat length(trim(faq400.GV_BODY)),
                'sslTolerate' value 'true'
                       );

    -- Now we can try QSYS2.HTTP_POST (pay attention to parameter's sequence, not the same!)
    select QSYS2.HTTP_POST(faq400.GV_URL,
                   cast(faq400.GV_BODY   as clob(10k)), 
                  cast(faq400.GV_HEADERJSON as clob(1k)) )
           from sysibm.sysdummy1;



    ------------------------------
    Chance favors only the prepared mind.
    -- Louis Pasteur
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 3.  RE: qsys2.http_post and sql plan cache

    Posted Tue December 05, 2023 05:15 AM

    Satid,

    Use of Global Variables won't prevent the values from being accessed in Visual Explain.  While the Plan Cache statement text may have the parameter marker (?), the Final Select icon in Visual Explain with display the value of the global variable.  



    ------------------------------
    Kent Milligan
    ------------------------------

    Original Message


  • 4.  RE: qsys2.http_post and sql plan cache

    Posted Tue December 05, 2023 05:06 AM

    If the parameters being passed to HTTP_POST are columns, then you can use the SET_COLUMN_ATTRIBUTE service in QSYS2 to mark the column as SECURED.

    BTW, there is Db2 for i SQL community at: ibm.biz/SQLonIBMi



    ------------------------------
    Kent Milligan
    ------------------------------

    Original Message


  • 5.  RE: qsys2.http_post and sql plan cache

    Posted Wed December 06, 2023 06:48 AM

    Thanks for the idea, but in this case the goal was to use a web service to tokenize the sensitive parameter before storing the token in the database. 



    ------------------------------
    David Hammond
    ------------------------------

    Original Message