如何在字符串中的特殊字符前后使用附加字符扩展字符串

A field on my form contains a string which is the formula for a math function. With this string I take data from database.

But the problem is, if some of this string in the database is NULL then my formula does not work.

Example of formula is

amount_injuries+amount_employment+health_insurance

This formula is PHP part in MySQL she looks like

SELECT SUM(amount_injuries+amount_employment+health_insurance) FROM ... !

The result which I want is

select sum((COALESCE(amount_injuries,0)+(COALESCE(amount_employment,0))+(COALESCE(health_insurance,0)))) from ...

How can I add the COALESCE part to the existing string in PHP?

If I understand correctly, the different parts of the string from your form (amount_injuries, etc.) are column identifiers that you're using in a math expression in your query.

There are a few different ways to do this. The simplest way is to use a regular expression replacement on anything that is a valid column identifier.

$coalesce = preg_replace('/(\\w+)/', '(COALESCE(\\1,0))', $string_from_form);

There are problems with this, though. Concatenating a user-supplied string into your SQL statement, is an injection vulnerability. This also assumes that all the columns have been entered correctly, are valid column names without backticks, and that only valid math operators have been used.

A better way would be to split the string on valid math operators (using PREG_SPLIT_DELIM_CAPTURE in order to capture the operators as well) and then validate the columns given against a list of acceptable columns as you apply the COALESCE modification.

$columns = preg_split('|([+-/*])|', $string_from_form, null, PREG_SPLIT_DELIM_CAPTURE);

$accepted_columns = ['amount_injuries', 'amount_employment', 'health_insurance'];

$columns = array_map(function($x) use ($accepted_columns) {
    if (in_array($x, ['+', '-', '/', '*'])) {
        return $x;
    } else {
        // validate the column; stop if column is invalid
        if (in_array($x, $accepted_columns)) {
            return sprintf('(COALESCE(`%s`,0))', $x);
        } else {
            // handle error
        }
    }
}, $columns);

$columns = implode('', $columns);

An approach like this should be able to handle most simple math expressions. It's still very naive, though; it doesn't do much to determine whether the expression provided is valid mathematically, or that it can be handled by MySQL, it doesn't handle parentheses, etc. To go farther with it you'd need to look into math expression parsers, and I'm not familiar enough with any to recommend one.