Using this quirk allows for "hiding" data in the database. Because data after the nul is more-or-less invisible to generic dbBrowser type programs.

If you suspect it is happening you can read it (by casting the SELECT as a BLOB, but obviously that's not a common pattern.

Personally I've never done it, and clearly it's not something useful for security, but it does open the door to interesting meta-data storage opportunities. Again with the proviso that it is "untrustworthy".