What is the 'X' in this insert > X'3C2F756C3E'-CodePudding

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?

CodePudding user response：

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).