Add option to auto-detect and display binary column types in SQLLab and Table chart type

[atronchi]

I've seen lots of cases where a binary field of a table is used to store some common binary data type that is used in support of machine learning, analytics, etc. A few common examples are images, audio, videos, etc. What do folks here think about adding an option to auto-detect such common types in order to render them into the relevant row/column position of a table displayed from a SQLLab query result or a Table chart?

Browsers of course already render common image types, audio widgets, and certain video types natively so I'm hoping it wouldn't be a huge amount of dev to put something like this together, but I work mostly in python no javascript or on UI so maybe I'm naive...
Any suggestions on where to start if I wanted to try hacking something like this together?

[nytai]

This is already supported to some degree in table charts, and more recently in sqllab results (via #24368). A couple caveats:

• you need to construct the html in the query, eg SELECT format('<img src="data:image/png;base64,%s"\>', to_base64(img)) FROM ...
• Only certain types are supported (eg, images, links, audio/video without data urls). This is due to the html sanitization lib we're using filtering out certain attributes (https://github.com/leizongmin/js-xss/blob/66df7c48d5280de27b787169165afc4ea850cda4/lib/default.js#L187)

It would be fairly simple to override the sanitization and allow for more use cases, or allow admins to customize this to some extent.

However, if we want to get rid of the first req, i'm not sure how we could auto-detect the type since we only get back a generic binary type from most sqlalchemy interfaces. We could maybe make the first easier by providing some jinja helpers like {{to_png('img_col')}}

[atronchi]

Cool, thanks Tai! I was thinking to auto-detect using magic number similar to how the linux file command works. There's an existing node package to do this: https://www.npmjs.com/package/magic-number

var magic = require('magic-number');
// magic.detectType(data: any): string

[atronchi]

It's too bad that js-xss doesn't allow data:audio/ or data:video/. Is this principled on known vulnerabilities or just a surface minimizing approach? Does js-xss provide any way to allow other patterns than the defaults? The specific use case I had in mind was for audio and would require something like <audio controls src="data:audio/wav;base64,%s"/>

[nytai]

it does, you can simply supply your own safeAttrValue function to the constructor, but superset is currently using the default and doesn't provide a way to override.

I'm not sure if why this is the default though, I wouldn't think audio/video files are that different than image files in terms of risk but I could be wrong.