Recently comitted rawAddPrefix() breaks queries

[killua-eu]

$query = 'UPDATE `t_fin_trx` SET `c_json` = JSON_SET(`c_json`, "$.id", `c_uid`) WHERE (NOT `c_uid` = c_json->>"$.id") or (NOT JSON_CONTAINS_PATH(c_json, "one", "$.id"));';
$this->db->rawQuery($query);

https://github.com/ThingEngineer/PHP-MySQLi-Database-Class/blob/db3f585c89c9afbd41c7a79acb772f268ddc8c70/MysqliDb.php#L559 will result in an Undefined offset: 0 notice. What's the intent of the function, @bejutassle ? Would you kinly look into fixing it?

[vermeerpaul]

Another issue - same result, but a quick fix could be to replace the function with below suggestion to bypass the problem. The function assumes that all queries are applied on tables and that the 'list' call has a result. However in your case you may want to look at the use of the ` in your statements - below regex fixes that issue too. Either way the $table variable is null (empty) in both cases. Slight performance improvement made to the function in case no prefix is being used.

BE AWARE ... This is not fully tested! So, use the applied changes to fit your needs.

function rawAddPrefix($query,$prefix){ if ($prefix == '') return $query; $query = str_replace(PHP_EOL, null, $query); $query = preg_replace('/\s+/', ' ', $query); preg_match_all("/(from|into|update|join) [\'\´\]?([a-zA-Z0-9_-]+)[\\'\\´\\]?/i", $query, $matches); list($from_table, $from, $table) = $matches; if ($table == null) return $query; return str_replace($table[0], $prefix.$table[0], $query); }

[vermeerpaul]

Grrr ... I noticed that my test functions are a bit muddled up by github's editor. Here's the 'what to do to fix it' and use it in the class.

insert this line to skip the regex function when there's no prefix if (self::$prefix == '') return $query;

insert this line after the 'list' function to check if there were any table names to prefix or not to prevent the $table[0] error if ($table == null) return $query;

Change the REGEX to include your backtick [sets to now include; single quote, tick and backtick] use ..... backslash backslash backtick .... to do so and apply it to BOTH sets.

Hope it helps

[RyadPasha]

The rawAddPrefix function has many bugs, first thing is that only the first table in the query gets prefixed, (so if using query with a JOIN clause for example only first table will get prefixed), also a lot of statements such as (DROP TABLE, TRUNCATE TABLE, CREATE TABLE, LOCK TABLE, FLASHBACK TABLE, ALTER TABLE, ANALYZE TABLE, DESCRIBE and EXPLAIN) are not supported .. You can fix all these bug by replacing that function with mines:

/**
 * Prefix add raw SQL query.
 *
 * @author Mohamed Riyad <https://github.com/RyadPasha>
 * @param string $query User-provided query to execute.
 * @return string Contains the returned rows from the query.
 */
public function rawAddPrefix($query){
    $query = preg_replace(['/[\r\n]+/', '/\s+/'], ' ', $query); // Replace multiple line breaks/spaces with a single space
    if (preg_match_all("/(FROM|INTO|UPDATE|JOIN|DROP TABLE|TRUNCATE TABLE|CREATE TABLE|LOCK TABLE|FLASHBACK TABLE|ALTER TABLE|ANALYZE TABLE|DESCRIBE|EXPLAIN) [\\'\\´\\`]?(?!SELECT|DELETE|INSERT|REPLACE|UPDATE)([a-zA-Z0-9_-]+)[\\'\\´\\`]?/i", $query, $matches)) {
        for ($i = 0; $i < count($matches[0]); $i++) {
            list($from_table, $from, $table) = $matches;
            $query = str_replace($table[$i], self::$prefix.$table[$i], $query);
        }
    }
    return $query;
}