PDOException when url contains invalid UTF8 string

[gijsstegehuis]

Describe the bug

When the url path contains invalid characters it will throw an PDOException.

Uncaught PDOException: SQLSTATE[22021]: Character not in repertoire: 7 ERROR:  invalid byte sequence for encoding "UTF8": 0xeb 0x6e 0x74
CONTEXT:  unnamed portal parameter $3 in /app/vendor/yiisoft/yii2/db/Command.php:1302

To reproduce

Steps to reproduce the behaviour:
Visit a path like: about/foo%EBbar without having a redirect in place.

Expected behaviour

A regular 404 page.

Screenshots

If applicable, add screenshots to help explain your problem.

Versions

• Plugin version: 3.2.13
• Craft version: Pro 3.9.10
• PostgreSQL 14.10

Stack trace

Uncaught PDOException: SQLSTATE[22021]: Character not in repertoire: 7 ERROR:  invalid byte sequence for encoding "UTF8": 0xeb 0x6e 0x74
CONTEXT:  unnamed portal parameter $3 in /app/vendor/yiisoft/yii2/db/Command.php:1302
Stack trace:
#0 /app/vendor/yiisoft/yii2/db/Command.php(1302): PDOStatement->execute()
#1 /app/vendor/yiisoft/yii2/db/Command.php(1168): yii\db\Command->internalExecute()
#2 /app/vendor/yiisoft/yii2/db/Command.php(424): yii\db\Command->queryInternal()
#3 /app/vendor/yiisoft/yii2/db/Query.php(287): yii\db\Command->queryOne()
#4 /app/vendor/craftcms/cms/src/db/Query.php(167): yii\db\Query->one()
#5 /app/vendor/nystudio107/craft-retour/src/services/Redirects.php(729): craft\db\Query->one()
#6 /app/vendor/nystudio107/craft-retour/src/services/Redirects.php(387): nystudio107\retour\services\Redirects->getStaticRedirect()
#7 /app/vendor/nystudio107/craft-retour/src/services/Redirects.php(238): nystudio107\retour\services\Redirects->findRedirectMatch()
#8 /app/vendor/nystudio107/craft-retour/src/Retour.php(604): nystudio107\retour\services\Redirects->handle404()
#9 [internal function]: nystudio107\retour\Retour->nystudio107\retour\{closure}()
#10 /app/vendor/yiisoft/yii2/base/Event.php(312): call_user_func()
#11 /app/vendor/yiisoft/yii2/base/Component.php(642): yii\base\Event::trigger()
#12 /app/vendor/craftcms/cms/src/web/ErrorHandler.php(49): yii\base\Component->trigger()
#13 [internal function]: craft\web\ErrorHandler->handleException()
#14 {main}

Next yii\db\Exception: SQLSTATE[22021]: Character not in repertoire: 7 ERROR:  invalid byte sequence for encoding "UTF8": 0xeb 0x6e 0x74
CONTEXT:  unnamed portal parameter $3
The SQL being executed was: SELECT *
FROM "retour_static_redirects"
WHERE ("redirectMatchType"='exactmatch') AND ((("redirectSrcMatch"='pathonly') AND ("redirectSrcUrlParsed"='/over-ons/organisatie/cli�ntenraad')) OR (("redirectSrcMatch"='fullurl') AND ("redirectSrcUrlParsed"='https://www.foo.bar/over-ons/organisatie/cli�ntenraad'))) AND (("siteId"=1) OR ("siteId" IS NULL)) AND ("enabled"=1)

[khalwat]

Are you sure this isn't some kind of an issue with your Postgres db setup? I'm unable to reproduce this with Retour connected to a Postgres db

[gijsstegehuis]

The Postgres db server_encoding is set to UTF8, so i can't imagine it is.
I was able to reproduce this locally by using the postgres 14 docker image

[khalwat]

So initially, I thought this was a lack of sanitization on the data being saved to the statistics, but that's already in place:

https://github.com/nystudio107/craft-retour/blob/develop-v4/src/helpers/Text.php#L85

Any of the user-provided strings are run through this method to clean up the text as a process of the model validation already.

What's actually happening is it's using the user-provided string (improperly encoded in your case) as a query parameter when looking up redirects, and probably statistics as well.

So what we'll do is ensure any of the parameters passed in to the various queries are also similarly sanitized to guard against this happening.

[khalwat]

Fixed in the above commits.

Craft CMS 3:

You can try it now by setting your semver in your composer.json to look like this:

    "nystudio107/craft-retour": "dev-develop as 3.2.14”,

Then do a composer clear-cache && composer update

…..

Craft CMS 4:

You can try it now by setting your semver in your composer.json to look like this:

    "nystudio107/craft-retour": "dev-develop-v4 as 4.1.16”,

Then do a composer clear-cache && composer update

…..

Craft CMS 5:

You can try it now by setting your semver in your composer.json to look like this:

    "nystudio107/craft-seomatic": "dev-develop-v5 as 5.0.0-beta.5”,

Then do a composer clear-cache && composer update

[gijsstegehuis]

Tested. Works 👍🏼
Good work Andrew!