Several plugins broken inserting/updating strings in SQL

[GlueGuy]

It seems that something has changed with (at least) the sql_sanitize function in the lib/database.php library.

Any plugin that uses the sql_sanitize function gets strings munged when they are inserted or updated in the SQL database.

If the string is entered plain, it will get extra single quotes around the string ( string becomes 'string' ).

If the string already has the single quote, it will get escaped ( 'string' becomes '\\\'string\\\'' ).

I've compiled a partial list of all the plugins that are affected:

• aggregate

• autom8

• discovery

• fix64bit

• nectar

• routerconfigs

As far as I can tell, none of the base cacti functionality uses sql_sanitize, and only "some" of the plugins use it. Of the plugins that I have looked at, the ones that do NOT use sql_sanitize appear to be OK.

[happyelf]

I think the syslog plugin also affected by this.

When I try to set syslog alert rules with SQL expression, I just cannot type like this ==> message LIKE '%xxx%'
After saving, it will become ==> message LIKE \'%xxx%\'

[GlueGuy]

I think the problem is the way the components (CentOS, php, and mysql) have changed in later revisions.

On my system (CentOS 6.7, php 5.3.3, and mysql 5.1.73), it turns out that the $_POST automatically fixes the outer quotes. So I just removed sql_sanitize() everywhere it occurred. Unfortunately, that broke embedded single quotes (and other characters) that were within a text field (e.g. ' became \' ).

What I discovered turned out to be relatively simple. I just replaced "sql_sanitize()" with "stripslashes()" wherever it occurred. For example, the following line

Code: Select all

$save['name']                   = sql_sanitize(form_input_validate($_POST['name'], 'name', '', false, 3));

became

Code: Select all

$save['name']                   = stripslashes(form_input_validate($_POST['name'], 'name', '', false, 3));

stripslashes is a built-in php function.