1

I am using PHP 7, MySQL. I had been coding for my project a long time and it now has thousands of MySQL queries. I was not aware of prepared statements then.

Now, to avoid SQL injection, I want to use prepared statements but it is difficult for me to convert all of them one by one to prepared statement.

Is there any way I could parse a normal statement and convert it to prepared statement automatically using PHP? For every MySQL query, I pass it to a PHP function before passing to MySQL command.

public function dbquery($query,$dbname,$dbclose="-1")
{   
        $this->mysqli->select_db($dbname);
        $GLOBALS["dbr_total"]++;$GLOBALS["dbr_query"]++;
        $colarr=Array();$tares=Array();
    if ($result = $this->mysqli->query($query))
    {
        $GLOBALS["dbretry"]=0;
        $finfo = $result->fetch_fields();
        $c=0;
        foreach ($finfo as $val)
        {
            $colarr[$c]=$val->name;//get all colum names in this array
            $c++;
        }
        $co=0;
        while($obj = $result->fetch_object())
        {
            for($k=0;$k<count($colarr);$k++)
            {
                $elem=$colarr[$k];
                $tares[$co][$elem]=$obj->{$colarr[$k]};
            }
            $co++;
        }
        if($co==0)
        {
            $GLOBALS["dbretry"]=0;
            if($dbclose!="-1"){$this->dbclose();}
            return EMPTY_RESULT;
        }
    }
    else
    {
        if($GLOBALS["dbretry"]>3)
        {
            $GLOBALS["dbretry"]=0;
            $errmsg=$this->mysqli->error;
            $errno=$this->mysqli->errno;
            if($dbclose!="-1"){$this->dbclose();}
            $errobj=new ta_errorhandle();
            $errobj->senderror("OOPS! Could Not process your query!".$errmsg,$errno,"1");
        }
        else
        {
            $GLOBALS["dbretry"]++;
            $this->dbquery($query,$dbname);
        }
    }
        //QUERY DONE
        if($dbclose!="-1"){$this->dbclose();$result->close();}
        unset($obj);unset($finfo);unset($query);unset($result);unset($colarr);unset($c);unset($co);
        return $tares;
}




public function dbinsert($query,$dbname,$dbclose="-1")
{   
    $this->mysqli->select_db($dbname);
    $GLOBALS["dbr_total"]++;;$GLOBALS["dbr_insert"]++;
    if (!$this->mysqli->query($query))
    {
        $errmsg=$this->mysqli->error;
        $errno=$this->mysqli->errno;

        die("<br><br>".$errmsg."<br><br>".$errno);

        if($GLOBALS["dbretry"]>3)
        {
            $GLOBALS["dbretry"]=0;
            $logobj=new ta_logs();
            $logobj->store_templogs("PROBLEM EXECUTING QUERY:".$query." ON ".$dbname);
            return $this->mysqli;
        }
        else
        {
            $GLOBALS["dbretry"]++;
            $this->dbinsert($query,$dbname);
        }
    }
    else
    {
        $GLOBALS["dbretry"]=0;
    }
    if($dbclose!="-1"){$this->dbclose();}
    return SUCCESS;
}

Now what I do is call $dbobj->dbquery("my query","database name"); where $dbobj is an object for the class of these functions.

How do I convert these functions so that whatever query I receive as parameter is used and converted to prepared statements? I cant rewrite every query in my code. I have written more than 10,000+ queries already.

Vignesh T.V.
  • 1,790
  • 3
  • 27
  • 48
  • What "net gain" do you expect by this approach? Yes, your script could parse the sql statement ....just like the MySQL server can. What would be the advantage? – VolkerK Feb 03 '16 at 13:05
  • I want to prevent SQL injection but I am not in a position to rewrite all queries to prepared. So, I want to convert this function to parse incoming query and convert to prepared. And the reason I made this function when I started coding was cause I thought if mysql changed syntax or if I had to migrate to some other db, I can do it easily by changing this function. – Vignesh T.V. Feb 03 '16 at 13:07
  • If you can parse the query "sql-injection-safe" then what hinders the MySQL server in doing so? I.e. what kind of extra SmartLayer™ do you intend to implement? – VolkerK Feb 03 '16 at 13:11
  • Infact, I dont even know if the query can be parsed when attacker tries SQL injection. Or is there any other way to do this apart from using prepared statements? I want the same value that the user enters to be stored in database and same value to be retrieved but all this without SQL injection. – Vignesh T.V. Feb 03 '16 at 13:15
  • When the parameters are in the statement, the damage is potentially already done. If your code is supposed to parse arbitrary sql statements, I don't see (at least in this generic formulation) what new thing it brings to table. – VolkerK Feb 03 '16 at 13:21
  • I get your point.. I will try some other way.. I will get all incoming get,post and all requests and sanitize it then.. – Vignesh T.V. Feb 03 '16 at 13:29

1 Answers1

0

The problem is that you appear to pass the whole query as a string to your dbquery() method.

Prepared statements only protect against sql injection in case a value is passed as a parameter to the query, not if the value is already in the query.

Since your entire query is a single string, with no placeholders and no values passed separately, you cannot use the prepared statements to protect against sql injection without changing how you pass the queries and parameters to dbquery() method. Therefore, you need to rewrite your queries in case you want to take advantage of sql prevention feature of the prepared statements.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Thank you. I get your point.. I will try some other way.. I will get all incoming get,post and all requests and sanitize it then.. Btw, is there anyway to do this instead of using prepared statements? – Vignesh T.V. Feb 03 '16 at 13:30
  • Yes, there are alternatives. See http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php topic here on SO. Read beyond the accepted answer because it pretty much repeats the "use-prepared-statements" mantra, which I'm not a huge fan of. – Shadow Feb 03 '16 at 13:37