MySQL EXTRACTVALUE not working with special characters

[alexander-schranz]

Currently the XML Property names are written the following way into MySQL:

<sv:property sv:name="val&quot;ue">

another valid xml struct for this would be

<sv:property sv:name='val"ue'>

As mysql itself seems not convert " to " it currently can not match in the query. Example the following SQL will not work in both cases:

SET @xmlPointer = '//sv:property[@sv:name=concat("val", concat(''"'', "ue"))]/sv:value[1]';
SELECT EXTRACTVALUE('<sv:property sv:name=\'val"ue\'><sv:value length="3">BBB</sv:value></sv:property>', @xmlPointer); // works
SELECT EXTRACTVALUE('<sv:property sv:name="val&quot;ue"><sv:value length="3">BBB</sv:value></sv:property>', @xmlPointer); // doesnt work

Currently a test testing a property named with val"ue" is skipped because of this.

jackalope-doctrine-dbal/tests/Jackalope/Transport/DoctrineDBAL/ClientTest.php

Lines 541 to 543 in f3107ac

	[MySQLPlatform::class]
	// see https://stackoverflow.com/questions/70339679/use-extractvalue-against-correctly-escaped-xml-attribute-value-in-mysql
	// currently mysql does not escape 'val"ue' the same was as "val"ue" so the test fails

I also created a stackoverflow question how this could maybe be solved: https://stackoverflow.com/questions/70339679/use-extractvalue-against-correctly-escaped-xml-attribute-value-in-mysql

[dbu]

could we convert " to " in the query for mysql, or does that have other side effects?

[alexander-schranz]

@dbu I'm afraid currently doing that, because maybe it will behave differently in MySQL 8 or in future MySQL versions.

[dbu]

in the mid-term i hope to release a version 2 of the jackalope ecosystem. mostly with cleanups like parameter and return type declarations and other such modern PHP improvements.

that might be a good opportunity to do BC breaking changes. do you think we could do something here to make things work reliably?

[alexander-schranz]

We didn't yet stumble over it in a project, and it was never reported to use yet, so this issue isnt important for us at current state. I did just stumble over it while writing the tests for the sql injection issue that mysql behave here strange. But if it is easy possible to write 'val"ue' instead of "val"ue", not sure if there is any flag in XML lib to achieve that, it would be a good solution for the 2.0 then. But as said it is not a important issue on our side yet. We didn't had yet any usecase to filter something with a double quote in it yet.

[dbu]

thanks. yeah lets keep it open in case somebody runs into problems because of this.