Why does FILTER_SANITIZE_STRING remove part of the SQL string?-CodePudding

I have a class that handles my database queries Select. The method Select::Assoc takes 3 parameters: an instance of my logging class, an array of query info, and a mysqli database object.

I've recently started moving the project toward a more object oriented approach, and created a new DBParameters object that can Set the SQL, add to the SQL, set the bind string for mysqli, and set the bind vars. It has been working great and works fine everywhere else on the project. It has a function GetParameters that simply returns an array of the relevant object properties. (Eventually I will be passing the whole object to Select)

In one particular function of a class, which the class successfully uses the DBParameters class elsewhere, when I use the SetSQL method to set the SQL to

$p->SetSQL("select period_start, period_end, period_id
-- 1
                    from cert_periods
-- 2
                    where period_id <= ?
-- 3                          
                    and period_id >= ?
-- 4
                    order by period_id desc
-- 5                    ");

and then immediately dump the variable to the log I get the following:

2021-09-24 11:53:34 WARNING V3 USER:APIuser array(3) {
  ["sql"]=>
  string(204) "select period_start, period_end, period_id
-- 1
                    from cert_periods
-- 2
                    where period_id = ?
-- 4
                    order by period_id desc
-- 5                    "
  ["affected"]=>
  int(0)
  ["bind"]=>
  bool(false)
}

The Code snippet doing this is as follows:

$p = new DBParameters();
    $p->SetSQL("select period_start, period_end, period_id
-- 1
                    from cert_periods
-- 2
                    where period_id <= ?
-- 3                          
                    and period_id >= ?
-- 4
                    order by period_id desc
-- 5                    ");
        $log->dump($p->GetParameters(), 3); // debug

the DBParameters->SetSQL method is as follows:

/*
     * @desc Sets the sql string
     * @since 3.0.0
     *
     * @param string $string A full SQL string
     */
    public function SetSQL($sql) {
        $this->sql = filter_var($sql, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);
    }

Can anyone offer any reason the SQL variable would stop half way through, go get some SQL from somewhere else, and then resume with the correct SQL? My brain is exploding trying to figure out how this is even possible.

CodePudding user response：

You are explicitely removing all strings between < and > when you pass it through your filter. As to why you are doing this and expect different results, I am unsure.

public function SetSQL($sql) {
    $this->sql = filter_var($sql, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);
}

Maybe you are confused about the name of FILTER_SANITIZE_STRING. This filter removes all substrings between < and > (or the end of the whole string) including the brackets. It doesn't encode quotes as you have disabled that with a flag.

To fix this, simply remove that filter altogether. This will do what you want:

public function SetSQL($sql) {
    $this->sql = $sql;
}

By the way, constant FILTER_SANITIZE_STRING is deprecated. Please stop using it.