[SOLVED] Avoiding html entities in .mode html

Avoiding html entities in .mode html

I'm trying to use .mode html in sqlite3 to create an html table. I want to create a link that looks like <a href="http://assets.example.com/hardware/600">EXAMPLE05593</a> as the last column in the table.

I've got an sqlite database that looks like this:

CREATE TABLE assets(
    "model_name" TEXT,
    "model_number" TEXT,
    "manufacturer" TEXT,
    "asset_tag" TEXT,
    "id" TEXT                                                                                                          
);

Let's say that my table looks like this:

model_name  model_number  manufacturer  asset_tag     id                                                          
----------  ------------  ------------  ------------  ----------                                                  
Meraki Z1   Z1            Cisco         EXAMPLE05108  115                                                         
us-24-500w  NULL          ubiquiti      EXAMPLE05348  355

My query looks like this:

select
    model_name,
    model_number,
    manufacturer,
    '<a href="http://assets.example.com/hardware/'
        || id
        || '">'
        || asset_tag
        || '</a>'
    as asset_tag
from assets;

When I set .mode column, I get this:

model_name  model_number  manufacturer  asset_tag
----------  ------------  ------------  -----------------------------------------------------------------
Meraki Z1   Z1            Cisco         <a href="http://assets.example.com/hardware/115">EXAMPLE05108</a>
us-24-500w                ubiquiti      <a href="http://assets.example.com/hardware/355">EXAMPLE05348</a>

when I run it in .mode html, quotes, < and > are all replaced with html entities, so I'm getting

<TR><TH>model_name</TH>
<TH>model_number</TH>
<TH>manufacturer</TH>
<TH>asset_tag</TH>
</TR>
<TR><TD>Meraki Z1</TD>
<TD>Z1</TD>
<TD>Cisco</TD>
<TD>&lt;a href=&quot;http://assets.example.com/hardware/115&quot;&gt;EXAMPLE05108&lt;/a&gt;</TD>
</TR>
<TR><TD>us-24-500w</TD>
<TD></TD>
<TD>ubiquiti</TD>
<TD>&lt;a href=&quot;http://assets.example.com/hardware/355&quot;&gt;EXAMPLE05348&lt;/a&gt;</TD>

rather than

<TR><TH>model_name</TH>
<TH>model_number</TH>
<TH>manufacturer</TH>
<TH>asset_tag</TH>
</TR>
<TR><TD>Meraki Z1</TD>
<TD>Z1</TD>
<TD>Cisco</TD>
<TD><a href="http://assets.example.com/hardware/115">EXAMPLE05108</a></TD>                                        </TR>
<TR><TD>us-24-500w</TD>
<TD></TD>
<TD>ubiquiti</TD>
<TD><a href="http://assets.example.com/hardware/355">EXAMPLE05348</a></TD>                                        </TR>

Ordinarily, this would be the right thing, but I want to do html injection. I don't want those characters replaced with their respective entities. Is there a way to specify literal text in .mode html?

Solution

sqlite3 doesn't have the option to disable escaping when using .html mode because then it can't guarantee valid HTML output. If the content you are pulling from sqlite is meant for browser consumption then probably JSON might be a more appropriate output format, because then you can, for example, use javascript to dynamically build your table from the json object. For JSON output you might need to switch to the latest version of sqlite3 because previous versions didn't have it.

Get selected columns from table mytable as JSON:

Using JSON output as an alternative

sqlite3 mydata.db ".output mydata.json" ".mode json"  "SELECT col1, col3, col4 FROM mytable;" ".exit"