We have something similar to the following in the SQL that builds a test database for use with phpunit testing:
INSERT (101, 23, 'test1', 'something', 0x3C2F756C3E)
INSERT INTO `tableA` (`field1`, `field2`, `field3`, `field4`, `field5`)
VALUES
(102, 23, 'test2', 'something', X'3C2F756C3E');
To my surprise this actually runs when I run it as a query and it inserts </ul> into field5.
I have tried searching for some info on the ‘X’ part but haven’t found anything, I can see that there is something related to HTML entities here, but what is the ‘X"? How does this work?
>Solution :
The X signifies that the following is a hex-encoded binary string literal. It is defined in the ISO-9075-2:2016 SQL Standard as <binary string literal>:
<binary string literal> ::=
X <quote> [ <space>... ] [ { <hexit> [ <space>... ] <hexit> [ <space>... ] }... ] <quote>
[ { <separator> <quote> [ <space>... ] [ { <hexit> [ <space>... ]
<hexit> [ <space>... ] }... ] <quote> }... ]
<hexit> ::=
<digit> | A | B | C | D | E | F | a | b | c | d | e | f
The literal X'3C2F756C3E' when interpreted in ASCII or UTF-8 is:
- 3C ->
< - 2F ->
/ - 75 ->
u - 6C ->
l - 3E ->
>
or </ul>.
See also the MySQL documentation, section Hexadecimal Literals (note: the MySQL syntax is more restrictive than the standard syntax, that is, MySQL does not allow spaces between hexits, nor splitting into multiple quote delimited sections).