It's actually not true. CNN's don't have to have a fixed-size input. It is possible to build CNN architectures that can handle variable-length inputs. Most standard CNNs are designed for a fixed-size input, because they contain elements of their architecture that don't generalize well to other sizes, but this is not inherent.
For example, standard CNN architectures often use many convolutional layers followed by a few fully connected layers. The fully connected layer requires a fixed-length input; if you trained a fully connected layer on inputs of size 100, and then there's no obvious way to handle an input of size 200, because you only have weights for 100 inputs and it's not clear what weights to use for 200 inputs.
That said, the convolutional layers themselves can be used on variable-length inputs. A convolutional layer has a convolutional kernel of fixed size (say, 3x3) that is applied to the entire input image. The training process learns this kernel; the weights you learn determine the kernel. Once you've learned the kernel, it can be used on an image of any size. So the convolutional layers can adapt to arbitrary-sized inputs. It's when you follow a convolutional layer with a fully connected layer that you get into trouble with variable-size inputs.
You might be wondering, if we used a fully convolutional network (i.e., only convolutional layers and nothing else), could we then handle variable-length inputs? Unfortunately, it's not quite that easy. We typically need to produce a fixed-length output (e.g., one output per class). So, we will need some layer somewhere that maps a variable-length input to a fixed-length output.
Fortunately, there are methods in the literature for doing that. Thus, it is possible to build networks that can handle variable-length inputs. For instance, you can train and test on images of multiple sizes; or train on images of one size and test on images of another size. For more information on those architectures, see e.g., https://stackoverflow.com/q/36262860/781723, https://stats.stackexchange.com/q/250258/2921, https://stackoverflow.com/q/57421842/781723, https://stackoverflow.com/q/53841509/781723, https://stackoverflow.com/q/53114882/781723, https://docs.fast.ai/layers.html#AdaptiveConcatPool2d, and so on.
That said, these methods are not yet as widely used as they could be. Many common neural network architectures don't use these methods, perhaps because it is easier to resize images to a fixed size and not worry about this, or perhaps because of historical inertia.