Issue Bug Closed

#82807dbal CAST AS CHAR added to FIND_IN_SET cuts values

Jan Mayer 10/18/17, 23:15 updated 10/18/17, 23:29

Description

Using “Usergroup Access Rights” for limiting access rights to specific usergroups creates queries with “FIND_IN_SET”. For postgresql (or more?), a CAST is added like so:

CAST

This truncates the string to the first char only – thus, if you have multiple usergroups or groups with ids > 9 in pages.fe_group, e.g. “16,17”, this gets truncated to “1” – for which FIND_IN_SET subsequently fails.

Possible solution: Use VARCHAR instead, e.g. in sysext/dbal/Database/DatabaseConnection.php L1792 and L1794. This might also effect more functions at different locations.

Full example query:

SELECT *
FROM “pages“
WHERE “uid” = 78
AND “pages”.“deleted” = 0
AND “pages”.“t3ver_state” <= 0
AND “pages”.“hidden” = 0
AND “pages”.“starttime” <= 1508357760
AND (“pages”.“endtime” = 0 OR “pages”.“endtime” > 1508357760)
AND “pages”.“doktype” < 200
AND (
“pages”.“fe_group” = ‘‘
OR “pages”.“fe_group” IS NULL
OR “pages”.“fe_group” = ’0’
OR FIND_IN_SET(‘0’, CAST) != 0
OR FIND_IN_SET(‘-2’, CAST) != 0
OR FIND_IN_SET(‘16’, CAST) != 0
)

(Took me half a day)

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.