1. 

   maron, Member

   Hello.

   I have a report that can handle multiple actions with all parameters set to optional.

   I'm trying to use dbr.searchable - first - even if all the parameters are optional - I must include them all here (I do not need to do that for dbr.report) otherwise I get a Missing parameters error

   
   
    select 'dbr.searchable','sp_DBR_manage_sellers','Search name and description','inLogin=login','inAction="store.browse"', 'inInt1=store_id', 'inText2=""', 'inInt2=""', 'inDate1=""', 'inDate2=""';
   
   

   Second - once I've included all parameters I get the following

   Report security hash does not match. Report execution aborted

   It is quite obvious looking at the urls that the url of the report and the callback url for the searchable field are very different and have different hash ids.

   It is also not clear if the in_search parameter mentioned here: http://mydbr.com/doc/?cmd.linked.html#dbr.searchable is passed by name or somewhere in the parameter order - in my case passed as in_search would most likely be best.

   Best regards,

   Maron
2. 

   myDBR Team, Key Master

   How does your sp_DBR_manage_sellers-report look like? The search paramerter should be the first parameter in the report.

   You do not need to populate the automatic inLogin-parameter. myDBR will do it for you. If the login contains some other login than the users login, use another variable than the reserved inLogin.

   The reason dbr.searchable needs all the parameters, is that it does not have any other UI than the field user types in. The dbr.report on the other hand will ask the missing parameters when needed.

   What is the purpose of the extra parameters for dbr.searchable if you simply pass constants / empty string to it? Are you reusing some other report for the purpose?

   --
   myDBR Team
3. 

   maron, Member

   I only put inLogin there after multiple trial and error attempts of getting the report to work - but after rereading my request I see I miss 1 parameter - inText1 - this + putting in_search before inLogin in the procedure definition fixed the issue at hand.

   On the other questions I often prototype small "apps" around a specific context in a single stored_procedure with multiple ifs that determine the outcome based on an inAction paramter.

   This has worked well and allowed me to control everything in one place while prototyping. If I then find need to reuse or it gets descent use I will break them up into more stored_procedures.

   The parameters in these cases have to reflect the action requiring the most parameters - it would be great if there was a way to pass session variables (@inAction instead of inAction) into the procedure in a safe & secure manner through mydbr, this would greatly increase the flexibility.
4. 

   myDBR Team, Key Master

   it would be great if there was a way to pass session variables (@inAction instead of inAction)

   Not sure what you mean by this. If you are referring to MySQL user defined variables that hold the content throughout the connection, please remember that the connection to the database is open only when the page loads, not across the pageloads.

   --
   myDBR Team
5. 

   myDBR Team, Key Master

   it would be great if there was a way to pass session variables (@inAction instead of inAction)

   Not sure what you mean by this. If you are referring to MySQL user defined variables that hold the content throughout the connection, please remember that the connection to the database is open only when the page loads, not across the pageloads.

   --
   myDBR Team
6. 

   maron, Member

   Understood.

   Please read this with the reservation that I have not given this a thorough safety review.

   Since all requests go over the network, through php and therefore create a new connection / session - you could have parameters that are executed in php before the procedure is called - these then become available to the procedure.

   Here is some idea for this - note that this is not validated code, but some copy / paste / pseudo thought swere $args would be an array of key->value elements (json object via post maybe).

   At least this works - passing SET @test = 'Test contents'; via php database call and then calling the stored procedure gives us access to @test within the procedure.

   
     // some securit checks
   
     foreach ($args as $key => $value)
   
       {
   
         $value = addslashes($value);
   
         $value = strip_tags($value);
   
         $value = $this->db->real_escape_string($value);
   
         $key = addslashes($key);
   
         $key = preg_replace('/[^a-z\d_]/i', '', $key);
   
         $key = strip_tags($key);
   
         $key = str_replace(" ", "", $key);
   
         if( !is_numeric($value) ) {
   
           $value = "'" . $value . "'";
   
         }
   
       $this->connect_mysql();
   
     $query = "SET @" . $key . " = " . $value . ";";
   
        $r = $this->db->query($query) or die ("Incorrect parameters");
   
      $call = $this->db->prepare('CALL ' . $name . '()');
   
       if($call->execute() == false ) {
   
       die("Incorrect Parameters");
   
   

   I tried to make some security guesses above - this approach might have security holes - but no harm in throwing it into the discussion.

   This would allow us to decide to pass a variable into a procedure dynamically, without it having to be part of the procedure definition - very flexible, but has some type safety drawbacks.
7. 

   myDBR Team, Key Master

   Maron,
   what would be the benefit of this?

   If you are using the user defined variable in the procedure, why not just declare it as a parameter and use it as a proper parameter?

   The normal procedure parameters have advantages over user defined variable: they have defined types, procedures will be self contained etc.

   If the idea is to construct some kind of general purpose procedures that do different things based on the variable values, would recommend re-thinking the approach.

   --
   myDBR Team

Reply

You must log in to post.