如何为SQLite FTS查询转义字符串

本文介绍了如何为SQLite FTS查询转义字符串的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我试图用不可信用户输入执行SQLite FTS查询。我不想让用户访问查询语法，也就是说他们将无法执行匹配查询，如 foo或bar and cats 。如果他们试图用该字符串查询，我想将它解释为更像 foo \OR bar \and cats 。

似乎没有为SQLite内置任何内容，所以我最终可能会构建自己的转义函数，但这似乎很危险且容易出错。有没有一种首选的方式来做到这一点？

解决方案

好的，我已经进一步调查了， SQLite的FTS使用的实际标记器。 简单标记器会将您的字符串分隔到不在[A-Za-z0-0]中的任何字符，并将其余部分降低。如果您执行相同的操作，您将获得适用于FTS的很好的转义字符串。

您可以编写自己的，但也可以访问SQLite的内部字符串。有关详情，请参阅此问题的详细信息：使用SQLite FTS4的自动OR查询 p>

I'm trying to perform a SQLite FTS query with untrusted user input. I do not want to give the user access to the query syntax, that is they will not be able to perform a match query like foo OR bar AND cats. If they tried to query with that string I would want to interpret it as something more like foo \OR bar \AND cats.

There doesn't seem to be anything built in to SQLite for this, so I'll probably end up building my own escaping function, but this seems dangerous and error-prone. Is there a preferred way to do this?

解决方案

OK I've investigated further, and with some heavy magic you can access the actual tokenizer used by SQLite's FTS. The "simple" tokenizer takes your string, separates it on any character that is not in [A-Za-z0-0], and lowercases the remaining. If you perform this same operation you will get a nicely "escaped" string suitable for FTS.

You can write your own, but you can access SQLite's internal one as well. See this question for details on that: Automatic OR queries using SQLite FTS4