插入protobuf时SQLite无法识别的令牌错误:要处理哪些令牌&怎么样? [英] SQLite unrecognized token error while inserting protobuf: Which tokens to be handled & How?

问题描述

我正在使用 sqlite3_exec() 函数来执行查询.我知道根据下面的帖子，最好使用 _prepare、_bind、_step、_finalize 组合以获得各种好处.

I am using sqlite3_exec() function for executing the queries. I am aware that as per below post, it's better to use _prepare, _bind, _step, _finalize combination for various benefits.

带有加密 std::string(无法识别的令牌)的 sqllite 查询

但是，就我而言，我已经有了一个库，它与 sqlite3_exec() 紧密耦合，并且很难更改.即使出于教育目的，我也想知道，如何在 sqlite3 数据库中输入特殊令牌.目前，我正在使用 C++ 以编程方式从文件中读取此语句，并使用 sqlite3_exec()

However, in my case I already have a library in place, which is tightly coupled around sqlite3_exec() and it will be difficult to change. Even for education purpose, I would like to know, how can I enter special tokens inside an sqlite3 database. Currently I am programatically reading this statement from a file using C++ and inserting into the DB using sqlite3_exec()

目前，以下语句出错:

INSERT INTO Convey(username, data, id, type)  
VALUES('12345', '^Z^Dfine* ^HºÉÙ<98>ö÷×^B2^@', 'abcd', 31);

错误出现在这个符号 ^@ 作为 SQL error: unrecognized token:.当我拆解语句并检查每个字符的 ascii 时，它显示 0 代替 ^@.data 的类型，无论是 TEXT 还是 BLOB 都没有区别.

The error comes at this symbol ^@ as SQL error: unrecognized token:. When I dismantled the statement and checked ascii for each characters, it showed 0 in place of ^@. The type of data, be it TEXT or BLOB doesn't make a difference.

请注意，我试图在此处输入的数据"是序列化形式的 Google protobuf 消息.

Note that, the "data", I am trying to enter here is a Google protobuf message in a serialised form.

当必须使用 sqlite3_exec() 时，在 sqlite 中转义此类nul"之类的字符的正确方法是什么?

What is the correct way to escape such "nul" like characters in sqlite, when one has to use sqlite3_exec()?

[注意:1 方法是用一些预定义的模式替换 0，这些模式不太可能在二进制字符串中找到，然后在读取时恢复它们.我也对这个解决方案持开放态度.]

[Note: 1 way is to replace 0 with some pre-defined pattern, which less likely to be found in binary strings and then restore them while reading it. I am open to this solution as well.]

推荐答案

似乎不可能 INSERT 具有 blob 性质的数据，即包含 NUL ('\0') 字符(或可能新行 '\n' 个字符).

Seems that it's not possible to INSERT a data with blob nature, i.e. containing NUL ('\0') characters (or possibly new line '\n' characters).

因此，现在我以以下方式插入:

Hence, now I am inserting in following way:

• 插入前，检查是否有这样的特殊字符(即'\0'、'\n')
• 如果是，则在字符串中，在末尾附加一个特殊的已知标记，例如'%'
• 列出该标记后以逗号分隔的位置；这些位置可以用临时占位符替换，例如0"

即'^Z^Dfine* ^HºÉÙ<98>ö÷×^B2^@' 看起来像 '^Z^Dfine* ^HºÉÙ<98>ö÷×^B20#<;位置>'

在读取字符串时，必须对其进行解码.到目前为止，这非常有用并且运行良好！

While reading the string, the same has to be decoded. This is very helpful and working nicely so far!

有兴趣的朋友，这里是替换'\0'的代码:

For those who are interested, here is the code for replacement of '\0':

#define SQLa_STRING_END      '%'
#define SQLa_NUL_SEPARATOR   ','
#define SQLa_NUL_TOKEN       '0'

string
ToString (const string& s_,  // need to enclose with '' and add ' before '
          const bool isEncodingString = false)
{
  string s = s_;
  const char apostrophe = '\'';
  bool isStringEndPending = isEncodingString;
  for(size_t i = 0, length = s.length(); i < length; ++i)
    switch(s[i])
    {
    case apostrophe:
      s.insert(i++, 1, apostrophe);
      ++length;
    break;
    case 0:
      if(isEncodingString)
      {
        if(isStringEndPending)
        {
          s += SQLa_STRING_END;
          isStringEndPending = false;
        }
        s[i] = SQLa_NUL_TOKEN; ;
        s += std::to_string(i) + SQLa_NUL_SEPARATOR;
      }
    default:
    break;
    }
  s.insert(0, 1, '\'');
  return s += '\'';
}

string
FromString (const char value[])  // enclosing '' are not present
{
  string s = value;
  if(const auto posEnd = s.rfind(SQLa_STRING_END) + 1)
  {
    std::replace(s.begin() + posEnd, s.end(), SQLa_NUL_SEPARATOR, '\0');
    for(auto pos = posEnd, length = s.length() - 1;
        pos < length;
        s[::atoll(&s[pos])] = 0, pos = s.find(SQLa_NUL_SEPARATOR, pos) + 1);
    s.erase(posEnd - 1);
  }
  return s;
}

这篇关于插入protobuf时SQLite无法识别的令牌错误:要处理哪些令牌&怎么样?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！