0

I usually handle db querys this way:

        if(!isset($macchineViewM)){error_log("MacchinaM::getSimiliMacchinaMArr macchineViewM is null");die("MacchinaM::getSimiliMacchinaMArr macchineViewM is null");}
        $returnList=array();
        $sql="SELECT m.* ,0";

        $sql.= !empty($this->funzioneM)? " +100*(f_id=".$this->funzioneM->id.")" : "";
        foreach ($this->prodottoMArr as $prodottoM) {
            $sql.=" +(find_in_set('".$prodottoM->id."',m.p_id_list) <> 0)";
        }
        $sql.=" AS score";

        $sql.=" FROM db_macchine_m AS m";
        $sql.=" WHERE m.id<>".$this->id;
        $sql.=empty($macchineViewM->filter_usato)?"":" AND 1=usato";
        $sql.=empty($macchineViewM->filter_nuovo)?"":" AND 1<>usato";
        $sql.=empty($macchineViewM->filter_language_en)?"":(" AND ".$macchineViewM->filter_language_en."_en=1");
        $sql.=" order by score desc LIMIT 9 ";

        trace($sql);
        $result=db::getInstance()->query($sql) ;
        while($row = $result->fetch_assoc()){
            $instance=  self::getInstanceFromViewRow($row);
            $returnList[] = $instance;
        }
        trace("returning ". count($returnList)." Macchine" ); 
        return $returnList;

I would like to use prepared statements because they are way more secure but i have a couble of blocking issues:

1) As you can see i have to "build" my sql programmatically: some sql blocks sometime are required , sometime are not. When i insert a specific block i can insert correct variables in place in a straightforward way. Doing the same thing with a prepared statement it would be a mess because the meaning of a ? placeholder differ every time.

2) I need to trace the resulting query in the application log: trace($sql); for debug purpose. It's easy with a string.. but with a prepared statement how to accomplish this?

I have no idea how to overcome those 2 issues

Dharman
  • 30,962
  • 25
  • 85
  • 135
Tara Rulez
  • 13
  • 1
  • 4
  • 2
    I believe this [post](https://stackoverflow.com/questions/62228814/how-to-make-a-fully-dynamic-prepared-statement-using-mysqli-api/62229798#62229798) has all the answers for the first question. And for the second.. well, there is no simple solution. you could log the query with placeholders along with the array with data. Mind you, logging every query is highly unusual. – Your Common Sense Jun 12 '20 at 18:02
  • Or you can check this [post](https://stackoverflow.com/q/210564/285587) for some solutions – Your Common Sense Jun 12 '20 at 18:06
  • Why do you need to trace the SQL? Why do you need to use mysqli? Can't you use PDO? This should be helpful https://github.com/paragonie/easydb – Dharman Jun 12 '20 at 19:07

0 Answers0