handling html escaping with sqlite's FTS snippet function

Question

I'm using sqlite's full-text-search support to store documents that may contain &, < and > characters. I intended to use the snippet function to highlight the matches for an html results page, but I don't see an obvious way to escape the text before injecting the markers. I'd rather not escape the text before storing it, because then amp, lt and gt will become tokens.

The simplest workaround I can think of is to escape the pages before storing them, and use a custom tokenizer that would first unescape &, < and >.

However... since the default for snippet(foo) is to use <b> and </b>, it really seems like this is a very common use case and I'm convinced that there must be a way to handle this without reinventing the wheel in C. Am I overlooking a more elegant solution?

CL. · Accepted Answer · 2012-11-22 09:29:48Z

1

It appears that FTS indeed does not handle markup in text (note how the <p> gets chopped up):

> CREATE VIRTUAL TABLE test USING fts3(content TEXT);
> INSERT INTO test VALUES('<p>Isn''t this <font face="Comic Sans">funny</font>?');
> INSERT INTO test VALUES('blah');
> SELECT snippet(test) FROM test WHERE content MATCH 'funny';
p>Isn't this <font face="Comic Sans"><b>funny</b></font>?
> SELECT snippet(test) FROM test WHERE content MATCH 'font';
p>Isn't this <<b>font</b> face="Comic Sans">funny</<b>font</b>>?

The cleanest way to store the text is as plain text, unescaped. However, to get proper highlighting of search results, you have two choices:

Use the optional parameters of snippet to specify markers that are guaranteed to never occur in the text (which might not be possible), and convert those to <b>... when you are escaping the text for HTML; or
use the offsets function instead and insert the markers by hand.

answered Nov 22, 2012 at 9:29

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

strcat Over a year ago

Thanks! The text I'm storing is ReStructured Text (and it's all validated already), so I just ended up using markers that get rejected by the docutils rst parser. Not pretty, but it works well.

Collectives™ on Stack Overflow

handling html escaping with sqlite's FTS snippet function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related